sql语句添加变量 pymysql.err.ProgrammingError: (1064, “You have an error in your SQL syntax

问题描述：

我在向sql语句中添加变量时

import pymysql
db_path = "hang"
serch = "haha"
sql = '''
        create table 工作信息(%s)
           (
            infoId INTEGER PRIMARY KEY AUTO_INCREMENT,
            infoTopic VARCHAR(100),
            infoContent VARCHAR(1000),
            infoURL VARCHAR(255),
            userPhone varbinary(30),
            infoCreatDate VARCHAR(30),
            infoVisitedCount int,
            infoColletedCount int,
            infoType int,
            restrictArea VARCHAR(30),
            infoPublisher VARCHAR(30),
            infoPictureUrl VARCHAR(300)
           )
 '''# 创建数据表 
# sql1 = sql.format(serch)
conn = pymysql.connect(host='localhost',user='root',passwd='6300975as',db=db_path,port=3306,charset='utf8')
cursor = conn.cursor()
cursor.execute("drop table if EXISTS  工作信息%s",(serch))
cursor.execute(sql,(serch))
conn.commit()
conn.close()
print("成功！")

系统提示我出现错误

pymysql.err.ProgrammingError: (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 ''haha'' at line 1")

---

解决方案：

我的解决方案是：

import pymysql
db_path = "hang"
serch = "haha"
sql = '''
        create table 工作信息%s
           (
            infoId INTEGER PRIMARY KEY AUTO_INCREMENT,
            infoTopic VARCHAR(100),
            infoContent VARCHAR(1000),
            infoURL VARCHAR(255),
            userPhone varbinary(30),
            infoCreatDate VARCHAR(30),
            infoVisitedCount int,
            infoColletedCount int,
            infoType int,
            restrictArea VARCHAR(30),
            infoPublisher VARCHAR(30),
            infoPictureUrl VARCHAR(300)
           )
 '''%serch# 创建数据表 
conn = pymysql.connect(host='localhost',user='root',passwd='6300975as',db=db_path,port=3306,charset='utf8')
cursor = conn.cursor()
cursor.execute("drop table if EXISTS  工作信息%s"%serch)
cursor.execute(sql)
conn.commit()
conn.close()
print("成功！")

我是直接在含有“%s”的sql语句后通过“%”添加内容，而没有在execute中进行操作，并且sql语句中括号也不能乱加，我原先的代码如果保留括号也还是错的

sql = '''
        create table 工作信息%s
           (
            infoId INTEGER PRIMARY KEY AUTO_INCREMENT,
            infoTopic VARCHAR(100),
            infoContent VARCHAR(1000),
            infoURL VARCHAR(255),
            userPhone varbinary(30),
            infoCreatDate VARCHAR(30),
            infoVisitedCount int,
            infoColletedCount int,
            infoType int,
            restrictArea VARCHAR(30),
            infoPublisher VARCHAR(30),
            infoPictureUrl VARCHAR(300)
           )
 '''%serch

cursor.execute("drop table if EXISTS  工作信息%s"%serch)

可以看到，运行成功

"C:\Users\老李\Desktop\Python项目\the one\venv\Scripts\python.exe" C:/Users/老李/Desktop/Python项目/智慧信使-ACV数据爬取码源/代码/text2.py
成功！

Process finished with exit code 0

注释：网上不少大佬的方法我都试过，并没有解决我的问题，希望我的这种解决方法能帮到大家！