8.1 Working with the SQLite database using Python

SQLite is a lightweight, disk-based pocket-size database management system embedded in Python that does not require installation and configuration services and supports the use of SQL statements to access the database. The database uses C language development, supports most SQL91 standards, supports atomic, consistent, independent, and persistent transactions, does not support foreign key restrictions, implements independent transactions through database-level exclusivity and shared locking, and when multiple threads access the same database at the same time and try to write data. Only one thread can write data at a time.

SQLite supports a single database up to 140TB in size, each database is completely stored in a single disk file, stored as a B + tree data structure, and a database is a file that can be backed up by copying database files directly. If you need to use visual management tools, you can download and use Sqlitemanager, SQLite Database Browser, or other similar tools.

When accessing and manipulating SQLite data, first import the Sqlite3 module and then create a connection object associated with the database, for example:

1 ImportSqlite3#Import Module2 3 #conn = sqlite3.connect (' example.db ') #连接数据库4 #The Connect () method allows you to determine whether a database file exists, automatically creates one if it does not exist, and then opens the database if it exists. 5 6 #Create a Cusor object, and call the Execute () method of the cursor object to execute the SQL statement to create the data table and query, insert, modify, or delete data from the database, for example:7 #C = conn.cursor ()8 9 #Create a tableTen #C.execute ("CREATE TABLE stocks (date Text,trans text,symbol text,gty real,price Real)" One  A #inserting a piece of data into a table - #C.execute ("INSERT into stocks values (' 2016-01-05 ', ' BUY ', ' rhat ', 100,35.14) ') -  the #commit the current transaction, save the data - #Conn.commit () -  - #To close a database connection + #conn.close () -  +  A  at #Query the data you just inserted - #the Connection object and the cursor object need to be recreated because the database connection has just been closed -conn = Sqlite3.connect ('example.db') -c = Conn.execute (" "SELECT * FROM Stocks" ") - Print(c)#<sqlite3. Cursor object at 0x00000000007e25e0> -  in Print(list (c))#[(' 2016-01-05 ', ' BUY ', ' rhat ', 100.0, 35.14)] -  to #The data was successfully extracted.

You can also query the data in the following ways:

1conn = Sqlite3.connect ('example.db')2c =conn.cursor ()3A = C.execute ('SELECT * FROM Stocks')4 Print(c)#<sqlite3. Cursor object at 0x00000000007e25e0>5 6  forIinchA:7     Print(i)#(' 2016-01-05 ', ' BUY ', ' rhat ', 100.0, 35.14)

8.1 Working with the SQLite database using Python