一.准备工作
1.1安装以下包
pip install openpyxl pip install sqlalchemy pip install pandas
1.2配置数据库的东西在conf.py文件下
DATABASE_CONFIG = { "HOST": "127.0.0.1", "USERNAME": "root", "PASSWORD": "", "DATABASE": "bike_ods", "PORT": 3306, }
View Code
1.3 mysql ---> excel
from sqlalchemy import create_engine import pandas as pd from conf import DATABASE_CONFIG # 创建数据库连接 ip = DATABASE_CONFIG["HOST"] username = DATABASE_CONFIG["USERNAME"] password = DATABASE_CONFIG["PASSWORD"] ods = DATABASE_CONFIG["DATABASE"] # 文件输出 excel_file = "out.xlsx" engine = create_engine(f'mysql+pymysql://{username}:{password}@{ip}/{ods}') # 读取mysql数据 select_sql = f"select * from areafence" db = pd.read_sql(select_sql, con=engine) # 导出数据到excel db.to_excel(excel_file)
View Code
1.4 excel ---------> mysql
from sqlalchemy import create_engine import pandas as pd from conf import LOCALHOST_CONFIG as DATABASE_CONFIG # 创建数据库连接 ip = DATABASE_CONFIG["HOST"] username = DATABASE_CONFIG["USERNAME"] password = DATABASE_CONFIG["PASSWORD"] ods = DATABASE_CONFIG["DATABASE"] # 文件输出 excel_file = "out.xlsx" # 创建数据库连接 engine = create_engine(f'mysql+pymysql://{username}:{password}@{ip}/{ods}') # 读取xlsx文件 df = pd.read_excel(excel_file) # 导入到mysql数据库 df.to_sql(name='test_data', con=engine, index=False, if_exists='replace')
View Code
1.5 如果默认端口不是3306
engine = create_engine('mysql://user:password@localhost:3306/test?charset=utf8mb4')
