1.51. SQLite-Python

1.51.1. Installation ¶

SQLite3 can integrate with Python using the sqlite3 module. The sqlite3 module is written by Gerhard Haring. It provides a SQL interface that is compatible with the DB-API 2.0 specification described by PEP 249. You do not need to install this module separately because it comes with Python 2.5.x and later by default.

In order to use the sqlite3 module, you must first create a connection object that represents the database, and then you can selectively create a cursor object, which will help you perform all SQL Statement.

1.51.2. Python sqlite3 module API ¶

The following are important sqlite3 Module program, which can meet your needs of using SQLite database in Python program. If you need more details, please check the official documentation of the Python sqlite3 module.

Serial number	API & description
1	Sqlite3.connect (database [,timeout ,other optional arguments] )
	The 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 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 commits. The timeout parameter indicates how long the connection waits to be locked until it is disconnected abnormally. The timeout parameter defaults to 5.0 (5 seconds).
	If the given database name filename does not exist, the call creates a database. If you do not want to create a database in the current directory, you can specify a file name with a path so that you can create the database anywhere.
2	Connection.cursor ( [cursorClass] )
	This routine creates a cursor that 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 that extends from sqlite3.Cursor.
3	Cursor.execute (sql [, optional parameters] )
	This routine executes a SQL statement. The SQL statement can be parameterized (that is, placeholders are used instead of SQL text). The sqlite3 module supports two types of placeholders: question marks and named placeholders (named styles).
	For example: cursor.execute (“insert into people values (?)”, (who, age))
4	Connection.execute (sql [, optional parameters] )
	This routine is a shortcut to the method provided by the cursor object executed above, which 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 a SQL command on all parameters or mappings in seq_of_parameters.
6	Connection.executemany (sql [, parameters] )
	This routine is a shortcut to the intermediate cursor object created by calling the cursor method, and then calls the cursor’s executemany method with the given parameters.
7	Cursor.executescript (sql_script)
	Once the routine receives the script, it executes multiple SQL statements. It first executes the COMMIT statement, and then executes the SQL script passed in as a parameter. All SQL statements should be separated by semicolons.
8	Connection.executescript (sql_script)
	This routine is a shortcut to the intermediate cursor object created 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 you have done since you last called commit () are not visible to other database connections.
11	Connection.rollback ()
	This method rolls back 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 don’t call the commit () method before, close the database connection directly, and all your changes will be lost!
13	Cursor.fetchone ()
	This method takes the next row in the query result set, returns a single sequence, and returns None when no more data is available.
14	Cursor.fetchmany ( [size=cursor.arraysize] )
	This method gets the next row group in the query result set and returns a list. When no more rows are available, an empty list is returned. This method attempts to get as many rows as possible specified by the size parameter.
15	Cursor.fetchall ()
	This routine gets all (remaining) rows in the query result set and returns a list. When no rows are available, an empty list is returned.

1.51.3. Connect to the database ¶

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

Example ¶

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print ("数据库打开成功")

Here, you can also copy the database name to a specific name :memory: Which creates a database in RAM Now, let’s run the above program to create our database in the current directory test.db . You can change the path as needed. Save the above code to sqlite.py File, and execute as shown below. If the database is created successfully, the message shown below is displayed:

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

1.51.4. Create a tabl ¶

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

Example ¶

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
print ("数据库打开成功")
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 ("数据表创建成功")
conn.commit()
conn.close()

When the above program is executed, it will be executed in the test.db Create in COMPANY Table and displays the message shown below:

数据库打开成功
数据表创建成功

1.51.5. INSERT operation ¶

The following Python program shows how to create the COMPANY Create a record in the table:

Example ¶

#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print ("数据库打开成功")
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 ("数据插入成功")
conn.close()

When the above program is executed, it will be executed in the COMPANY The given record is created in the table and the following two rows are displayed:

Database opened successfully data inserted successfully

1.51.6. SELECT operation ¶

The following Python program shows how to create the COMPANY Get and display records in the table:

#!/usr/bin/python

Example ¶

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print ("数据库打开成功")

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 ("数据操作成功")
conn.close()

When the above program is executed, it produces the following results:

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

数据操作成功

1.51.7. UPDATE operation ¶

The following Python code shows how to use the UPDATE Statement to update any records, and then from the COMPANY Table to get and display updated records:

Example ¶

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print ("数据库打开成功")

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 ("数据操作成功")
conn.close()

When the above program is executed, it produces the following results:

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

数据操作成功

1.51.8. DELETE operation ¶

The following Python code shows how to use the DELETE Statement to delete any records, and then from the COMPANY Get and display the remaining records in the table:

Example ¶

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()
print ("数据库打开成功")

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 ("数据操作成功")
conn.close()

When the above program is executed, it produces the following results:

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

数据操作成功