python写Mysql数据库的问题问题描述：我想将一个csv文件导入数据库，在导入的时，首先查询expinfo表，得到最后
python写Mysql数据库的问题
问题描述：
我想将一个csv文件导入数据库，在导入的时，首先查询expinfo表，得到最后一条记录的id，然后在将当前csv数据写入数据表expdata时，用前面得到的id填充expdata表的exp_id域。举个例子：
csv数据：1，2，3
expdata表 x,y,z,exp_id
1,2,3,id
expdata表的定义如下：
- SQL code
CREATE TABLE IF NOT EXISTS `Experiment`.`expdata` ( `expdata_id` INT NOT NULL AUTO_INCREMENT , `exp_id` INT NULL , `x` FLOAT NULL , `y` FLOAT NULL , `z` FLOAT NULL , `angle01` FLOAT NULL , `angle02` FLOAT NULL , `angle03` FLOAT NULL , PRIMARY KEY (`expdata_id`) )ENGINE = InnoDB;
python代码是以一个网上找到的代码段为基础改的：
- Python code
#!/usr/bin/env python # Run with no args for usage instructions## Notes:# - will probably insert duplicate records if you load the same file twice# - assumes that the number of fields in the header row is the same# as the number of columns in the rest of the file and in the database# - assumes the column order is the same in the file and in the database## Speed: ~ 1s/MB# # usage:## csv2db.py root financial csvtable Book1.csv###import sysimport MySQLdbimport csvimport os def main(user, db, table, csvfile): try: conn = getconn(user, db,) except MySQLdb.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) sys.exit (1) cursor = conn.cursor() id1 = getExpId(conn,cursor) print "last id from getExpId is %d" % id1 loadcsv( conn,cursor, table, csvfile,id1) cursor.close() conn.close() def getconn(user, db, passwd=""): conn = MySQLdb.connect(host = "localhost", user = user, passwd = "521521", db = db) return conndef getExpId(conn, cursor): ''' get the last item's id in the tbincome table''' ''' so we can insert the csv data to the csvtable ''' try: cursor.execute("select max(exp_id) from expinfo") conn.commit() except MySQLdb.Error, e: print "Error %d: %s " %(e.args[0],e.args[1]) id = cursor.fetchone() print "last id in expinfo is %d" % id return id def nullify(L): """Convert empty strings in the given list to None.""" # helper function def f(x): if(x == ""): return None else: return x return [f(x) for x in L] def loadcsv(conn,cursor, table, filename,_exp_id): """ Open a csv file and load it into a sql table. Assumptions: - the first line in the file is a header """ f = csv.reader(open(filename)) header = f.next() numfields = len(header) query = buildInsertCmd(table, header,_exp_id,numfields) for line in f: vals = nullify(line) vals.append(_exp_id[0]) print len(vals) for x in vals: print x try: cursor.execute(query, vals) conn.commit() except MySQLdb.Error, e: print "Error %d: %s " %(e.args[0],e.args[1]) return def buildInsertCmd(table, header, _exp_id, numfields): """ Create a query string with the given table name and the right number of format placeholders. example: >>> buildInsertCmd("foo", 3) 'insert into foo values (%s, %s, %s)' """ assert(numfields > 0) placeholders = (numfields) * "%s, " + "%s" header.append('exp_id') query = ("insert into %s " % table) + ("(%s)" %header) +(" values (%s)" % (placeholders)) print query return query if __name__ == '__main__': # commandline execution args = sys.argv[1:] if(len(args) < 4): print "error: arguments: user db table csvfile" sys.exit(1) main(*args)
错误信息：
Error: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 '['x','y','z','angle01','angle02','angle03','exp_id']) values ('0.4','1.1''at line 1
环境:python 2.7 myslq 5.5
[解决办法]
int 和 float 貌似不需要用 ' 括起来吧
[解决办法]
[解决办法]
[解决办法]
'['x','y','z','angle01','angle02','angle03','exp_id']) values ('0.4','1.1''at line 1
提示语法错误，你打出query语句看看先，貌似("(%s)" %header)处理有问题，有些符号不该出现，改一下类似应该是:
query = "insert into %s (%s) values (%s)" % (table, ", ".join(header), placeholders)