SQLite It's a lightweight disk based database , It is not like MySQL That requires a separate server , Its data is direct persistent storage , Its database is actually a file .
Like any other relational database , We will learn about Python Yes sqlite3 Database connection object creation 、 Create tables in the database 、 Insert a record... Into the table 、 Query the data in the table based on clauses 、 Update data based on clauses 、 Delete some or the whole table data and other related skills .

1. Create connection objects

Python Built in sqlite3, And provide sqlite3 library . So we don't need to install anything 、 It can be used directly .
To create a sqlite The connection object of , have access to sqlite3.connect() function . The syntax of this function is as follows ：

conn = sqlite3.connect('dbname.db')

among ,connect() The function reads a string as the database name and returns a sqlite3.Connection Class object .
If the database already exists , It will only return one Connection object , Otherwise, the database will be created and a new database will be returned Connection object .

1.1. Use sqlite3 Create a Connection Examples of objects

In this case , We will create a connection to sqlite be known as mysqlite.db Database Connection object .

import sqlite3
conn = sqlite3.connect('mysqlite.db')

In the use of sqlite3 You need to import any function of sqlite3 library .
Of course , You can also in memory (RAM) Create a database , Just create Connection When the object will be :memory: As a parameter to sqlite3.connect() that will do .

import sqlite3
conn = sqlite3.connect(':memory:')

To create in your sqlite If you operate on the database , You have to give Connection Object to create a cursor .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()

2. stay sqlite3 Database create table

You can sqlite3 Create one or more tables in the database .
In this section , We will learn how to use Python stay sqlite3 Create a new table in the database .
Use Python sqlie3 To create a table, follow these steps ：

1. Create a connection to sqlite3 Database Connection object .
2. One Connection Object's cursor .
3. take CREATE Query to sqlite3.execute() To create a new table .

2.1. Use sqlite3 new table

In this example , We will create a new one called mysqlite.db The database of , And create a new one called students Table of .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# create table
c.execute('''CREATE TABLE students (rollno real, name text, class real)''')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output ：

After executing the program , A group called students The new table will be in the database mysqlite.db Created in . But if you execute the program again , You will get the following wrong results ：

In order not to be disturbed by whether the table already exists , We can refer to the following section for example , A new table is created only if the table does not exist .

2.2. A new table is created only if the table does not exist

In the query statement , We can define it as creating a new table only when the table does not exist . You can use... Before the table name in the query statement IF NOT EXISTS To create a new table .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# Create the table
c.execute('''CREATE TABLE IF NOT EXISTS students (rollno real, name text, class real)''')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output ：

You can see , Although we have created the table before executing the program students, But I was not disturbed by the error in the previous table .

2.3. Summary

In this section , We learned how to be in sqlite3 Create a new table in the database . Besides , We also learned about how to create a new table only when the table does not exist .

3. See if the table already exists

Before executing a query statement on a table , You can check whether the table already exists in sqlite3 database .
To check if a table already exists in sqlite3 database , You can go from the watch sqlite_master Query whether there is a table name matching your table name .
The relevant syntax is as follows ：

SELECT name FROM sqlite_master WHERE type='table' AND name='table_name';

among ,table_name Need to replace with the table name you want to query .
In the results of this query, you can check whether there are some lines . If there's a line of results , Then the table already exists .

3.1. Check whether the table already exists in sqlite3

In the example in the previous section, we have created a new one called students Table of . Now? , We will program to check if the table already exists .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# get the count of the tables with the name
c.execute('''SELECT count(name) FROM sqlite_master WHERE type='table' AND name='students' ''')
# if the count is 1, then table exists
if c.fetchone()[0]==1:
print('Table students exists')
else:
print('Table students not exists')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output ：

