Dry goods | Use Python to operate mysql database
Author | Tao
Source | Know almost
This article mainly explains how to use the pymysql library in python to operate the mysql database.
First look at the most common operations:
- Select the required fields from the database (simple data aggregation processing)
- Export the searched data to a local file (csv, txt, xlsx, etc.)
- Convert local files into variables in python through pandas read_excel (csv, txt), and process and analyze the data accordingly
- Export the processed data to a local file through pandas to_excel (csv, txt)
But don’t you think that the second step is too much? Why do you need to export and then import? This intermediate step is a waste of time. The ideal step should be like this
- Import the data in mysql into python
- Use python to process and analyze data
- Export to excel report
Does this make you feel more comfortable? So the question is, how to directly import the data in mysql directly into python?
This brings us to the key points of today:
- The first method: read_sql
- The second method: pymysql
First look at our database information today:
host：192.168.0.***port：3306
user：root
password:********
Database: test
Table name: weather_test
Fields and data:
1
read_sql()
read_sql(sql,con,index_col='None',coerce_float='True',params='None',parse_dates='None',columns='None',chunksize:None='None')
The read_sql method is used in pandas to execute the specified SQL statement query in the database or query the specified entire table, and return the query result in the DataFrame type.
The meaning of each parameter is as follows:
- sql: SQL statement to be executed
- con: The engine required to connect to the database, which is established with other database connection packages, such as SQLalchemy and pymysql
- index_col: Which column to choose as index
- coerce_float: convert a numeric string to float
- parse_dates: Convert a column of date string to datetime data
- columns: select the columns you want to keep
- chunksize: How many rows of data are output each time
1. First import pandas and sqlalchemy
2. Create connection
3. Write sql code, execute sql code, get the returned value
import pandas as pd
import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://root:******@192.168.0.***:3306/test')
sql='''
select *from weather_test where
create_time between '2020-09-21' and '2020-09-22'
and city in('Hangzhou','Shanghai')'''
df = pd.read_sql(sql,engine)
df
It is also possible to use pymysql to establish a connection and query
So far, simply use the read_sql method in pandas to get data from the database once.
2
PyMySQL
PyMySQL is a library used to connect to the MySQL server in the Python3.x version, which can easily connect to and operate the database
1. Installation
First open cmd, enter pip install pymysql to install the pymysql library
2. Use pymysql to operate the database
Next, open the jupyter notebook and start trying to manipulate the database
2.1 First import pandas, pymysql
import pandas as pd
import pymysql
2.2 Next create a connection to the database
import pandas as pd
import pymysql
# Open database connection
db = pymysql.connect("192.168.0.***","root","******","test", charset='utf8')
Use the connect() method to establish a connection to the database. The main parameters required are already marked on the picture. Charset recommends utf8 to prevent Chinese garbled. Assign the established connection object to the variable name db
2.3 Use cursor() method to get operation cursor
import pandas as pd
import pymysql
# Open database connection
db = pymysql.connect("192.168.0.***","root","******","test", charset='utf8')
# Use cursor()Method to get operation cursor
cursor = db.cursor()
Cursor is a method of processing data. In order to view or process the data in the result set, the cursor provides the ability to browse the data one or more rows at a time in the result set.
The cursor can be regarded as a pointer, it can specify any position in the result, and then allow the user to process the data at the specified position. In layman's terms, ** operating data and obtaining database results must be operated through the cursor. **If we don't get the cursor, we can't get the queried data.
The most commonly used and default cursor is the cursor. The returned data format is tuple. The remaining cursor types include DictCursor, SSCursor, SDictCursor, etc. The cursors at the beginning of SS are called stream cursors, and Cursor and DictCursor cursors can All data is returned at once, and the streaming cursor intelligently returns the query data one by one, so this type of cursor is suitable for application scenarios with low memory, small network bandwidth, and large data volume.
DictCursor: Returns data in dictionary (Dict) format
SSCursor: Streaming cursor returns Tuple format data
SSDictCursor: Streaming cursor returns dictionary (Dict) format data
When using other cursors, just add the corresponding parameters in the cursor() method
cursor = db.cursor(pymysql.cursors.SSDictCursor)
2.4 Write sql code, execute sql code
Write a simple SQL statement, the purpose is to check the weather in Shanghai and Hangzhou during the two days of 2020-09-21~2020-09-22, change the written SQL statement to string format and assign it to the variable name sql. Using the excute() method, you can execute the written SQL statement through the defined cursor, and you can see that a number 4 is output, which means that the queried data set contains a total of 4 data.
2.5 Get the returned query results
Use the fetchall() method to obtain the complete data set queried through the defined cursor, and assign it to the variable name cds
Print the cds variable, you can see that the data has been obtained, and now we need to change it into our commonly used DataFrame format
In addition to the fetchall() method, there are also two methods fetchone() and fetchmany(size) to get the returned data
fetchall(): return all data
fetchone(): return to the next data
fetchmany(size): returns the next size data
2.6 Convert the acquired data into DataFrame format
Convert the cds variable in tuple format to list, and then use the DataFrame() method in pandas to convert cds to DataFrame format, change the column name, and assign it to the weather variable name
Output weather to see the data
2.7 Close the cursor, close the database connection
import pandas as pd
import pymysql
# Open database connection
db = pymysql.connect("192.168.0.***","root","******","test", charset='utf8')
# Use cursor()Method to get operation cursor
cursor = db.cursor()
sql ="""
select *from weather_test
where create_time between '2020-09-21' and '2020-09-22'
and city in('Shanghai','Hangzhou')"""
cursor.execute(sql)
cds = cursor.fetchall()
weather = pd.DataFrame(list(cds),columns=['ID','time','province','city','Maximum temperature','lowest temperature','Daytime weather','Night weather','Wind force','wind direction'])
cursor.close() #Close cursor
db.close() #Close database connection
When using pymysql to create a connect object, a long tcp connection has been created with mysql. As long as the close method of this object is not called, the long connection will not be disconnected, and resources will always be occupied. , So don’t forget to close the cursor and database connection after execution
The above is just the simplest way to query the database using python. Other additions, deletions and modifications are similar to this, you can play by yourself
- END -
This article is reprinted for sharing & recommended reading, if infringement, please contact the background to delete
Recommended Posts