How to use three lines of Python code to import and export database and Excel?

Database->Excel

Let’s first take a look at the department table in the database. There are six pieces of data in this table, representing different departments.

Next, take a look at this Python code. First, import the library you need to use, SQLAlchemy, which is the most famous ORM tool in Python.

The full name is Object Relational Mapping.

Why use SQLAlchemy?

It abstracts your code from the underlying database and its associated SQL features.

The characteristic is to manipulate Python objects instead of SQL queries, that is, objects are considered at the code level instead of SQL. It embodies a kind of programmatic thinking, which makes Python programs more concise and easy to read.

The specific usage is as follows:

from sqlalchemy import create_engine
import pandas as pd
# 创建数据库连接
engine = create_engine('mysql+pymysql://root:211314@localhost/hong')
# 读取mysql数据
db = pd.read_sql(sql='select * from hong.department', con=engine)
# 导出数据到excel
db.to_excel('部门数据.xlsx')

The first line of code is to first create a connection to the database.

My mysql username is root and the password is 211314.

Because I am starting the local database service here, it is localhost.

The slash is followed by the name of the database hong

The second line of code is to use pandas's read_sql() to query the data in the mysql table department

The second line The code is to write the queried data to the local through to_excel() of pandas

The execution result is successfully written to the local excel file

Excel->Database

Next let’s look at how to write local xlsx data into a mysql file.

from sqlalchemy import create_engine
import pandas as pd
# 创建数据库连接
engine = create_engine('mysql+pymysql://root:211314@localhost/hong')
# 读取xlsx文件
df = pd.read_excel('模拟数据.xlsx')
# 导入到mysql数据库
df.to_sql(name='test_data', con=engine, index=False, if_exists='replace')

The same first line of code is to first create a connection to the database

The second line of code uses pandas's read_excel() to read the local file. As follows:

This is one hundred pieces of data that I simulated using python’s faker

The third step uses the to_sql() method of pandas to read The obtained data is written into mysql

After the code execution is completed, I return to my hong database in mysql and find that there is an extra table of test_data.

Open it and take a look. Then this data is the same as the local data.

so. Here we use three lines of code to import data from the database to excel, and another three lines of code to import data from excel to the database.

To summarize:

Two-way data import can be achieved with only 3 lines of code.

Import data from the database to excel:

1. Use sqlalchemy to create a database connection

2. Use pandas’ read_sql to read the database data

3 , Use pandas' to_csv to store data into a csv file

Import data from excel to the database:

1. Use sqlalchemy to create a database connection

2. Use pandas' read_csv to read Get the csv data

3. Use pandas’ to_sql to store the data into the database a

The above is the detailed content of How to use three lines of Python code to import and export database and Excel?. For more information, please follow other related articles on the PHP Chinese website!