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 .
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()
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 :
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 .
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 .
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 .
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 .
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 :
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 :
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 .
In this example, we learned to check whether the specified table already exists in sqlite3 Knowledge of database .
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 .
You can go through cursor.lastrowid() To check if the new row is inserted successfully .
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 :
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 .
In this section , We learned how to sqlite3 Insert new row in table .
From you to sqlite3 To query data in a database table, follow these steps :
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 .
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 :
In this section we learned about how to query records from a table .
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 :
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 .
In this section , We learned how to insert multiple rows of records into a single query statement sqlite3 Table related knowledge .
To delete sqlite3 All records in the table , perform DELETE FROM Inquire about . The detailed steps are as follows :
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 .
In this section , We learned about deleting sqlite3 All the relevant knowledge recorded in the table .