Let's first take a look at the database department This department table . There are six pieces of data in this table , Represent different departments .
Let's take a look at this Python Code , First, import the library you need SQLAlchemy, This is a Python The most famous ORM Tools .
Full name Object Relational Mapping（ Object relation mapping ）.
Why use SQLAlchemy？
It can take your code from the underlying database and its related SQL Abstracted from features .
It's characterized by manipulation Python Object instead of SQL Inquire about , That is, at the code level, objects are considered , instead of SQL, It embodies a kind of procedural thinking , This makes Python The program is more concise and easy to read .
The specific use method is as follows ：
from sqlalchemy import create_engine
import pandas as pd
engine = create_engine(‘mysql+pymysql://root:[email protected]/hong’)
db = pd.read_sql(sql=‘select * from hong.department’, con=engine)
db.to_excel(‘ Department data .xlsx’)
The first line of code is to first create a connection to the database .
my mysql User name is root, The password is 211314,
Because here I start the local database service , So it is localhost.
The slash is followed by the name of the database hong
The second line of code is to use pandas Of read_sql() Inquire about mysql surface department Data in
The second line of code is to pass the queried data through pandas Of to_excel() Write locally
The execution result is successfully written to the local server excel file
Next, let's look at how to integrate local xlsx Data written to mysql In file .
from sqlalchemy import create_engine
import pandas as pd
engine = create_engine(‘mysql+pymysql://root:[email protected]/hong’)
df = pd.read_excel(‘ Analog data .xlsx’)
df.to_sql(name=‘test_data’, con=engine, index=False, if_exists=‘replace’)
Similarly, the first line of code is to first create a connection to the database
The second line uses pandas Of read_excel() Read local file . as follows :
This is for me python Of faker A hundred simulated data
The third step is to use pandas Of to_sql() Method writes the read data to mysql in
After the code is executed, return to mysql In my hong The database found one more test_data Table of .
Open it and have a look . Then this data is the same as the local data .
therefore . Here we use three lines of code from the database to excel Import data , Another three lines of code from excel Import data into the database .
Click for relevant information [ Here is the picture 005]https://docs.qq.com/doc/DU2t3eVlTdE9TekRJ
To sum up ：
Two way data import , All are 3 One line of code .
From database to excel Import data ：
1、 use sqlalchemy Create database connection
2、 use pandas Of read_sql Read data from the database
3、 use pandas Of to_csv Store data in csv file
from excel Import data into the database ：
1、 use sqlalchemy Create database connection
2、 use pandas Of read_csv Read csv The data of
3、 use pandas Of to_sql Put the data in the database a
