Use Python to operate SQLite databases and pythonsqlite Databases

Use Python to operate SQLite databases and pythonsqlite Databases
Connect to database

From version 2.5, the Python standard library has a sqlite3 module dedicated for SQLite. The connection to the SQLite database is as follows:

import sqlite3 as 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: Call the connect Method of the database module. The parameter of this method is a string that defines the database to be connected. Since SQLite stores the entire database in a file on the disk, This is the filePath. If the database does not exist, it will be created directly;
The third statement is used to obtain a cursor. It is similar to the cursor in the text editor and is used to record our current location in the database. When multiple programs access the database at the same time, the database knows who is doing what.

Data Type comparison

SQLite	Python	Description
NULL	NontType	I don't know.
INTEGER	Int or long	Integer
REAL	Float	8-byte floating point number
TEXT	Unicode or str	String
BLOB	Buffer	Binary data (Binary Large OBject)

Database Operations

Now we can operate the database. The SQL statement we want to execute is put into a string and the database is told to execute this string, as shown below:

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 changes to the database, we must submit the changes through the commit method of the connection:

con.commit()

Now we can use the following method to obtain data:

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

The fetchone method usesTuplesReturns each record, where each element is arranged in the order specified by the query. If no other record exists, None is returned. By default, the TEXT in the database is returned as a Unicode string. We can tell sqlite3 to return a string of the str type: Set the text_factory Member of the connection to the str type.

con.text_factory = str

The function of the fetchall method isTuplesReturns all data generated by the query.

A quick insert method:

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

Two parameters are used in this execute call. The first is an SQL statement with question marks. These question marks are placeholders of the values to be inserted, and the second is a tuple consisting of values to be inserted, when executing this statement, the database replaces the question marks with these values from left to right.

Note:

• The Data Types of SQL are not exactly the same as those in programming languages. Therefore, it is necessary to convert the data types between them when writing applications.
• The modifications made to the database will not have any effect until they are submitted. This ensures that the database is in the same state when it is operated by more than two programs at the same time.