3.2. Check whether the checklist already exists in sqlite3 ( A scene that doesn't exist )

In this example, we will examine a negative scenario , It's called students1 The table of does not exist in sqlite3 Database scenario .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# get the count of tables with the name
c.execute('''SELECT count(name) FROM sqlite_master WHERE type='table' and name='students1' ''')
# if the count is 1, then exists
if c.fetchone()[0]==1:
print('Table students1 exists')
else:
print('Table students1 does not exists')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output ：

3.3. Check if the table exists in memory (RAM)

If you want to check the memory (RAM) In the table , have access to sqlite_temp_master Put... In the above example sqlite_master Replace .

3.4. Summary

In this example, we learned to check whether the specified table already exists in sqlite3 Knowledge of database .

4. towards sqlite3 Insert row in table

You can go to sqlite3 Insert one or more rows... Into the table .
To insert a row of data into sqlite3 surface , Follow these steps .

1. Create a connection to sqlite3 Database connection .
2. Get a cursor for the connection .
3. Check if the table already exists , If it doesn't exist, create a new one .
4. If the table already exists , Using cursors execute() Method , By way of SQL The insert query is passed to the method to insert a new row .

You can go through cursor.lastrowid() To check if the new row is inserted successfully .

4.1. Insert a line into sqlite3 surface

In the next example , If the table does not exist, we will create a new table , And then use INSERT INTO Query to insert a record into the table .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# create table
c.execute('''CREATE TABLE IF NOT EXISTS students (rollno real, name text, class real)''')
c.execute('''INSERT INTO students VALUES(1, 'Alex', 8)''')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output ：

4.2. Insert new row to sqlite3 Table and check if the insert was successful

In most scenarios , We need to check that INSERT INTO Query whether the insert is successful .
Do that , We can check by sqlite3 Of the last row of records inserted by the cursor id, If it's time to id No 0, Then we can confirm that the insertion has been successful .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# create table
c.execute('''CREATE TABLE IF NOT EXISTS students (rollno real, name text, class real)''')
c.execute('''INSERT INTO students VALUES(1, 'Glen', 8)''')
print(c.lastrowid)
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output ：

If the output is not zero , So prove that your insertion did work . If you get a zero , So you need to debug your program .

4.3. Summary

In this section , We learned how to sqlite3 Insert new row in table .

5. from sqlite3 Query rows in the table

From you to sqlite3 To query data in a database table, follow these steps ：

1. Create a connection to sqlite Database Connection object .
2. Create a new cursor for this connection .
3. take SELECT FROM Query to sqlite3.execute() Method and execution .

5.1. from sqlite3 Read data in table

The following example will read sqlite3 Database table students All the lines in .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
c.execute('''SELECT * FROM students;''')
rows = c.fetchall()
for row in rows:
print(row)
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output ：

You can see that the previously inserted test data has been printed out . Of course , You can also modify SELECT FROM Query to filter data 、 Sort or transform .

5.2. Use WHERE Clause from sqlite3 Read lines in

Next is in SELECT FROM Use in query WHERE Example of filtering query lines by clauses .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
c.execute('''SELECT * FROM students WHERE name="Alex";''')
rows = c.fetchall()
for row in rows:
print(row)
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output ：

5.3. Summary

In this section we learned about how to query records from a table .

6. Insert multiple lines to sqlite3 surface

In section four , We learned how to insert a single row into a table . In this section, we'll learn how to use a single INSERT INTO Query inserts multiple rows of data into the table .
The steps to insert multiple rows of records into a table are as follows ：

1. Ready to connect to the database Connection Object and get a cursor .
2. Store the record to be inserted in a sheet list in .
3. Insert the list As a parameter to executemany() Method and execution .

6.1. Insert multiple lines to sqlite surface

In the next example , We'll go to the students Insert three more pieces of data into the table .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# records or rows in a list
records = [(1, 'Alen', 8),
(2, 'Elliot', 9),
(3, 'Bob', 7)]
# insert multiple records in a single query
c.executemany('INSERT INTO students VALUES(?,?,?);', records)
print('We have inserted', c.rowcount, 'records to the table.')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output ：

We print out cursor.rowcount To confirm that the query statement successfully inserted three records .

6.2. Summary

In this section , We learned how to insert multiple rows of records into a single query statement sqlite3 Table related knowledge .

7. Delete sqlite3 All records in the table

To delete sqlite3 All records in the table , perform DELETE FROM Inquire about . The detailed steps are as follows ：

1. Create a new one sqlite3 The connection of .
2. Get the cursor of the connection .
3. perform DELETE FROM table Inquire about .

7.1. Delete sqlite3 The rows in the table

In the following example , We will learn how to use DELETE FROM table The query statement will sqlite3 Delete all the records in the table .

import sqlite3
conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()
# delete all rows from table
c.execute('DELETE FROM students;')
print('We have deleted', c.rowcount, 'records from the table.')
# commit the changes to db
conn.commit()
# close the connection
conn.close()

Execution and output ：

you 're right , We inserted a total of 5 Data , Now it's all deleted .

7.2. Summary

In this section , We learned about deleting sqlite3 All the relevant knowledge recorded in the table .

Reference material

• Python sqlite3 Tutorial
• Python sqlite3 – Create Connection Object
• Python sqlite3 – Create Table
• Python sqlite3 – Check if Table Exists
• Python sqlite3 – INSERT INTO table
• Python sqlite3 – SELECT FROM TABLE
• Python sqlite3 – INSERT Multiple Rows to TABLE
• Python sqlite3 – DELETE all rows FROM TABLE