Exploring Sqlite Database and table connection of python
In our python learning series, we will continue some of the other modules which are going to help in learning python. As I have posted many articles related to python basic concept and I hope it helps you a lot in learning python. As my last article, we have learned about the Iterator concept. If you haven’t checked then I would recommend checking the post from this link. I continue the series, today we will learn about the major used module in python is Database connection and uses of python.
In python, we can use any database API like Mysql, SQL server etc. But today we will learn about the sqlite3 database module which is mostly used. To use the Sqlite3 module we need to install the Sqlite3 module by using pip command in the virtual environment. Before installing the SQLite package please check which python version running in your system.
If you are using the 2.7.3 version then use this command to install.
pip install pysqlite
For version 3.0, run this command.
pip install pysqlite3
Once you install the above package then you can use or import this module in your project and use this feature easily. Now let’s see what are the methods available in the SQLite module.
Methods:
sqlite3.connect(database)
-> Database: either provide db name or ':memory:‘
Detect_types: PARSE_DECLTYPES, PARSE_COLNAMES
sqlite3.register_converter(typename, callable):
-> convert database byte string to python type
sqlite3.register_adapter(type, callable):
-> Convert the python type to database types
sqlite3.complete_statement(sql):
-> whether string contains one or more sql statements
Now we need to understand about the Database Connection Object and their method to uses.
Database: Connection object
Connection Object returned by calling sqlite3.Connection().
Methods:
cursor(): creates a pointer to database commit(): commit the current transaction rollback(): rollback any changes till the last commit close(): close the connection execute(sql): execute an sql query executemany(sql): exceute multiple sql statements
Database: Cursor object
Cursor Object returned by calling connection.cursor()
Statement:
execute(sql, parameters): Execute sql statements. Parameters can be passed as ? Or named executemany(sql, parameters_list): Same sql command with each parameter from a sequence executescript(sql_script): Execute an entire script.
Methods:
fetchone(): fetch one row of query result fetchmany(size): number for rows from query result fetchall(): fetches all the rows from query result
Now let’s see the few examples of SQLite to create a connection between database and table. We will create the table and insert some data and fetch those data from the SQL query. The most import thing that once you have a connection object that executed properly then you need to close the connection in the last to avoid the leaks.
import sqlite3
conn = sqlite3.connect('example.db')
print conn
c = conn.cursor()
print c
# Create table
c.execute('''CREATE TABLE stocks
(date text, trans text, symbol text, qty real, price real)''')
# Insert a row of data
c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
# Save (commit) the changes
conn.commit()
# We can also close the connection if we are done with it.
# Just be sure any changes have been committed or they will be lost.
conn.close()
conn = sqlite3.connect('example.db')
c = conn.cursor()
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print c.fetchone()
As in the above example, we can see that we need to import the SQLite module package to use the feature. We need to create a connection with the named database. Now we need to get the cursor object to create the table and insert data in the table. Once you have done then commit the transaction to execute data inserted and after please close the connection.
Now if you want to execute a few more data inserts or fetch or delete then you need to get the connection object again and use the cursor object of that. I hope it is very clear to understand. If still having confusion then let’s see one more example for cursor object for use memory rather than a database, we can directly use the memory to do few transactions. For few transactions is okay but it is not recommended to use huge transaction or data to store in memory, it will affect the performance and storage issue.
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("create table people (name_last, age)")
who = "Yeltsin"
age = 72
# This is the qmark style:
cur.execute("insert into people values (?, ?)", (who, age))
# And this is the named style:
cur.execute("select * from people where name_last=:who and age=:age", {"who": who, "age": age})
print cur.fetchone() // fetch the signle current data
print cur.fetchall() // fetch all the data
Now it is very understood to use the database cursor object. For advance, we can the Cursor Adapter to convert from the SQL to python. But I am not going into detail of Cursor Adapter here, I will come with the new article for this. In this article, we have covered and learned the basic of cursor object statement to execute the transaction.
Summary: Now we have a good understanding of the Database connection SQLite3 module. We have seen a few examples and play around it to clear the doubts. If you still having issue or doubts then please add a comment I will try to reply as much possible. In my next python tutorial series, we will learn about the Threading concept with the example.
If you are wondering to learn Android then Please Learn from Android category and wondering to lean Kotlin then Kotlin Category will help you. If you want to learn all the python article, then learn from the python category.
Please do subscribe your email to get the newsletter on this blog on below and if you like this post then do not forget to share like and comment on the below section.
Happy Coding 
I am a very enthusiastic Android developer to build solid Android apps. I have a keen interest in developing for Android and have published apps to the Google Play Store. I always open to learning new technologies. For any help drop us a line anytime at contact@mobologicplus.com
