Python pandas连接mysql数据库 实例

最新实例：

# coding:utf-8
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.exc import SQLAlchemyError


engine=create_engine('mysql+pymysql://root:123)@123asd.com:3306/test_leftjoin_backup')
result = engine.execute("update test set a=33333 where id<30;" )
result = engine.execute("select * from test where id<5" )
print(result.rowcount)
print(result.returns_rows)
print(result.fetchall())
print(result)
print(dir(result))

#session操作----------
session_name = sessionmaker(bind=engine)
session = session_name() #实例化session
try:#sql错误检查处理
    result = session.execute("update test set a=121 where id<30;")
    print(result.rowcount)
    # 处理结果
except SQLAlchemyError as e:
    print("SQL错误:", e)
result = session.execute('select ROW_COUNT();')
for row in result:
    print(row)
#提交持久化
session.commit()
# 关闭会话
session.close()

 

 

 

首先安装包

pip install pandas
pip install sqlalchemy
pip install pymysql

初始化数据库连接：

import pandas as pd
from sqlalchemy import create_engine

# 初始化数据库连接
# 按实际情况依次填写MySQL的用户名、密码、IP地址、端口、数据库名
engine = create_engine('mysql+pymysql://root:12345678@localhost:3306/testdb')

# 如果觉得上方代码不够优雅也可以按下面的格式填写
# engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format('root', '12345678', 'localhost', '3306', 'testdb'))

 

# MySQL导入DataFrame
# 填写自己所需的SQL语句，可以是复杂的查询语句
sql_query = 'select * from product;'
# 使用pandas的read_sql_query函数执行SQL语句，并存入DataFrame
df_read = pd.read_sql_query(sql_query, engine)
print(df_read)

# DataFrame写入MySQL
# 新建DataFrame
df_write = pd.DataFrame({'id': [10, 27, 34, 46], 'name': ['张三', '李四', '王五', '赵六'], 'score': [80, 75, 56, 99]})
# 将df储存为MySQL中的表，不储存index列
df_write.to_sql('testdf', engine, index=False)

此外还可以直接执行sql语句

# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES ('1.1.1.22', 3)"
# )
 
# 新插入行自增ID
# cur.lastrowid
 
# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES(%s, %s)",[('1.1.1.22', 3),('1.1.1.221', 3),]
# )
 
 
# 执行SQL
# cur = engine.execute(
#     "INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)",
#     host='1.1.1.99', color_id=3
# )
 
# 执行SQL
# cur = engine.execute('select * from hosts')

cur.lastrowid #这个是取插入id

# 获取第一行数据 # cur.fetchone() # 获取第n行数据 # cur.fetchmany(3) # 获取所有数据 # cur.fetchall()