python写Mysql数据库的有关问题

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 sys
import MySQLdb
import csv
import 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 conn

def 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)

python写Mysql数据库的有关问题

相关推荐