Tips ： Here you can add a list of all the articles in the series , The directory needs to be added manually
This chapter mainly explains the use python operation mysql database
Tips ： After writing the article , Directories can be generated automatically , How to generate it, please refer to the help document on the right
Tips ： Here you can add the general content to be recorded in this article ：
First install... Locally PyMySQL, Installation mode Win+R --> Input cmd–>pip install PyMySQL
If already installed , It will appear as in the picture .
Then go online to download mysql, and Navicat That's all right. . Of course Navicat It can be replaced by others , Because I installed this software locally .Navicat The cracked version can be found online , There are still many .
Tips ： The following is the main body of this article , The following cases can be used for reference
1、 First, establish a connection to the database
2、 Generate cursor
3、 Execute the required database statements
4、 Get query results
5、 Close cursor
6、 Close database connection
Let's use python Database < check > This operation , The code is as follows
The code is as follows （ Example ）：
import pymysql
# Connect to database
con=pymysql.connect(host='localhost',user='root',password='root',database='huace',charset='utf8')
# Create cursors
cur=con.cursor()
# Generate database
sql='select * from sscore'
# To get the results
cur.execute(sql)
# Get all the records fetchall-- Get all the records fetchmany-- Get multiple records , Need to pass parameters fetchone-- Get a record
all=cur.fetchall()
# Output query results
print(all)
# Close cursor
cur.close()
# Close database connection , The purpose is to free memory
cur.close()
That's it .
Here are some points for attention ！！！
1、 The results of some small partners may be ’？‘, Like the picture 
This is because pymysql.connect Medium charset Value and sql The character set in does not correspond to .
So what do you think sql What character set is the field in , I am still in Navicat For example , Pictured 
Friends can try the code charset='utf8’ Change it to charset='utf-8’ Is there going to be ’？‘.
By the way, I'll explain to you , The meaning of these fields host– Connection database address
user– Database login user name ,password= Database login password ,database= The name of the database ,charset= Database character set port– Database port number autocommit– Transaction submission ,True Is for automatic submission False For manual submission .
Check the source code yourself , Understand the meaning of each field , This source code is a little around , Here I also show you with pictures 
I copy the source code below
def __init__(self, host=None, user=None, password="",
database=None, port=0, unix_socket=None,
charset='', sql_mode=None,
read_default_file=None, conv=None, use_unicode=None,
client_flag=0, cursorclass=Cursor, init_command=None,
connect_timeout=10, ssl=None, read_default_group=None,
compress=None, named_pipe=None, no_delay=None,
autocommit=False, db=None, passwd=None, local_infile=False,
max_allowed_packet=16*1024*1024, defer_connect=False,
auth_plugin_map={}, read_timeout=None, write_timeout=None,
bind_address=None, binary_prefix=False):
Be careful 2、 If some friends output a value when running the code , You can check whether cur.fetchall() Get all this statement . without , Then the value output from the console is the quantity in the table , This table has multiple data .
The code is as follows （ Example ）：
Additions and deletions , The operation mode is the same , It's just sql Different sentences , Here I will write a code . The point to note here is , We need to commit the transaction , If you don't commit the transaction , Statements are not executed , I'll start with the one who doesn't commit , Show successful execution , But there is no new 
And then we ; Add transaction commit to it .
There are two ways , The first is pymysql.connect Medium plus autocommit=True
The second kind con.commit()
The code is as follows , I use the first method in the code
import pymysql
# Connect to database The first one is autocommit=True
con=pymysql.connect(host='localhost',user='root',password='root',database='huace',charset='utf8',autocommit=True)
# Create cursors
cur=con.cursor()
# Generate database
sql='insert into sscore (name,class,socre) VALUES (" Li Si "," Chinese language and literature ",90)'
# To get the results
cur.execute(sql)
# Commit transaction Method 2
# con.commit()
# Close cursor
cur.close()
# Close database connection , The purpose is to free memory
cur.close()
If you need to delete , Or modify , Only need to modify sql Statement is enough .
Let's talk about how to add multiple data again , There are also two ways
import pymysql
# Connect to database The first one is autocommit=True
con=pymysql.connect(host='localhost',user='root',password='root',database='huace',charset='utf8',autocommit=True)
# Create cursors
cur=con.cursor()
# # Mode one
# # Generate database
# sql='insert into sscore (name,class,socre) VALUES (" Li Si "," Chinese language and literature ",90),(" Zhang Liu "," Chinese language and literature ",90)'
# # To get the results
# cur.execute(sql)
# Mode two
sql2='insert into sscore (name,class,socre) VALUES (%s,%s,%s)'
data=[(" Li Si "," Chinese language and literature ",91),(" Zhang Liu "," Chinese language and literature ",91)]
# To get the results
cur.executemany(sql2,data)
# Commit transaction Method 2
# con.commit()
# Close cursor
cur.close()
# Close database connection , The purpose is to free memory
cur.close()
It's too late today. , Make up for it tomorrow , Playing games and sleeping
Here it is... Here it is , Fill up ！！！
import pymysql
class Mysql_Object():
def __init__(self,host,user,password,database,port=3306,charset='utf8'):
self.host=host
self.user=user
self.password=password
self.database=database
self.port=port
self.charset=charset
def select_sql(self,sql,size=0):
'''
Inquire about sql sentence
:param sql Incoming query's sql sentence , character string
:param size The number of records returned , If there is no input, the default output is all pieces
:return: self.count Returns the total number of records queried ,slef.res Return the result of the query
'''
self.con=pymysql.connect(host=self.host,user=self.user,password=self.password,database=self.database,port=self.port,charset=self.charset)
self.cur=self.con.cursor() # Create cursors
self.sql=sql
# Judge whether it is a query statement
if self.sql.startswith('select'):
self.cur.execute(self.sql) # Get database results
self.count=self.cur.rowcount # Count the number of query records
# adopt if Sentence to judge
if size == 0:
self.res=self.cur.fetchall() # Output all results
elif size != 0 :
self.res=self.cur.fetchmany(size) # Output the specified value
self.cur.close()
self.con.close() # Close the connection
return self.count,self.res
def excute_sql(self,sql):
'''
:param sql Enter the added, deleted and modified sql sentence
:return:
'''
self.con = pymysql.connect(host=self.host, user=self.user, password=self.password,port=self.port, database=self.database,
charset=self.charset,autocommit=True)
self.cur = self.con.cursor() # Create cursors
self.sql = sql
if self.sql.startswith('insert'):
print(' Insert statement ',self.sql)
self.cur.execute(self.sql) # Execute statement
self.cur.close() # Close the connection
self.con.close()
if self.sql.startswith('delete'):
print(' Delete statements ',self.sql)
self.cur.execute(self.sql) # Execute statement
self.cur.close() # Close the connection
self.con.close()
if self.sql.startswith('update'):
print(' UPDATE statement ',self.sql)
self.cur.execute(self.sql) # Execute statement
self.cur.close() # Close the connection
self.con.close()
# call
m=Mysql_Object('localhost','root','root','huace')
print(m.select_sql('select * from sscore',3))# Query results
m.excute_sql('update sscore set name=" Wang Liu "where `name`=" Zhang San "' ) # UPDATE statement
m.excute_sql('delete from sscore where name=" Li Si "') # Delete statements
m.excute_sql('insert into sscore VALUES(" Zhao Qi "," English ","89")') # Insert statement
Console results , as follows 
Tips ： Here is a summary of the article ：
These are some summaries after watching the video , Use this as a notebook , Simply sort out the learning content , Because now home isolation , Under normal circumstances, one watch a day , I will go to the new company next week , It may not be updated so frequently , Let's make progress together , If there is any mistake , Or you are welcome to point out the wrong places , After all, there are still many deficiencies after just learning . Thank you , I think it's OK. I can also like it ！！！
I will update it later selenium The framework ,python Other foundations , I have learned to update it in succession
