python 执行多个sql语句，实现大批量追加数据

建立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 "导出文件完成。"