Python Operation SQLite Database

Connecting to a database

Starting with version 2.5, Python's standard library has a sqlite3 module dedicated to SQLite. Connect the SQLite database in the following ways:

importas dbapicon = dbapi.connect(‘population.db‘)cur = con.cursor()

The first statement is used to reference the database API;
The second statement creates a connection to the database (connection): Invokes the Connect method of the database module. The parameter of the method is a string that defines the database that we want to connect to. Since SQLite keeps the entire database in a file on disk, this is the path to the file. If the database does not exist, it will be created directly;
The third statement is used to get a cursor that resembles a cursor in a text editor to record where we are currently in the database, and when multiple programs access the database at the same time, the database can know who is doing it.

Data type Comparison

SQLite	Python	Description
Null	Nonttype	I don't know anything.
INTEGER	int or Long	Integer
REAL	Float	8-byte floating-point number
TEXT	Unicode or str	String
Blob	Buffer	Binary data (binary Large OBject)

manipulating databases

Now we can manipulate the database, we will execute the SQL statement into a string, and tell the database to execute the string, as follows:

cur.execute(‘CREATE TABLE Student(Stuid TEXT, Age INTERGER, Name TEXT)‘)cur.execute(‘INSERT INTO Student VALUES("00001", 20, "Lucy")‘)cur.execute(‘INSERT INTO Student VALUES("00002", 21, "Lily")‘)

After inserting the database into the database or making any other modifications to the database, we must commit the changes through the commit method of the connection (commit):

con.commit()

Now we can get the data in the following ways:

cur.execute(‘SELECT * FROM Student‘)print(cur.fetchone())print(cur.fetchall())

The Fetchone method returns each record in the form of a tuple , where the elements are arranged in the order specified by the query. If no more records exist, none is returned. By default, the text in the database is returned as a Unicode string, and we can tell Sqlite3 to return the string as a type of str: Set the Text_factory member of the connection to type Str.

con.text_factory = str

The function of the Fetchall method is to return all the data produced by the query in the form of a tuple list .

A more efficient way to insert:

ss = [("00003"20"David"), ("00004"23"Cneagle"),      ("00005"22"qxzy")]forin ss:    cur.execute(‘INSERT INTO Student VALUES(?, ?, ?)‘, (s[0], s[1], s[2]))con.commit()

This call to execute uses two parameters, the first being a SQL statement with a question mark, a placeholder for the value to be inserted, and a tuple of the values to be inserted, and the database replaces those question marks from left to right when executing the statement.

Attention:

• The data type of SQL is not exactly the same as the data type in the programming language, so it is necessary to type the conversion between them when writing the application.
• Modifications made to the database do not have any effect until they are committed. This ensures that the database can be maintained in a consistent state when it is manipulated by more than two programs at the same time.

Python Operation SQLite Database