SQLite Python

installation

SQLite3 can be used with sqlite3 module and Python. sqlite3 module is written by Gerhard Haring. It provides an integration with PEP 249 describes the DB-API 2.0 compliant SQL interface. You do not need to install the module separately because Python 2.5.x and above versions come with the module by default.

To use sqlite3 module, you must first create a connection object representing the database, and then you can optionally create a cursor object, which will help you execute all SQL statements.

Python sqlite3 module API

The following is an important sqlite3 Module program, which can meet your needs for using SQLite database in Python programs. If you need to know more details, please refer to Python sqlite3 The official documentation of the module.

Serial Number	API & Description
1	sqlite3.connect(database [,timeout ,other optional arguments]) This API opens a link to the SQLite database file database. You can use ":memory:" to open a database connection to database in RAM instead of on disk. If the database is successfully opened, a connection object is returned. When a database is accessed by multiple connections and one modifies the database, the SQLite database is locked until the transaction is committed. The timeout parameter indicates the duration for which the connection waits for the lock, until an exception occurs or the connection is disconnected. The default timeout parameter is 5.0 (5 seconds). If the given database name filename does not exist, this call will create a database. If you do not want to create a database in the current directory, you can specify a filename with a path, allowing you to create a database anywhere.
2	connection.cursor([cursorClass]) This routine creates a cursorIt will be used in Python database programming. This method accepts a single optional parameter cursorClass. If this parameter is provided, it must be an extension of sqlite3.Cursor's custom cursor class.
3	cursor.execute(sql [, optional parameters]) This routine executes an SQL statement. The SQL statement can be parameterized (i.e., using placeholders instead of SQL text). sqlite3 The module supports two types of placeholders: question marks and named placeholders (named style). For example: cursor.execute("insert into people values (?, ?)", (who, age))
4	connection.execute(sql [, optional parameters]) This routine is a shortcut for the method executed above, provided by the cursor (cursor) object. It creates an intermediate cursor object by calling the cursor (cursor) method and then calls the cursor's execute method with the given parameters.
5	cursor.executemany(sql, seq_of_parameters) This routine executes an SQL command for all parameters or mappings in seq_of_parameters.
6	connection.executemany(sql[, parameters]) This routine is a shortcut for a middle cursor object created by calling the cursor method, and then calling the cursor's executemany method with the given parameters.
7	cursor.executescript(sql_script) This routine executes multiple SQL statements once the script is received. It first executes the COMMIT statement, and then executes the SQL script passed as a parameter. All SQL statements should be separated by semicolons ;.
8	connection.executescript(sql_script) This routine is a shortcut for a middle cursor object created by calling the cursor method, and then calling the cursor's executescript method with the given parameters.
9	connection.total_changes() This routine returns the total number of database rows modified, inserted, or deleted since the database connection was opened.
10	connection.commit() This method commits the current transaction. If you do not call this method, any actions you have taken since your last call to commit() will not be visible to other database connections.
11	connection.rollback() This method rolls back the changes made to the database since the last call to commit().
12	connection.close() This method closes the database connection. Please note that this does not automatically call commit(). If you have not called the commit() method before, closing the database connection will result in the loss of all changes you have made!
13	cursor.fetchone() This method retrieves the next row from the query result set and returns a single sequence. None is returned when there is no more available data.
14	cursor.fetchmany([size=cursor.arraysize]) This method retrieves the next group of rows from the query result set and returns a list. An empty list is returned when there are no more available rows. This method attempts to retrieve as many rows as possible specified by the size parameter.
15	cursor.fetchall() This routine retrieves all (remaining) rows from the query result set and returns a list. An empty list is returned when there are no more available rows.

Connect to the database

The following Python code demonstrates how to connect to an existing database. If the database does not exist, it will be created, and finally, a database object will be returned.

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Successfully opened database"

Here, you can also rename the database name to a specific name :memory:Thus, a database will be created in RAM. Now, let's run the above program to create our database in the current directory test.dbYou can change the path as needed. Save the above code to the sqlite.py file and execute it as shown below. If the database is created successfully, the following message will be displayed:

$chmod +x sqlite.py
$./sqlite.py
Database opened successfully

Create table

The following Python code block will be used to create a table in the previously created database:

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print "Successfully opened database"
c = conn.cursor()
c.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print "Table created successfully"
conn.commit()
conn.close()

When the above program is executed, it will create a table in the following location: test.db The COMPANY table was created in the database, and the following message was displayed:

Successfully opened database
Table created successfully

INSERT operation

The following Python program demonstrates how to create records in the above created COMPANY table:

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print "Successfully opened database"
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )")
c.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")
conn.commit()
print "Records created successfully"
conn.close()

When the above program is executed, it will create the given records in the COMPANY table and display the following two lines:

Successfully opened database
Records created successfully

SELECT operation

The following Python program demonstrates how to retrieve and display records from the previously created COMPANY table:

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print "Successfully opened database"
cursor = c.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"
print "Operation done successfully"
conn.close()

When the above program is executed, it will produce the following results:

Successfully opened database
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0
ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0
ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0
ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0
Operation done successfully

UPDATE operation

The following Python code demonstrates how to use the UPDATE statement to update any record and then retrieve and display the updated records from the COMPANY table:

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print "Successfully opened database"
c.execute("UPDATE COMPANY set SALARY =" 25000.00 where ID="1)
conn.commit()
print "Total number of rows updated :", conn.total_changes
cursor = conn.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"
print "Operation done successfully"
conn.close()

When the above program is executed, it will produce the following results:

Successfully opened database
Total number of rows updated : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  25000.0
ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0
ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0
ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0
Operation done successfully

DELETE operation

The following Python code demonstrates how to use the DELETE statement to delete any record and then retrieve and display the remaining records from the COMPANY table:

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print "Successfully opened database"
c.execute("DELETE from COMPANY where ID="2;
conn.commit()
print "Total number of rows deleted :", conn.total_changes
cursor = conn.execute("SELECT id, name, address, salary  from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"
print "Operation done successfully"
conn.close()

When the above program is executed, it will produce the following results:

Successfully opened database
Total number of rows deleted : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0
ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0
ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0
Operation done successfully