When it comes to databases, you may think of sql databases for the first time,
This kind of database * * * * is very easy to use, but it is not easy for novices to get started,
I need to be familiar with it for a period of time before I can roughly grasp it. This kind of database is used in large
There are not many places used in the project development process, but some ordinary projects
Still very practical, greatly reducing the amount of code.
catalogue
2.1 create a connection to the database
sqlite3 database
sqlite3 database is Python's own database. It doesn't even need to install additional modules, and the operation is simple.
Python + Mysql = SQLite
But there are few online tutorials on this kind of database, because I only learned about it recently, so I have searched for information for a long time,
Today, we finally have everything. Let's sum it up. I've been looking for it for a long time
1. Required module (only one)
import sqlite3
2. Use of modules
First open our compiler (it is recommended to use vscode, because the suffix of the database file is. db, which is clearly displayed in vscode)
2.1 create a connection to the database
Put the code first
conn = sqlite3.connect('test.db')
The connect function of sqlite3 can be used to create a database or connect to a database,
If the database exists, connect to the database,
If the library does not exist, create the database.
The database name is in the bracket on the right.
We save this in the variable conn, which can be named by ourselves
Formula:
Variable name = sqlite3.connect( 'Database name you want.db' )
2.2 create cursor
Or put the code first
cur = conn.cursor()
In 2.1, we created a connection to the database. We now need a cursor to execute sql commands,
So we need to use the cursor function of conn to create a cursor.
conn is the variable created in 2.1 to save the database. You need to use the variable name you defined,
Define a variable to represent the cursor.
Formula:
variable = Database variable.cursor()
2.3 creating a table
Put the code first
import sqlite3
def check(db_name,table_name):
conn = sqlite3.connect(db_name)
cursor = conn.cursor()
sql = '''SELECT tbl_name FROM sqlite_master WHERE type = 'table' '''
cursor.execute(sql)
values = cursor.fetchall()
tables = []
for v in values:
tables.append(v[0])
if table_name not in tables:
return False # You can create a table
else:
return True # Cannot create table
conn = sqlite3.connect('Database name.db')
#Create a cursor
cur = conn.cursor()
if (check("Database name.db","Table name") == False):
sql_text_1 = '''CREATE TABLE Yours table name
(xx XX,
xx XX);'''
# Execute sql statement
cur.execute(sql_text_1)
The code is relatively long, because I added two lines of code in 2.1 and 2.2.
We need a table to store data. The code for creating a table is as follows:
Variable name = '''CREATE TABLE Yours table name
(xx XX,
xx XX);'''
# Execute sql statement
cur.execute(Above variable name)
In the above, a variable is used to save the sql statement for creating the table,
Next, use cur (the cursor just now) The execute() function executes the statement to create a table.
We can also use cur Execute any function to execute multiple sql statements at the same time.
The contents of sql statements, such as the format of creating tables, are as follows
Format: 'CREATE TABLE your table name (xx XX, xx XX);'
The lowercase xx in this line of code is the attribute name you want, for example, your database is like this
full name
class
Zhang San
1
The attribute names are "name" and "class",
The lowercase xx should write the name and class respectively (note, no quotation marks)
The uppercase XX after this is the type of data accepted by this attribute,
It is equivalent to int type and str type in Python.
However, in the sql statement, we changed the int type to NUMBER and the str type to TEXT.
When we run this code, your database name will appear in our folder directory db file
When we run again, we will find that the program reports an error.
The general meaning of the error message is that the table already exists.
This is because we have created the table the first time we run it. When we run it again,
The program will create a table with the same name again, and an error will be reported.
Therefore, before creating a table, we need to judge whether the table exists. If it exists, it will not be created. If it does not exist, it will be created
I wrote this judgment into a function, that is, the check function in my above code.
I thought about this step for a long time and searched for information for a long time before I knew it
2.4 insert data
Put the code first
cur.executemany('INSERT INTO Yours table name VALUES (?,?)', data)
conn.commit()
The executemany() function in the first line of code means to execute multiple sql statements at the same time.
The comma written in the parentheses of this function is preceded by the sql statement to insert data, and the data can be a list or tuple. Note that if it is a list, it must be in the form of several tuples in the list.
Use of sql statement to insert data:
INSERT INTO your table name VALUES (several commas, separated by commas)
Here we want to insert data, so we use the question mark "" in parentheses to replace this element.
You can go back to the explanation of creating a table in 2.3. In 2.3, we created two attributes, "name" and "class". Because we have two attributes, we need to have two question marks.
2.5 finding data
Put the code first
def find_tb():
cur.execute("select * from Yours table name")
# Extract the queried data
return cur.fetchall()
This is very simple. When I write this function, I can get all the data in your table.
The first line is the sql statement to find the table. The format is:
select * from your table name
On the next line, use the fetchall() function to extract the data, and directly return it.
Happy coding time!
OK, everyone should have seen all the things in front of us. Would you like to have a demo?
By the way, the inspiration of my demo is that I have recently swiped many videos of college entrance examination score checking on the Internet, and I happen to be working on this database recently. Therefore, inspired by the exam, I have built a student score system in my demo. In fact, this is especially cool, and I can change my score to full marks! Although it's useless in practice, it's still very cool
There are no parts in the code that I haven't talked about. You can look at the code according to the above explanation. VScode runs without error.
By the way, if there is something you can't understand, you can send me a private message. If there is no accident, you can reply within one day.
code:
import sqlite3
import os
def check(db_name,table_name):
conn = sqlite3.connect(db_name)
cursor = conn.cursor()
sql = '''SELECT tbl_name FROM sqlite_master WHERE type = 'table' '''
cursor.execute(sql)
values = cursor.fetchall()
tables = []
for v in values:
tables.append(v[0])
if table_name not in tables:
return False # You can create a table
else:
return True # Cannot create table
def find_tb():
cur.execute("select * from scores")
# Extract the queried data
return cur.fetchall()
def zcd():
os.system('cls')
print("Student score management system")
print("1.Add student score information")
print("2.View all student scores")
print("3.Query student scores in the score section")
print("4.sign out")
if __name__ == '__main__':
# Create a connection to the database
conn = sqlite3.connect('stuents_scores.db')
#Create a cursor
cur = conn.cursor()
# If there is no table, execute the sql statement to create the table
if (check("stuents_scores.db","scores") == False):
sql_text_1 = '''CREATE TABLE scores
(full name TEXT,
class TEXT,
Gender TEXT,
language NUMBER,
mathematics NUMBER,
English NUMBER,
Total score NUMBER);'''
# Execute sql statement
cur.execute(sql_text_1)
zcd()
while True:
op = int(input("Please enter:"))
if op == 1:
S_name = input("Please enter the name of the student to be added(as:Zhang San):")
S_class = input("Please enter the class of the student to be added(as:Class 1):")
S_xb = input("Please enter the student's gender:")
S_Chinese = int(input("Please enter the student's language score(Enter only one number,as:82):"))
S_Maths = int(input("Please enter the student's math score(Enter only one number,as:95):"))
S_English = int(input("Please enter the student's English score(Enter only one number,as:98):"))
S_gj = S_Maths+S_Chinese+S_English # Total score
data = [(S_name, S_class, S_xb, S_Chinese, S_Maths, S_English,S_gj)]
cur.executemany('INSERT INTO scores VALUES (?,?,?,?,?,?,?)', data)
conn.commit()
# cur.close()
# conn.close()
print("success!")
os.system('pause')
os.system('cls')
zcd()
elif op == 2:
info_list = find_tb()
print("All student information(Ranking is not divided):")
for i in range(len(info_list)):
print("Second"+str(i+1)+"individual:")
print("Student Name::"+str(info_list[i][0]))
print("Student class:"+str(info_list[i][1]))
print("Student gender:"+str(info_list[i][2]))
print("Students' Chinese scores:"+str(info_list[i][3]))
print("Student math scores:"+str(info_list[i][4]))
print("Student English scores:"+str(info_list[i][5]))
print("Total student scores:"+str(info_list[i][6]))
os.system('pause')
os.system('cls')
zcd()
elif op == 3:
info_list = find_tb()
fen = int(input("The total score you want to query is higher than n Students with scores, Please enter n:"))
for i in range(len(info_list)):
if info_list[i][6] >= fen:
print("Query results:")
print("Second"+str(i+1)+"individual:")
print("Total student scores:"+str(info_list[i][6]))
os.system('pause')
os.system('cls')
zcd()
elif op == 4:
os.system('cls')
break
last
Classic end emojis go!
(3) must return~
last
I know that most junior and middle-level Java engineers want to improve their skills, often by groping for growth or signing up for classes, but for training institutions, the tuition fee is nearly 10000 yuan, which is really a lot of pressure. The self-study effect that is not systematic is inefficient and long, and it is easy to encounter the stagnation of ceiling technology!
Therefore, I have collected and sorted out a complete set of learning materials for Java development and sent it to you. The original intention is also very simple, that is, I hope to help friends who want to improve themselves by self-study but do not know where to start, and at the same time reduce everyone's burden.
Xiaobian has been encrypted: ahr0chm6ly9kb2nzlnfxlmnvbs9kb2mvrvrvm9asgxqzuvstlkwunc = = for security reasons, we have encoded the website through base64. You can get the website through base64 decoding.