Use Python to operate on the SQLite database and pythonsqlite Database

Use Python to operate on the SQLite database and pythonsqlite Database

SQLite is an embedded database, and 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.

1. Connect to the database

Import sqlite3 # Database Name db_name = "test. db" # table name table_name = "catalog" conn = sqlite3.connect (db_name)

Ii. Open the cursor

rs = conn.cursor()

3. Create a table

SQL = 'create table' + table_name + '(id varchar (20) primary key, pid integer, name varchar (10) 'try: rs.exe cute (SQL) print ("table created successfully") failed T: print ("table created failed ")

4. add, delete, modify, and query operations

# Add: add three records: SQL = "Insert into" + table_name + "values ('001', 1, 'zhang san')" try: rs.exe cute (SQL) # commit transaction conn. commit () print ("inserted successfully") failed T: print ("inserted failed") SQL = "Insert into" + table_name + "values ('002 ', 2, 'Li si') "try: rs.exe cute (SQL) # submit the transaction conn. commit () print ("inserted successfully") failed T: print ("inserted failed") SQL = "Insert into" + table_name + "values ('003 ', 3, 'wang wu') "try: rs.exe cute (SQL) # submit the transaction conn. commit () print ("inserted successfully") failed T: print ("inserted failed") # delete: delete SQL = "Delete from" + table_name + "where pid = 3" try: rs.exe cute (SQL) conn. commit () print ("deleted successfully") failed T: print ("failed to delete") # modify: change the pid of the record whose pid is equal to 2 to 1sql = "Update" + table_name + "set pid = 1 where pid = 2" try: rs.exe cute (SQL) conn. commit () print ("modified successfully") failed T: print ("failed to modify ") # query all table names in the database SQL = "Select name From sqlite_master where type = 'table'" res = rs.exe cute (SQL) print (res. fetchall () # query all records in the table SQL = "Select * from" + table_nametry: res = rs.exe cute (SQL) print (res. fetchall () before T: print ([])

5. Close the cursor

rs.close()

6. Close database connection

conn.close()