Python插入mysql的变量问题

问题

先上代码

import pymysql
import time
import random
import uuid
import threading

# 连接mysql
db = pymysql.connect(host='10.10.100.184', port=3308,
                     user='root', passwd='3hyHd1MLARaMkUJsr3dp', db='iot')
# 获取mysql操作光标
cursor = db.cursor()
# 初始化变量
count = 0
# 设置sql语句循环次数
print(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
while count < 5:
    count += 1
    # 定义mysql字段的范围随机数变量
    quyu = random.randint(440300, 440310)
    jingdu = random.uniform(99, 117)
    weidu = random.uniform(23, 41)
    city_list = [11, 12, 13, 14, 15, 25, 35, 37, 38]
    city_id = random.choice(city_list)
    # driver_name = f"'cs{count}'"
    driver_name = 'cs'
    print(driver_name)
    print(type(driver_name))
    driver_type_list = [111,311,812,813,814,911,1111,1211,1311]
    driver_type = random.choice(driver_type_list)
    online = [0,1]
    line = random.choice(online)
    # 生成mysql语句插入语句
    sql = "insert into assets (`user_id`, `company_id`, `province_id`, `city_id`, `district_id`, `assets_name`, `assets_code`, `device_num`, `zn_device_type_id`, `device_type_id`, `project_id`, `longtitude`, `latitude`, `create_time`, `assets_picture`, `address`, `order_num`, `description`, `description_two`, `is_online`, `assets_type_picture`, `product_material`, `online_time`, `offline_time`)\
                        values ('554','99','440000','440300',{},{},'ghhgfftyhddd',{},NULL,{},'1',{},{},'2020-12-15 17:09:04','[\"http://10.10.100.184:9997/images/1111608021229407238.jpg\"]','广东省深圳市龙岗区宝龙街道新能源一路科信科技大厦',NULL,NULL,NULL,{},NULL,NULL,NULL,NULL);".format(quyu,driver_name,driver_name,driver_type,jingdu,weidu,line)
    print(sql)
    # 执行sql语句
    try:
        cursor.execute(sql)
        db.commit()
    # 错误回滚
    except:
        db.rollback()
# 关闭mysql
print(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))

db.close()

可以看到'cs'在传递的过程中去掉了引号，导致数据操作不成功,于是我们在外面在嵌套一层引号，变成"'cs'"

# encoding:utf-8
import pymysql
import time
import random
import uuid
import threading


# 连接mysql
db = pymysql.connect(host='10.10.100.184', port=3308,
                     user='root', passwd='3hyHd1MLARaMkUJsr3dp', db='iot')
# 获取mysql操作光标
cursor = db.cursor()
# 初始化变量
count = 0
# 设置sql语句循环次数
print(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
while count < 5:
    count += 1
    # 定义mysql字段的范围随机数变量
    quyu = random.randint(440300, 440310)
    jingdu = random.uniform(99, 117)
    weidu = random.uniform(23, 41)
    city_list = [11, 12, 13, 14, 15, 25, 35, 37, 38]
    city_id = random.choice(city_list)
    # driver_name = f"'cs{count}'"
    driver_name = "'cs'"
    print(driver_name)
    print(type(driver_name))
    driver_type_list = [111,311,812,813,814,911,1111,1211,1311]
    driver_type = random.choice(driver_type_list)
    online = [0,1]
    line = random.choice(online)
    # 生成mysql语句插入语句
    sql = "insert into assets (`user_id`, `company_id`, `province_id`, `city_id`, `district_id`, `assets_name`, `assets_code`, `device_num`, `zn_device_type_id`, `device_type_id`, `project_id`, `longtitude`, `latitude`, `create_time`, `assets_picture`, `address`, `order_num`, `description`, `description_two`, `is_online`, `assets_type_picture`, `product_material`, `online_time`, `offline_time`)\
                        values ('554','99','440000','440300',{},{},'ghhgfftyhddd',{},NULL,{},'1',{},{},'2020-12-15 17:09:04','[\"http://10.10.100.184:9997/images/1111608021229407238.jpg\"]','广东省深圳市龙岗区宝龙街道新能源一路科信科技大厦',NULL,NULL,NULL,{},NULL,NULL,NULL,NULL);".format(quyu,driver_name,driver_name,driver_type,jingdu,weidu,line)
    print(sql)
    # 执行sql语句
    try:
        cursor.execute(sql)
        db.commit()
    # 错误回滚
    except:
        db.rollback()
# 关闭mysql
print(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))

db.close()

传递的参数有引号了，我们也检查了数据库，数据插入成功。

下面我来进行字符串和数字拼接,同时数字是一个变量，于是这么写：f"'cs{count}'"

# encoding:utf-8
import pymysql
import time
import random
import uuid
import threading


# 连接mysql
db = pymysql.connect(host='10.10.100.184', port=3308,
                     user='root', passwd='3hyHd1MLARaMkUJsr3dp', db='iot')
# 获取mysql操作光标
cursor = db.cursor()
# 初始化变量
count = 0
# 设置sql语句循环次数
print(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
while count < 5:
    count += 1
    # 定义mysql字段的范围随机数变量
    quyu = random.randint(440300, 440310)
    jingdu = random.uniform(99, 117)
    weidu = random.uniform(23, 41)
    city_list = [11, 12, 13, 14, 15, 25, 35, 37, 38]
    city_id = random.choice(city_list)
    driver_name = f"'cs{count}'"
    print(driver_name)
    print(type(driver_name))
    driver_type_list = [111,311,812,813,814,911,1111,1211,1311]
    driver_type = random.choice(driver_type_list)
    online = [0,1]
    line = random.choice(online)
    # 生成mysql语句插入语句
    sql = "insert into assets (`user_id`, `company_id`, `province_id`, `city_id`, `district_id`, `assets_name`, `assets_code`, `device_num`, `zn_device_type_id`, `device_type_id`, `project_id`, `longtitude`, `latitude`, `create_time`, `assets_picture`, `address`, `order_num`, `description`, `description_two`, `is_online`, `assets_type_picture`, `product_material`, `online_time`, `offline_time`)\
                        values ('554','99','440000','440300',{},{},'ghhgfftyhddd',{},NULL,{},'1',{},{},'2020-12-15 17:09:04','[\"http://10.10.100.184:9997/images/1111608021229407238.jpg\"]','广东省深圳市龙岗区宝龙街道新能源一路科信科技大厦',NULL,NULL,NULL,{},NULL,NULL,NULL,NULL);".format(quyu,driver_name,driver_name,driver_type,jingdu,weidu,line)
    print(sql)
    # 执行sql语句
    try:
        cursor.execute(sql)
        db.commit()
    # 错误回滚
    except:
        db.rollback()
# 关闭mysql
print(time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))

db.close()

成功。