Python's example of SQLite database operations

This article mainly introduces Python's simple tutorial on SQLite database operation. 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.

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 manipulate a relational database, you first connect to the database, and a database connection is 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.

First, connect the database

Import sqlite3# database name db_name = "Test.db" #表名table_name = "CATALOG" conn = Sqlite3.connect (db_name)

Second, open the cursor

rs = conn.cursor()

Third, the establishment of the table

sql = ' CREATE TABLE ' + table_name + ' (ID varchar (primary) key, PID integer, name varchar ()) ' Try:rs.execute (SQL) PR Int ("Build table succeeded") Except:print ("Build table Failed")

Iv. increase, delete, change, check operation

# Increase: Add three records sql = "Insert into" + table_name + "VALUES (' 001 ', 1, ' Zhang San ')" Try:rs.execute (SQL) #提交事务 Conn.commit () print ("Insert succeeded") Except:print ("Insert failed") sql = "INSERT INTO" + table_name + "VALUES (' 002 ', 2, ' John Doe ')" try:rs.exec Ute (SQL) #提交事务 Conn.commit () print ("Insert succeeded") Except:print ("Insert failed") sql = "INSERT INTO" + table_name + "VALUES (' 003 ', 3, ' Harry ') "Try:rs.execute (SQL) #提交事务 Conn.commit () print (" Insert succeeded ") Except:print (" Insert Failed ") # Delete: Remove the record with PID equal to 3 sql =" Delete from "+ Table_ Name + "WHERE PID = 3" Try:rs.execute (SQL) Conn.commit () print ("Delete succeeded") Except:print ("Delete failed") # change: Change pid to 1sql = "Up" Date "+ table_name +" Set PID = 1 WHERE pid = 2 "Try:rs.execute (SQL) Conn.commit () print (" Modify succeeded ") Except:print (" Modify failed ") # Check # Query database for all table names in sql = "SELECT name from sqlite_master where type = ' table '" res = rs.execute (SQL) print (Res.fetchall ()) # query table for all Record sql = "SELECT * from" + table_nametry:res = rs.execute (SQL) print (Res.fetchall ()) Except:print ([]) 

Five, close the cursor

Rs.close ()

Vi. Closing database connections

Conn.close ()