SQLite Overview:
- SQLite is good in so many ways.
- SQLite is an in-process library.
- SQLite does not run as a separate Database Server Process. An application can host a database all by itself using SQLite.
- SQLite comes in a single C file and makes use of only a handful of C library calls. SQLite does not make use of any external libraries.
- SQLite is a Zero Configuration Database. There is no need of installing SQLite before use. No need install it as a server and make it up and running before your run your program. No configuration, troubleshooting, recovery operations required for SQLite.
- SQLite is a fully transactional ACID compliant database and transactions are either fully completed or not completed.
SQLite and Python:
- SQLite is supported in Python through the module sqlite3, which confirms to Python Database API Specification.
- By using the interfaces provided by sqlite3 module all the functionalities of SQLite Database can be used inside any Python Program.
Simplest SQLite Program using Python:
|
import sqlite3 conn = sqlite3.connect(“sample.adb”) conn.close() |
The above 3 lines will create a Database called sample and will return the Connection Object. The Connection object is closed.
The one liner
conn = sqlite3.connect(“sample.adb”)
creates the database sample in a disk file sample.adb. The extension can be anything like .adb or any other extension as the developer prefers.
Python SQLite Example – Create Table, Insert Data and Query Data:
|
#import the sqlite module import sqlite3
# Through connection object create/open already existing DB connectionObject = sqlite3.connect('sample.db') cursorObject = connectionObject.cursor()
studId = 11 studName = 'John' studAdmissionDate = '2016-Jan-15'
# Drop the table if already existing... dropTableSQL = "drop table stud" cursorObject.execute(dropTableSQL)
# Create student table createTableSQL = "CREATE TABLE stud (id INTEGER, name text, admission_date text)" cursorObject.execute(createTableSQL)
# Insert a row of data into stud table insertDataSQL = "INSERT INTO stud VALUES(?,?,?)"
# substitution parameters - ? will be replaced by members of this tuple insertSpec = (studId,studName,studAdmissionDate)
#execute SQL insert using parameter substitution cursorObject.execute(insertDataSQL,insertSpec)
# Commit the changes connectionObject.commit()
# Query the stud table whereSpec = (studId,)
cursorObject.execute("SELECT * FROM stud WHERE id=?", whereSpec)
# print the row print(cursorObject.fetchone())
# promptly closing the connection as the resource is no longer needed connectionObject.close() |
In the above Python program a Connection Object is created using the sqlite3 interface. While obtaining a connection object, Python connects to an already existing sqlite3 database or creates a new database with the given name.
Calling cursor()method on connection object provides a cursor with which SQL statements can be executed.
A new sqlite table with the name stud is created by calling the execute() method on the cursor object obtained.
The same cursor is used to insert a new row of data into as well as for querying the data from sqlite table named stud.
The parameters to the insert and select SQL statements are not provided using Python’s string concatenation routines due to security issues which will pave for SQL injection attacks.
Instead, parameter substitution for the fields marked by "?" is done using the execute method. Either one variable or a tuple can be passed in the second parameter of the execute method which will replace one or more fields marked by "?".
Once the database operations are complete the database connection can be closed by calling close() on the connection object. Always, remember to call commit() before closing any connection objects to ensure no data lose.