建立mysql的链接
def mysqlconn():
# 导入数据库操作包
# 数据库的基本信息[主机、用户名、密码、端口号、连接的数据库]
Host = '127.0.0.1'
user = 'new***'
pwd = '****_2022'
port = 3306
database = 'qx2****'
# 进行创建数据库的连接
#conn = pymysql.connect(host=Host, user=user, password=pwd, port=port, db=database,local_infile=1)
#需要导入文件需要增加 infile
conn = pymysql.connect(host=Host, user=user, password=pwd, port=port, db=database)
##print(conn)
return conn
利用链接对象建立游标
def db_excute_sql(sql):
db=mysqlconn()
db.set_charset('utf8')
cursor = db.cursor()
try:
cursor.execute(sql)
db.commit()
except pymysql.Error as e:
print(e)
return e
finally:
db.close()
单独测试一条sql命令
sql="truncate new_t"
db_excute_sql(sql)
如果把多个sql命令组成list逐条执行可以再编写一个参数是list的函数
def db_excute_sql_list(sql_list):
db=mysqlconn()
db.set_charset('utf8')
cursor = db.cursor()
for sql in sql_list:
try:
cursor.execute(sql)
db.commit()
except pymysql.Error as e:
print(e)
return e
db.close()
测试多个sql命令的list
sql_l=[]
sql="truncate new_t"
sql_l.append(sql)
sql=" LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/test1.csv' INTO TABLE new_t FIELDS TERMINATED BY ',' lines terminated by '\\r\\n' ignore 1 lines (id, nameqqq,new_tcol);"
sql_l.append(sql)
sql="insert into new_ttt(nameqqq, data) SELECT nameqqq, data FROM new_t"
sql_l.append(sql)
db_excute_sql_list(sql_l)
把文件导入临时表,让后再mysql中进行追加,这应该最快的大批量追加数据的方法。
特别注意,成功执行 LOAD DATA INFILE 命令需要有比较复杂的设置。
你必须要在mysql测试成功以后再可以在python中调用这个命令。
你可能会会遇到如下错误
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '工作簿1.csv LOAD DATA INFILE 'd:/工作簿1.csv' -> INTO TABLE new_t ' at line 1
#忘记什么原因了
Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
#数据库都在安全模式,没有开启导入功能
Error Code: 29. File 'C:\ProgramData\MySQL\MySQL Server 8.0\Data\ProgramDataMySQLMySQL Server 8.0Uploads est1.csv' not found (OS errno 2 - No such file or directory)
Error Code: 29. File 'C:\ProgramData\MySQL\MySQL Server 8.0\Data\ProgramDataMySQL est1.csv' not found (OS errno 2 - No such file or directory)
Error Code: 29. File 'C:\ProgramData\MySQL\MySQL Server 8.0\Data\qx202204\test1.csv' not found (OS errno 2 - No such file or directory)
#导入文件路径secure_file_priv设置不对,导致导入文件路径不正常,其实到最后我也没有搞明白,我用的默认路径C:\ProgramData\MySQL\MySQL Server 8.0\Data\
Error Code: 1366. Incorrect string value: '\xC4\xE3' for column 'name' at row 1
#导入的文件使用的是GB2312不是UTF8
Error Code: 1300. Invalid utf8mb4 character string: '3'
#数据库的编码不对
可以参考文章
(2条消息) mysql提示错误[Error Code] 1290 - The MySQL server is running with the --secure-file-priv option解决办法..._Claire_ljy的博客-CSDN博客(2条消息) LOAD DATA INFILE使用与详解_longzhoufeng的博客-CSDN博客
上面的sql命令都是操作数据库的,没有返回值
执行既有返回值的查询命令可以使用这个函数
def sql_to_list(str_sql):
sql_list=[]
conn = mysqlconn()
# 获取游标
cursor = conn.cursor()
cursor.execute(str_sql)
results = cursor.fetchall()
for r in results:
sql_list.append(r)
cursor.close()
conn.close()
return sql_list
把数据的表直接导出文件
from sqlalchemy import create_engine
import pandas as pd
def msg_to_excel(sql, saveto):
engine = create_engine('mysql+pymysql://newuser:¥¥¥¥¥@127.0.0.1:3306/qx2####204')
df_read = pd.read_sql_query(sql, engine)
df_read.to_excel(saveto, index=True)
return "导出文件完成。"