玩蛇网提供最新Python编程技术信息以及Python资源下载!

Python mysql数据牗executemany指定写入表报错

curs.executemany('insert into dbname values(%s, %s, %s, %s)', file_value)

sql语句如上,需要对dbname定义
我试了在参数那里加上表名的位置,失败:

file_value = [(tables,1,2,3,4)]
curs.executemany('insert into %s values(%s, %s, %s, %s)', file_value)
# 报错:
not all arguments converted during string formatting

用++带入表名也不行:

curs.executemany('insert into '+tables+' values(%s, %s, %s, %s)', file_value)
# 报错:
(1136, "Column count doesn't match value count at row 1")

请问该如何处理?

自行用字符串替换吧,看了下手册excutemany似乎不是你这么用的啊,它的参数就是要INSERT进去的数据而已,不是纯粹的替换:http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html

sql='insert into %s values(%s)' % ("dbname ","%s, %s, %s, %s")
curs.executemany(sql, file_value)

楼主,刚敲了一下代码,executemany执行时最好对应个数。

#!/usr/bin/env python
# -- coding:utf-8 --

import logging
import MySQLdb

class _MySQL(object):
     def __init__(self, host, port, user, passwd, db, charset='utf8'):
        self.conn = MySQLdb.connect(
                host = host,
                port = port,
                user = user,
                passwd = passwd,
                db = db,
                charset = charset)

    def get_cursor(self):
        return self.conn.cursor()

    def query(self, sql):
        cursor = self.get_cursor()  
        try:
            cursor.execute(sql, None)
            result = cursor.fetchall()  
        except Exception, e:
            logging.error("mysql query error: %s", e)
            return None
        finally:
            cursor.close()
        return result

    def execute(self, sql, param=None):
        cursor = self.get_cursor()
        try:
            cursor.execute(sql, param)
            self.conn.commit()
            affected_row = cursor.rowcount
        except Exception, e:
            logging.error("mysql execute error: %s", e)
            return 0
        finally:
            cursor.close()
        return affected_row

    def executemany(self, sql, params=None):
        cursor = self.get_cursor()
        try:
            cursor.executemany(sql, params)
            self.conn.commit()
            affected_rows = cursor.rowcount
        except Exception, e:
            logging.error("mysql executemany error: %s", e)
            return 0
        finally:
            cursor.close()
        return affected_rows

    def close(self):
        try:
            self.conn.close()
        except:
            pass

    def __del__(self):
        self.close()


host = 'localhost'
port = 3306
user = 'root'
passwd = '123456'
db = 'foo'

mysql = _MySQL(host, port, user, passwd, db)

def create_table():
    table = """
            CREATE TABLE IF NOT EXISTS `watchdog`(
                `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
                `name` varchar(100),
                `price` int(11) NOT NULL DEFAULT 0
            ) ENGINE=InnoDB charset=utf8;
            """
    print mysql.execute(table)

def insert_data():
    params = [('dog_%d' % i, i) for i in xrange(12)]
    sql = "INSERT INTO `watchdog`(`name`,`price`) VALUES(%s,%s);"   
    print mysql.executemany(sql, params) 

if __name__ == '__main__':
    create_table()
    insert_data()

玩蛇网文章,转载请注明出处和文章网址:https://www.iplaypy.com/wenda/wd19337.html

相关文章 Recommend

玩蛇网Python互助QQ群,欢迎加入-->: 106381465 玩蛇网Python新手群
修订日期:2017年05月24日 - 17时48分43秒 发布自玩蛇网

您现在的位置: 玩蛇网首页 > Python问题解答 > 正文内容
我要分享到:

必知PYTHON教程 Must Know PYTHON Tutorials

必知PYTHON模块 Must Know PYTHON Modules