Python standard library 14 database (SQLITE3)

Python standard library 14 database (SQLITE3)

Vamei Source: Http://www.cnblogs.com/vamei Welcome reprint, Please also keep this statement. Thank you!

Python comes with a lightweight relational database, SQLite. This database uses the SQL language. As a back-end database, SQLite can be used with Python to build websites or to create tools with data storage requirements. SQLite also has a wide range of applications in other fields, such as HTML5 and mobile. The sqlite3 in the Python standard library provides an interface to the database.

I will create a simple relational database that stores the classification and price of books for a bookstore. The database contains two tables: category is used for record classification, and book is used to record the information of a certain books. A book belongs to a category, so there is a foreign key (foreign key), which points to the primary key ID of the Catogory table.

Create a database

I'll start by creating the database, as well as the tables in the database. After connecting to the database using Connect (), I can execute the SQL command by locating the pointer cursor:

#by VameiImportSqlite3#Test.db is a file in the working directory.conn = Sqlite3.connect ("Test.db") c = Conn.cursor () #< Span style= "color: #008000;" > create Tablesc.execute (create TABLE category (ID int primary key, sort int, name text) " ' ) C.execute ( "create TABLE book (id int primary key, sort int, name text, price real, category int, FO Reign KEY (category) REFERENCES category (ID))  ") # Save the Changesconn.commit () # Close the connection with the Databaseconn.close ()     

SQLite database is a disk file, such as the above test.db, so the entire database can be easily moved or copied. Test.db does not exist at first, so SQLite will automatically create a new file.

With the execute () command, I executed two SQL commands to create two tables in the database. After the creation is complete, save and disconnect the database.

Inserting Data

The database and table are created above, and the abstract structure of the database is established. The following will insert the data in the same database:

#by VameiImportSqlite3conn = Sqlite3.connect ("Test.db") C =Conn.cursor ()Books = [(1, 1,‘Cook Recipe', 3.12, 1), (2, 3,‘Python Intro', 17.5, 2), (3, 2,‘OS Intro', 13.6, 2), ]#Execute "INSERT" C.execute ( "insert into category VALUES (1, 1, ' kitchen ')  "" # using the Placeholderc.execute ( "insert into category VALUES (?,? , ?) " computer< Span style= "color: #800000;" > ' )]) # execute Multiple Commandsc.executemany ( "insert into book VALUES (?,?,?,?,?)  '    

Inserting data you can also use Execute () to execute a complete SQL statement. The arguments in the SQL statement, using the "?" As an alternative symbol and give a specific value in the following arguments. It is not possible to format strings in Python, such as "%s", because this usage is susceptible to SQL injection attacks.

I can also use the Executemany () method to perform multiple insertions, adding multiple records. Each record is an element in the table, such as an element in the books table above.

Enquiry

After executing the query statement, Python returns a circulator containing multiple records obtained by the query. You can also read the records using the Fetchone () and Fetchall () methods provided by the Sqlite3:

#by VameiImportSqlite3conn = Sqlite3.connect (‘Test.db‘) C =Conn.cursor ()#Retrieve One Recordc.execute (‘SELECT name from category ORDER by sort‘)Print (C.fetchone ()) print ( C.fetchone ()) # Retrieve all records as a listc.execute ( Span style= "color: #800000;" > ' ): print (Row)               

Update and delete

You can update a record, or delete a record:

# by Vameiconn = Sqlite3.connect ("test.db") c = conn.cursor () c.execute ('UPDATE Book SET price=? WHERE id=? ', (+, 1)) C.execute ('DELETE from book WHERE id=2') conn.commit () conn.close () 

You can also delete the entire table directly:

C.execute ('DROP TABLE book')  

If you delete test.db, the entire database is deleted.

Summary

Sqlite3 is just an interface for SQLite. To be proficient in using SQLite databases, you need to learn more about relational databases.

Welcome to the Python quick tutorial

Python standard library 14 database (SQLITE3)