Python code operation single file database sqlite3 CRUD method, please pay attention to the flexibility of inserting data operation!
import sqlite3
#generate a database connection object conn
conn = sqlite3.connect("test.db") #if the file does not exist, it will be automatically created
#you can create a cursor object by connecting objects to execute SQL statement, for example :
cursor = conn.cursor()
cursor.execute("create table if not exists user (id integer primary key, name text, age integer)") #create a user surface
cursor.execute("insert into user (id, name, age) values (1, 'Alice', 20)") #insert a record
for i in range(2, 10):
cursor.execute("insert into user (name, age) values ('cnliutz', {})".format(60+i))
# Replace 'Unknown' with the actual name
data = ('John Doe'+str(i), 25+i)
cursor.execute("insert into user (name, age) values ( ?, ?)", data)
conn.commit() #submit transaction
#you can use the cursor object's fetchone(), fetchmany () or fetchall the () method is used to obtain data from the query result set, for example :
cursor.execute("select * from user where age > 18") #query users aged over 18
users = cursor.fetchall() #obtain all eligible users
for user in users:
print(user) #print information for each user
#you can use the cursor object's execute () or executemany () method to update or delete data, for example :
cursor.execute("update user set age = 21 where id = 1") #update id the age of user 1 is 21
cursor.executemany("delete from user where name = ?", [("Alice",), ("Bob",)]) #delete name as Alice or Bob users of
conn.commit() #submit transaction
#finally, you need to close the cursor and connection objects to free up resources, such as :
cursor.close()
conn.close()