本文介绍如何在Python中使用Pandas库实现MySQL数据库的读写&操作。
数据库信息如下:
user_name = 'root'
password = '12345678'
address = 'localhost'
port = 3306
database_name = 'rkqy_wwsj'
一、python 读取Mysql
#读取表'modle_test_20190906'中的所有数据
def Read_database (user_name,password,address,port,database_name,sql):
import pymysql
import pandas as pd
conn = pymysql.connect(host = address,user = user_name,passwd = password,\
db = database_name , port = int(port) ,charset = "utf8mb4")
try:
df = pd.read_sql (sql,con = conn)
except:
print ('\n Reading Error \n')
finally:
conn.close()
print ('\n Completion of data reading \n')
return (df)
sql = '''
SELECT * from modle_test_20190906;
'''
df = Read_database (user_name,password,address,port,database_name,sql)
二、python 写入Mysql
#将数据导入'rkqy_wwsj'库,其中的'scs_rk_qc_all'表
def Write_to_database(df,user_name,password,address,port,database_name,table_name):
import pandas as pd
import sqlalchemy
engine = sqlalchemy.create_engine('mysql+pymysql://{}:{}@{}:{}/{}?charset=utf8'.format(user_name,password,address,int(port),database_name))
try:
pd.io.sql.to_sql(frame = df,name = table_name,con=engine,if_exists='replace',index = False)
except:
print ('\n Writting Error \n')
finally:
engine.dispose()
print ('\n Data has been written \n')
table_name = 'scs_rk_qc_all'
df = pd.read_excel(r'C:\Users\Administrator\Desktop\test.xlsx',sheet_name = 'test1')
Write_to_database(df,user_name,password,address,port,database_name,table_name)
三、python 操作Mysql
#操作数据库,删除'scs_rk_qc_all'表
def Operating_database (user_name,password,address,port,database_name,sql):
import pymysql
import pandas as pd
conn = pymysql.connect(host = address,user = user_name,passwd = password,\
db = database_name , port = int(port) ,charset = "utf8mb4")
cur = conn.cursor() # 使用cursor()方法获取操作游标
try:
cur.execute(sql) #像sql语句传递参数
conn.commit() #提交
print ('\n Completion of database operation \n')
except Exception as e:
conn.rollback() #错误回滚
print ('\n Operation error \n')
finally:
conn.close()
sql = '''
drop table scs_rk_qc_all
'''
Operating_database (user_name,password,address,port,database_name,sql)
网友评论