A simple tutorial on using SQLite in Python

SQLite is an embedded database, and its database is a file. Since SQLite itself is written in C and small in size, it is often integrated into a variety of applications, even in iOS and Android apps.

Python has built-in SQLite3, so using SQLite in Python does not require anything to be installed and used directly.

Before using SQLite, we need to figure out a few concepts:

A table is a collection of relational data in a database that usually contains multiple tables, such as a student's table, a class table, a school table, and so on. The table and table are associated by a foreign key.

To operate a relational database, you first need to connect to the database, a database connection called connection;

After connecting to the database, you need to open the cursor, called the cursor, execute the SQL statement through the cursor, and then get the execution result.

Python defines a set of API interfaces for manipulating databases, and any database to be connected to Python requires only a Python-compliant database driver.

Since SQLite's drivers are built into the Python standard library, we can directly manipulate the SQLite database.

Let's practice in the Python interactive command line:

# import SQLite driver:>>> import sqlite3# connect to sqlite database # 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 ') # creates a cursor:>>> cursor = Conn.cursor () # Executes an SQL statement that creates a user table:>>> Cursor.execute (' CREATE table user (ID varchar (primary key, name varchar) ')
 
  
   
  # Continue executing an SQL statement, inserting a record: >>> Cursor.execute (' INSERT INTO user ' (ID, name) VALUES (\ ' 1\ ', \ ' michael\ ') ')
  
   
    
   # Gets the number of rows inserted by rowcount :>>> cursor.rowcount1# close Cursor:>>> cursor.close () # COMMIT TRANSACTION:>>> Conn.commit () # Close connection: >>> conn.close ()
  
   
 
  

Let's try the query record again:

>>> conn = sqlite3.connect (' test.db ') >>> cursor = Conn.cursor () # Executes the query statement:>>> cursor.execute ( ' SELECT * from user where id=? ', ' 1 ')
 
  
   
  # Get query result set:>>> values = Cursor.fetchall () >>> values[( U ' 1 ', U ' Michael ')]>>> cursor.close () >>> conn.close ()
 
  

When using Python's db-api, just make sure that the connection and cursor objects are open and remember to close them, so you can use them with confidence.

When you execute a insert,update,delete statement using the cursor object, the execution results are returned by the number of rows affected by rowcount, so you can get the results.

When you execute a SELECT statement using the Cursor object, you can get the result set through Featchall (). The result set is a list, each element is a tuple, corresponding to a row of records.

If the SQL statement has parameters, you need to pass the parameters to the Execute () method by location, with a few? Placeholders must correspond to several parameters, for example:

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

SQLite supports common standard SQL statements and several common types of data. Please refer to the official SQLite website for specific documentation.
Summary

When you manipulate a database in Python, you first import the driver for the database, and then manipulate the data through the connection object and the cursor object.

To ensure that both the connection object and the cursor object are turned off correctly, the resource will be compromised.

How can you make sure that the connection object and the cursor object are also closed when an error occurs? Please recall try:...except:...finally: ... The usage.

