Python operation SQLite database
Know SQLite database
SQLite database will be automatically installed on your computer after installing Python. Through it, data can be permanently stored on the local computer. Someone must say that we can store data in files. Why should we store it in the database? Well, it must be comfortable to use (it's the same as what you didn't say). To be honest, you don't have to ask why. Start first, and then supplement these concept classes. Using a database is better than using files in some scenarios, but only in some scenarios.
In Python, import the module through import sqlite3.
SQLite basic usage
The database is generally divided into the following three steps.
- Connect to database
- Operation database
- close database
Use the following command to connect to the database.
conn = sqlite3.connect("Database name")In this way, if the database exists, the connection is automatically established. If it does not exist, the database is created first and then the connection relationship is established. Remember to close the database in time after use.
conn.close()
conn in the above code is a common variable, which is generally called a database connection object. Remember, it is also an object. It must have properties and methods.
Please run the following code to check whether there is one more file in the local directory.
import sqlite3
conn = sqlite3.connect("my_data.db")
conn.close()Create SQLite database table
Through the connect method, you can establish a connection with the database file. The returned object is the connect object. The common methods of this object are as follows.
- Close close the database connection;
- commit updates the database content;
- Cursor creates a cursor object, which can execute the execute method;
- execute executes SQL database commands, such as data table creation, query, deletion and update.
SQLite data type
Before formally learning the operation of SQLite database, you need to learn several data types contained in the database. Because SQLite is relatively simple, there are not many data types involved.
- NULL null value;
- INTEGER;
- REAL floating point number;
- TEXT string;
- BLOB rich text data, such as pictures and songs.
With the above, you can start operating the database. Before that, we need to add a concept that a database can contain multiple tables, which can be translated into a common word, a my_ data. The DB file can contain multiple tables.
In my_ data. Create the first table in the DB file.
import sqlite3
# Connect to my_data.db database
conn = sqlite3.connect("my_data.db")
# Create cursor object
cursor = conn.cursor()
# Create table SQL statement
sql = """
create table students(
id int,
name text,
sex text,
age int
)
"""
# Execute sql statement
cursor.execute(sql)
# Close cursor object
cursor.close()
# Close database connection
conn.close()Relevant comments have been added to the code. Note that both conn object and cursor object need to be closed after use, and cursor object needs to be closed before conn object.
create table students( id int, name text, sex text, age int )
|This content is a table creation SQL statement, in which create table table name (field list), and each field definition is designed according to the format of field name field type. Through this SQL statement, you can create a students table in the database, which contains four columns: id, name, sex and age. Here you can think of a table as something similar to a table.
| id | name | sex | age |
|---|
After the students table has been created, you cannot create a table with the same table name. That is, an error will be reported when executing the above code. At this time, you can use try Except statement.
import sqlite3
# Connect to my_data.db database
conn = sqlite3.connect("my_data.db")
# Create cursor object
cursor = conn.cursor()
# Create table SQL statement
sql = """
create table students(
id int,
name text,
sex text,
age int
)
"""
try:
# Execute sql statement
cursor.execute(sql)
except:
print("The table already exists in the database")
# Close cursor object
cursor.close()
# Close database connection
conn.close()Add data to the table
After creating a table in the database, you can add data. The data in the table is generally called records. Next, use the command to insert data to add a piece of student data to the table.
import sqlite3
# Connect to my_data.db database
conn = sqlite3.connect("my_data.db")
# Create cursor object
cursor = conn.cursor()
# SQL to insert data into a table
insert_sql = """
insert into students values(?,?,?,?)
"""
try:
# Data to be inserted
data = (1,"Charlie","male",18)
# Execute sql statement
cursor.execute(insert_sql,data)
# Update the database content. Don't forget this command when inserting data, deleting data and updating data
conn.commit()
except Exception as e:
print("Insert exception",e)
# Close cursor object
cursor.close()
# Close database connection
conn.close()The core of the above code is insert into students values(?,?,?,?), This content is also an SQL statement. When the execute method is used to execute the statement, the corresponding data needs to be passed in the second parameter of the method, that is, if there are three question marks in the SQL statement, the second parameter here should also be a tuple of three elements.
Query table data in database
The SQL format of the query statement is:
select * from Table name
Query the data code in the students table as follows:
import sqlite3
# Connect to my_data.db database
conn = sqlite3.connect("my_data.db")
# Create cursor object
cursor = conn.cursor()
# Query statement SQL
select_sql = """
select * from students
"""
try:
# Execute sql statement
results = cursor.execute(select_sql)
print(results)
for record in results:
print(record)
except Exception as e:
print("Query exception", e)
# Close cursor object
cursor.close()
# Close database connection
conn.close()Use the above code to read all the data of the specified table in the database. And when we execute results = cursor After execute (select_sql), the output results object type is & lt; sqlite3. Cursor object at 0x00000000020DCB90>， The object has a fetchall method, which can store all data in a tuple at one time.
Another thing to note is that if you only want a column in a data table and don't want all the data, the syntax format of the SQL statement used is:
select Listing,Listing from Table name
Update table data
The keyword used to update the data in the data table is update. The syntax format is as follows:
update surface set Listing = New value,Listing=New value... where condition
The SQL syntax format here has become relatively complex. The where condition judgment is mainly added. First, complete the following code content.
import sqlite3
# Connect to my_data.db database
conn = sqlite3.connect("my_data.db")
# Create cursor object
cursor = conn.cursor()
# Modify SQL statement
update_sql = """
update students set name = "Big Charlie" ,age=20
where id = 1
"""
try:
# Execute sql statement
cursor.execute(update_sql)
conn.commit()
except Exception as e:
print("Update exception", e)
# Close cursor object
cursor.close()
# Close database connection
conn.close()Execute the update operation to update Charlie to big Charlie. You can use the query statement to check whether the data in the table has been modified.
The key point of Python operating the database is actually the proficiency of SQL statements. The SQL statements involved in this blog are only the tip of the iceberg. In fact, there is a professional syntax structure for SQL statements. I will explain it for you when I snowball again later.
When updating the column data in the table, the first thing to pay attention to is to add the where condition, otherwise the whole table will be updated; Second, you must make sure the column name, otherwise you will report an error if you can't find the column name.
Delete table data
After learning insert and update, it becomes much easier to delete table data. Remember the syntax format of SQL:
delete from Table name where condition
When the where condition is met, the corresponding data will be deleted. It must be noted that if the where condition is not written, the whole table will be deleted.
\`If you don't want to delete the database and run away, remember that when updating or deleting any table in the database, you must write the where condition\`
Summary of this blog
If this blog is opened by a friend who is learning Python for the first time, it is actually very difficult, because it involves another language system called SQL language, a syntax for operating databases. After learning this syntax, you can actually perform simple operations on many databases. The embarrassing thing is that if you learn Python for the first time, you need to look at Python syntax, While watching SQL syntax, it's easy to get confused about both, but don't worry. After all, this is only the first snowball. I believe you will be more confident the second time.