SQLite - Python

Installation

SQLite3 can be integrated with Python using the sqlite3 module. The sqlite3 module was written by Gerhard Haring. It provides an SQL interface compatible with the DB-API 2.0 specification described in PEP 249. You do not need to install this module separately, because Python 2.5.x and above comes with this module by default.

To use the sqlite3 module, you must first create a connection object that represents 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 are important sqlite3 module routines that can meet your needs for using SQLite databases in your Python programs. If you need more details, please refer to the official documentation of the Python sqlite3 module.

No.	API & Description
1	sqlite3.connect(database [,timeout ,other optional arguments]) This API opens a connection to the SQLite database file database. You can use ":memory:" to open a database connection to the database in RAM instead of opening it on disk. If the database is opened successfully, a connection object is returned. When a database is accessed by multiple connections, and one of them modifies the database, the SQLite database is locked until the transaction is committed. The timeout parameter indicates how long the connection waits for the lock, until an exception occurs and the connection is disconnected. The timeout parameter defaults to 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 the database in the current directory, you can specify a filename with a path, so that you can create the database anywhere.
2	connection.cursor([cursorClass]) This routine creates acursor, which will be used in Python database programming. This method accepts a single optional parameter cursorClass. If this parameter is provided, it must be a custom cursor class extended from sqlite3.Cursor.
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). The sqlite3 module supports two types of placeholders: question marks and named placeholders (named style). Example: cursor.execute("insert into people values (?, ?)", (who, age))
4	connection.execute(sql [, optional parameters]) This routine is a shortcut for the method provided by the cursor object described above. It creates an intermediate cursor object by calling the 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 against all parameter sequences or mappings found in seq_of_parameters.
6	connection.executemany(sql[, parameters]) This routine is a shortcut that creates an intermediate cursor object by calling the cursor() method, and then calls the cursor's executemany method with the given parameters.
7	cursor.executescript(sql_script) This routine executes multiple SQL statements once it receives a script. It first executes a COMMIT statement, and then executes the SQL script passed in as a parameter. All SQL statements should use semicolons;for separation.
8	connection.executescript(sql_script) This routine is a shortcut that creates an intermediate cursor object by calling the cursor() method, and then calls the cursor's executescript method with the given parameters.
9	connection.total_changes() This routine returns the total number of database rows that have been 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 performed 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. Note that this does not automatically call commit(). If you close the database connection without previously calling the commit() method, all the changes you have made will be lost!
13	cursor.fetchone() This method fetches the next row in the query result set, returning a single sequence, and returns None when there is no more available data.
14	cursor.fetchmany([size=cursor.arraysize]) This method fetches the next group of rows in the query result set, returning a list. When there are no more available rows, it returns an empty list. This method attempts to fetch as many rows as specified by the size parameter.
15	cursor.fetchall() This routine fetches all (remaining) rows in the query result set, returning a list. When there are no available rows, it returns an empty list.

Connect to Database

The following Python code shows 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.

Example

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')

print (Database opened successfully)

Here, you can also set the database name to a specific name:memory:, which will create a database in RAM. Now, let's run the above program to create our database in the current directorytest.db. You 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 message shown below will be displayed:

$chmod +x sqlite.py
$./sqlite.py
Open database successfully

Create Table

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

Example

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
print (Database opened successfully)
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 willtest.dbcreate the COMPANY table in, and display the message shown below:

Database opened successfully
Table created successfully

INSERT Operation

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

Example

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print (Database opened successfully)

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 (Data inserted successfully)
conn.close()

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

Database opened successfully Data inserted successfully

SELECT Operation

The following Python program shows how to fetch and display records from the COMPANY table created earlier:

#!/usr/bin/python

Example

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print (Database opened successfully)

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 (Data operation successful)
conn.close()

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

数据库打开成功
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

数据操作成功

UPDATE Operation

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

Example

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print (Database opened successfully)

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 (Data operation successful)
conn.close()

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

数据库打开成功
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

数据操作成功

DELETE Operation

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

Example

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print (Database opened successfully)

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 (Data operation successful)
conn.close()

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

数据库打开成功
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

数据操作成功

Other Extensions