插入mysql数据库,pymysql无法插入

gcuhipw9  于 2021-06-21  发布在  Mysql
关注(0)|答案(4)|浏览(684)

我正在尝试将虚拟数据插入mysql数据库。
数据库结构如下所示: database name: messaround database table name: test 表结构: id (Primary key, auto increment) path (varchar(254)) 更新了下面的2个方法,出现错误。我有一个方法可以尝试通过以下方式插入:

def insert_into_db(dbcursor, table, *cols,**vals):
    try:
        query = "INSERT INTO {} ({}) VALUES ('{}')".format(table, ",".join(cols),  "'),('".join(vals))  
        print(query)
        dbcursor.execute(query)
        dbcursor.commit()
        print("inserted!")
    except pymysql.Error as exc:
        print("error inserting...\n {}".format(exc))

connection=conn_db()        
insertstmt=insert_into_db(connection, table='test', cols=['path'], vals=['test.com/test2'])

然而,这并不是说:

INSERT INTO test () VALUES ('vals'),('cols')
error inserting...
 (1136, "Column count doesn't match value count at row 1")

你能帮忙吗?
谢谢您。

ogq8wdun

ogq8wdun1#

如果使用代码:

def insert_into_db(dbcursor, table, *cols,**vals):
    query = "INSERT INTO {} ({}) VALUES ({})".format(table,",".join(cols), ",".join(vals))
    print(query)

insert_into_db('cursor_here', 'table_here', 'name', 'city', name_person='diego', city_person='Sao Paulo')

python返回:

INSERT INTO table_here (name,city) VALUES (name_person,city_person)

另一方面:

def new_insert_into_db(dbcursor, table, *cols,**vals):
    vals2 = ''
    for first_part, second_part in vals.items():
        vals2 += '\'' + second_part + '\','
    vals2 = vals2[:-1]
    query = "INSERT INTO {} ({}) VALUES ({})".format(table,",".join(cols), vals2)
    print(query)

new_insert_into_db('cursor_here', 'table_here', 'name', 'city', name_person='diego', city_person='Sao Paulo')

python将返回正确的sql:

INSERT INTO table_here (name,city) VALUES ('diego','Sao Paulo')
2q5ifsrm

2q5ifsrm2#

您应该打印您生成的sql语句,这样可以更容易地看到错误所在。
但我想你需要报价 ' 关于字符串值 ",".join(vals) (如果有字符串值。
所以你的代码产生了

insert into test (path,) values (test.com/test2,);

但它应该产生

insert into test (`path`) values ('test.com/test2');

否则试试看https://github.com/markuman/mariasql/ 这使得使用pymysql向mariadb/mysql插入数据非常容易。

fjnneemd

fjnneemd3#

通常在python中,将参数化查询传递给db驱动程序。参见pymysql文档中的这个例子;它构建了 INSERT 使用占位符进行查询,然后调用 cursor.execute() 传递查询和实际值的元组。
出于安全目的,还建议使用参数化查询,因为它可以抵御许多常见的sql注入攻击。

5f0d552i

5f0d552i4#

更改您的查询如下

query = "INSERT INTO {} ({}) VALUES ('{}')".format(table, ",".join(cols),  "'),('".join(vals))

在使用join时,变量应该是列表而不是字符串

table = 'test'
cols = ['path']
vals = ['test.com/test2', 'another.com/anothertest']

print(query)

"INSERT INTO test (path) VALUES ('test.com/test2'),('another.com/anothertest')"

更新:

def insert_into_db(dbconnection=None, table='', cols=None, vals=None):
    mycursor = dbconnection.cursor()
    if not (dbconnection and table and cols and vals):
        print('Must need all values')
        quit()
    try:
        query = "INSERT INTO {} ({}) VALUES ('{}')".format(table, ",".join(cols),  "'),('".join(vals))
        mycursor.execute(query)
        dbconnection.commit()
        print("inserted!")
    except pymysql.Error as exc:
        print("error inserting...\n {}".format(exc))

connection=conn_db()        

insertstmt=insert_into_db(dbconnection=connection, table='test', cols=['path'], vals=['test.com/test2'])

相关问题