【MySQL+Python】使用pymysql连接数据库并执行操作

1.使用pymysql库与数据库建立连接

 conn = pymysql.connect(
        # 服务器地址
        host='10.10.10.10',
        # 端口号
        port=3306,
        # 用户名
        user='username',
        # 密码
        passwd='password',
        # 数据库名称
        db='data',
        # 字符编码格式
        charset='utf8',
    )
    cursor = conn.cursor()

2.定义多段SQL语句并执行

2.1 使用f’‘’…‘’‘将python变量传递到SQL语句中，写法为’{variable}’

 # 定义多段SQL语句
        sql_statements = [
            f'''
                CREATE TABLE pic_data_cache AS
                SELECT *
                FROM pic_data
                WHERE pic_data.pictime BETWEEN '{begin_time}' and '{end_time}'
                AND (pic_data.gantryid = '{gantry_id1}' OR pic_data.gantryid = '{gantry_id2}' OR pic_data.gantryid = '{gantry_id3}' OR pic_data.gantryid = '{gantry_id4}')
            ''',

            f'''
                CREATE TABLE gantry_data_cache AS
                SELECT *
                FROM gantry_data
                WHERE gantry_data.transTime BETWEEN '{begin_time}' and '{end_time}'
                AND (gantry_data.gantryId = '{gantry_id1}' OR gantry_data.gantryId = '{gantry_id2}' OR gantry_data.gantryId = '{gantry_id3}' OR gantry_data.gantryId = '{gantry_id4}')
                ''',

        ]

        # 逐个执行并提交
        for sql in sql_statements:
            cursor.execute(sql)
            conn.commit()  # 每个execute后commit
            print('SQL语句已执行！')

2.2 或使用%s传递参数

# 操作
pic_into = "INSERT INTO chinaetc.user_data(user_time, user_ip, user_pic) VALUES(%s, %s, %s)"
# 执行
cursor.execute(pic_into, (local_time, local_ip, img_byte_arr,))
conn.commit()

2.3 或在执行execute中包含语句和参数

cursor.execute('''
                  INSERT INTO pic_data (id, gantryid, gantryhex, pictime)
                  VALUES (%s, %s, %s, %s)
                  ''', args=(
			                _id,
			                gantryid,
			                gantryhex,
			                pictime,)
                )

3.执行完关闭连接(一定要关闭)

conn.close()

4.完整代码

import mysql.connector
import pymysql


def batch_screening(gantry_id1, gantry_id2, gantry_id3, gantry_id4, begin_time, end_time):
    # 连接到SQLite数据库
     conn = pymysql.connect(
        # 服务器地址
        host='10.10.10.10',
        # 端口号
        port=3306,
        # 用户名
        user='username',
        # 密码
        passwd='password',
        # 数据库名称
        db='data',
        # 字符编码格式
        charset='utf8',
    )
    cursor = conn.cursor()

    try:
        # 定义多段SQL语句
        sql_statements = [
            f'''
                CREATE TABLE pic_data_cache AS
                SELECT *
                FROM pic_data
                WHERE pic_data.pictime BETWEEN '{begin_time}' and '{end_time}'
                AND (pic_data.gantryid = '{gantry_id1}' OR pic_data.gantryid = '{gantry_id2}' OR pic_data.gantryid = '{gantry_id3}' OR pic_data.gantryid = '{gantry_id4}')
            ''',

            f'''
                CREATE TABLE gantry_data_cache AS
                SELECT *
                FROM gantry_data
                WHERE gantry_data.transTime BETWEEN '{begin_time}' and '{end_time}'
                AND (gantry_data.gantryId = '{gantry_id1}' OR gantry_data.gantryId = '{gantry_id2}' OR gantry_data.gantryId = '{gantry_id3}' OR gantry_data.gantryId = '{gantry_id4}')
                '''
        ]

        # 逐个执行并提交
        for sql in sql_statements:
            cursor.execute(sql)
            conn.commit()  # 每个execute后commit
            print('SQL语句已执行！')

    finally:
        conn.close()