1. Install the pymysql library
If you want to use python to operate the MySQL database, you must first install the pymysql library, the installation of this library is very simple, directly using pip install pymysql; to install.
If the above method is still not installed, use the following link to find a suitable installation package to install, this will not be detailed, please help yourself to Baidu.
/~gohlke/pythonlibs/
It would be better to study this post by first reading my other post on cursor cursor explanation:https:///article/
2, use python to connect mysql database
1) Six common connection parameters
- Parameter host: ip of the host where the mysql server is located;
- Parameter user: user name
- Parameter password: password
- Parameter port: port of the connected mysql host, default is 3306
- Parameter db: name of the database to connect to
- Parameter charset: when reading data in Chinese will be garbled, we need to set the encoding; we use python to operate the database, then python is equivalent to the client, we are using this client to operate the mysql server server, python3 default utf8 character set, my mysql server defaults to the latin1 character set, so every table created in mysql is built with utf8 encoding, so the setting here should be the connection connector encoding.
What is connection? You can refer to my other article to learn about it.
https:///article/
2) Python syntax for connecting to mysql
import pymysql db = (host='localhost',user='root',password='123456',port=3306,db='spiders',charset=' utf8')
- The most basic parameters are host, user, password and port, which are required. The remaining two parameters are used according to your own situation.
- host refers to where the mysql server is installed, since my mysql is installed on this machine, so here you can write localhost, I can also write the host name or host ip.
- db refers to which database you are operating on, and it is a good idea to add this parameter when making a database connection.
3) A simple warm-up case
# Guide packages
import pymysql
# Connect to the mysql database server using pymysql and create a database object;
db = (host='localhost',user='root', password='123456',
port=3306, db='huangwei', charset='utf8')
# Enable mysql's cursor feature to create a cursor object;
cursor = ()
# The SQL statement to be executed;
sql = "select * from student"
# Execute SQL statements using cursor objects;
(sql)
# Use the fetchone() method to get the returned result, but you need to save the returned result with a variable;
data = ()
print(data)
# Disconnect from the database and release resources;
()
The results are as follows:
3, cursor cursor object of some common methods
1) The method used by cursor to execute commands
- execute(query, args): execute a single sql statement, receiving arguments as the sql statement itself and a list of arguments to use, the return value is the number of rows affected;
- executemany(query, args): executes the singled out sql statement, but repeats the arguments in the argument list, the return value is the number of rows affected;
2) Methods used by cursor to receive return values
- fetchone(): return a result line;
- fetchmany(size): receives the size bar to return the result rows. If the value of size is greater than the number of result rows returned, then the data will be returned;
- fetchall(): receive all the returned result rows;
4、Create table
import pymysql db = (host='localhost',user='root', password='123456',port=3306, db='huangwei', charset='utf8') # Create a cursor object; cursor = () # Build table statement; sql = """ create table person( id int auto_increment primary key not null, name varchar(10) not null, age int not null )charset=utf8 """ # Execute the sql statement; (sql) # Disconnect from the database; ()
Note: What you write in mysql for sql statements is what you write here. Another detail to note is that in python, it is better to use "triple quotes" when you make multiple line breaks in your code.
5、Query data...check
1) fetchone(): get one record at a time
import pymysql
db = (host='localhost',user='root',db='huangwei',password='123456',port=3306,charset='utf8')
cursor = ()
('select count(*) from person')
aa = ()
print(aa)
('select name,age from person')
for i in range(aa[0]):
a,b = ()
c = "My name is{}，last year{}year (of crop harvests)".format(a,b)
display(c)
()
The results are as follows:
2) fetchall(): get all records at once
import pymysql
db = (host='localhost',user='root',db='huangwei',password='123456',port=3306,charset='utf8')
cursor = ()
('select name,age from person')
aa = ()
# print(aa)
for a,b in aa:
c = "My name is{}，last year{}year (of crop harvests)".format(a,b)
display(c)
()
The results are as follows:
Note: There is also a fetchmany() method for fetching a specified number of records at once, so please go down and study it yourself.
3) Use the read_sql() method in pandas to convert the extracted data directly into a DataFrame for manipulation
import pymysql
import pandas as pd
db = (host='localhost',user='root',db='huangwei',password='123456',port=3306,charset='utf8')
cursor = ()
df1 = pd.read_sql("select * from student where ssex='Male'",db)
display(df1)
df2 = pd.read_sql("select * from student where ssex='Female'",db)
display(df2)
The results are as follows:
6. Insert data...add
1) Insert one piece of data at a time
import pymysql
db = (host='localhost',user='root', password='123456',port=3306, db='huangwei', charset='utf8')
cursor = ()
# How SQL statements are written in mysql is how they are written here;
name = "Porky Pig."
age = 8000
sql = 'insert into person(name,age) values ("Porky Pig.",8000)'
try:
(sql)
()
print("Insertion successful.")
except:
print("Insertion failed.")
()
()
1.1) Insert one piece of data at a time
import pymysql
db = (host='localhost',user='root', password='123456',port=3306, db='huangwei', charset='utf8')
cursor = ()
# Insert data
sql = 'insert into person(name,age) values(%s,%s)'
try:
(sql,('Monkey King',100000))
()
print("Insertion successful.")
except:
print("Insertion failed.")
()
()
2) Insert multiple pieces of data at once
import pymysql
db = (host='localhost',user='root', password='123456',port=3306, db='huangwei', charset='utf8')
cursor = ()
# Insert data
sql = 'insert into person(name,age) values(%s,%s)'
# Note: (('King Bull',9000),('Princess Iron Fan',8000),('Jade Emperor',6000)) are also possible, and the parentheses can all be replaced with center brackets.
datas = [('King Bull Demon',9000),('Princess Iron Fan',8000),('Jade Emperor',6000)]
try:
(sql,datas)
()
print("Insertion successful.")
except:
print("Insertion failed.")
()
()
Summarized below:
① The pymysql module enables mysql's transaction function by default, so when you do "add", "delete", "change", you must use () to commit the transaction, otherwise you can't see the inserted data.
② When doing "add", "delete", "change", always use try...except... statement, because if the insertion is not successful, the rest of the code can not be executed. When the statement is unsuccessful, we () roll back to the state before the operation; when the statement is successful, we () commit the transaction.
7. Update data...change
import pymysql
db = (host='localhost',user='root', password='123456',port=3306, db='huangwei', charset='utf8')
cursor = ()
# Update data
sql = 'update person set age=%s where name=%s'
try:
(sql,[90000,"Jade Emperor."])
()
print("Update successful.")
except:
print("Update failed.")
()
()
8. Delete data...delete
import pymysql
db = (host='localhost',user='root', password='123456',port=3306, db='huangwei', charset='utf8')
cursor = ()
# Delete data
sql = 'delete from person where age=8000'
try:
(sql)
()
print("Deleted successfully.")
except:
print("Deletion failed.")
()
()
This is the whole content of this article, I hope it will help you to learn more.