Simple tutorial on using SQLite in Python

This article mainly introduces a simple tutorial on using SQLite in Python. as an embedded database, SQLite is embedded in Python versions of previous generations. For more information, see SQLite as an embedded database, its database is a file. Since SQLite is written in C and small, it is often integrated into various applications, and can be integrated in iOS and Android apps.

Python has built-in SQLite3. Therefore, you can directly use SQLite in Python without installing anything.

Before using SQLite, we need to clarify several concepts:

A table is a collection of relational data stored in a database. a database usually contains multiple tables, such as a student table, a class table, and a school table. A table is associated with a table by a foreign key.

To operate a relational database, you must first connect to the database. a database Connection is called Connection;

After connecting to the database, you need to open the Cursor, called Cursor, and run the SQL statement through Cursor to obtain the execution result.

Python defines a set of database operation APIs. to connect any database to Python, you only need to provide a database driver that complies with the Python Standard.

Since the SQLite driver is embedded in the Python standard library, we can directly operate the SQLite database.

Let's take a look at the Python interactive command line practice:

# Import SQLite driver: >>> import sqlite3 # connect to SQLite database # the database file is test. db # if the file does not exist, it will be automatically created in the current directory: >>> conn = sqlite3.connect ('test. db') # Create a Cursor: >>> cursor = conn. cursor () # execute an SQL statement to create a user table: >>> cursor.exe cute ('create table user (id varchar (20) primary key, name varchar (20 ))')
 
  
# Execute an SQL statement and insert a record: >>> cursor.exe cute ('Insert into user (id, name) values (\ '1 \ ', \ 'Michael \') ')
  
   
# Use rowcount to obtain the number of inserted rows: >>> cursor. rowcount1 # Disable Cursor: >>> cursor. close () # submit the transaction: >>> conn. commit () # Close Connection: >>> conn. close ()
  
 

Let's try again to query records:

>>> Conn = sqlite3.connect ('test. db') >>> cursor = conn. cursor () # run the query statement >>> cursor.exe cute ('select * from user where id =? ', '1 ')
 
  
# Obtain the query result set: >>> values = cursor. fetchall () >>> values [(u'1', u'michael ')] >>> cursor. close () >>> conn. close ()
 

When using the Python DB-API, as long as you understand the Connection and Cursor object, you must remember to close after opening, you can safely use.

When you use the Cursor object to execute insert, update, and delete statements, the number of rows affected by the execution result returned by rowcount can be obtained.

When you use the Cursor object to execute the select statement, you can obtain the result set through featchall. The result set is a list, each element is a tuple, corresponding to a row of records.

If an SQL statement has parameters, you need to pass the parameters to the execute () method by position. how many? The placeholder must correspond to several parameters, for example:

cursor.execute('select * from user where id=?', '1')

SQLite supports common standard SQL statements and several common data types. For more information, see the official SQLite website.
Summary

When operating a database in Python, you must first import the corresponding driver to the database, and then operate data through the Connection object and Cursor object.

Make sure that both the opened Connection object and the Cursor object are properly closed. Otherwise, the resource will be leaked.

How can we ensure that the Connection object and Cursor object are closed even when an error occurs? Recall the usage of try:... faster T:... finally.