python读写mysql数据库

python读写mysql数据库

一。读
1 .密码里面有特殊字符，例如@

from urllib.parse import quote_plus
#用户名：user1 密码：user1 数据库地址:127.0.0.1:3306 数据库名:db1
db_engin='mysql+pymysql://user1:%[email protected]:3306/db1' %quote_plus('pass@aaa')

如果没有特殊字符串

#密码password
db_engin='mysql+pymysql://user1:[email protected]:3306/db1'

3.连接数据库
结果用dataframe保存起来，还加上异常处理

from sqlalchemy import create_engine
from sqlalchemy import exc
import pandas as pd
conn = create_engine(db_engin)
sqlcmd1='SELECt * FROM table1 WHERe id=123'
    try:
        df = pd.read_sql_query(sqlcmd1, conn)
    except exc.OperationalError as e:
        print(e)

二。写数据库
将dataframe写入数据库

#db1是数据库名。table1是表名 db_engin就是上面读的那个数据库
 conn = create_engine(db_engin)
    try:
        pd.io.sql.to_sql(df, table1,con=conn, schema='db1', if_exists='append', index=False)
        # 关闭
        conn.dispose()
    except exc.OperationalError as e:
        print("writedb",e)