使用python读写MySQL数据库的基本操作

使用python读写MySQL数据库的基本操作

1.导库

常见的数据库的一些连接方式

• MySQL

  create_engine('mysql+pymysql://user:password@host:port/db_name')

• PostgreSQL

  create_engine('postgresql+psycopg2://user:password@host:port/db_name')

• Oracle

  create_engine('oracle+cx_oracle://user:password@host:port/db_name')

• SQLServer

  create_engine('mssql+pymssql://user:password@host:port/db_name')

对于MySQL我们需安装pymysql库

#!pip install -i https://pypi.douban.com/simple pymysql
import pandas as pd
import numpy as np
import pymysql
from sqlalchemy import create_engine # 构建数据库连接信息
from sqlalchemy import types        # 可用于dataframe类型的数据与数据库类型转换

2.使用create_engine方法创建连接

user="root"
pw=123456
host="localhost"
port="3306"
db_name="mysql"
# 自定义函数获取数据库的连接信息
def get_mysql_conn(user=user,pw=pw,host="localhost",port="3306",db_name="mysql"):
    return create_engine(f'mysql+pymysql://{user}:{pw}@{host}:{port}/{db_name}?charset=utf8')
mysql_con = get_mysql_conn()

# 也可以一句搞定
mysql_con = create_engine('mysql+pymysql://root:123456@localhost:3306/mysql?charset=utf8')

3.基本操作

通过execute()执行SQL命令，查看所有的数据库名

for database in mysql_con.execute("show databases"):
    print(database)

删除名称中含test的数据库

# 删除chaoshi相关的数据库
for db in [database[0] for database in mysql_con.execute("show databases") if "test" in database[0]]:
       mysql_con.execute(f"drop database {db}")

创建两个数据库，分别命名为Fact和Dim

list = ['Fact','Dim']
for db in  list:
     mysql_con.execute(f"create database {db}")

在数据库中创建表，表名命名为fact_order_tb

# 创建表
ddl = """
create table Fact.fact_order_tb(
  订单ID varchar(15)
 ,订单日期 timestamp
 ,发货日期 timestamp
 ,邮寄方式 varchar(10)
 ,客户ID varchar(10)
 ,城市 varchar(10)
 ,产品ID varchar(15)
 ,销售额 decimal(9,3)
 ,数量 bigint
 ,折扣 decimal(9,3)
 ,利润 decimal(9,3)
)
"""
mysql_con.execute(ddl)

效果： 

 使用to_sql()将python读取的数据存储到MySQL数据库fact_order_tb表中

# 设置各个字段的类型映射
fact_order_tb_dtypes = { 
                      '订单ID':types.VARCHAR(15),
                      '订单日期':types.TIMESTAMP,
                      '发货日期':types.TIMESTAMP,
                      '邮寄方式':types.VARCHAR(10),
                      '客户ID':types.VARCHAR(10),
                      '城市':types.VARCHAR(10),
                      '产品ID':types.VARCHAR(15),
                      '销售额':types.DECIMAL(9,3),
                      '数量':types.BIGINT,
                      '折扣':types.DECIMAL(9,3),
                      '利润':types.DECIMAL(9,3)}
# 获取数据
fact_order_tb = pd.read_csv(rf"D:\Python\20230701 CDA二级\ETL\fact_order_tb_2023.csv")
fact_order_tb.head()
fact_order_tb.shape

# 向MySQL数据库中写入数据
fact_order_tb.to_sql(name ="fact_order_tb"  # dataframe加载到数据库里面的哪个表中
                     ,con = get_mysql_conn(db_name="fact") # 连接对象，要指定的数据库名
                     ,dtype = fact_order_tb_dtypes #对dataframe里的数据进行数据库的数据类型的映射
                     ,if_exists = "append"   # 在表中有数据时使用replace覆盖数据，使用append可追加数据
                     ,index = None # DF索引列不存入数据库
                    )

在python中使用pd.read_sql()查询数据

pd.read_sql(sql=f"select * from fact_order_tb limit 3"
            ,con=get_mysql_conn(db_name="fact"))