python - 使用 python 从 csv 文件读取并放入具有不同字段类型的 Mysql

Question

我在 Mysql 中有“datetime”、“integer”、“double”、“string (varchar)”类型的表字段，如何使用 python 将字符串(来自 csv 文件)转换为每种类型的 Mysql 字段？我的 csv 文件示例:

欧元兑美元;M5;2011.01.05 02:10:00;1.33193;1.33193;1.33112;1.33135;0;-1;0;0;1.33215

我的Python代码是这样的:

import MySQLdb

# Open database connection 
db = MySQLdb.connect("localhost","me","xxxxx","test" )

# prepare a cursor object using cursor() method 
cursor = db.cursor()

inputFile = "source.csv"

with open(inputFile, 'r') as fr:
    data = fr.readlines()

    for line in data:
        words = line.strip().split(";")

        getpair = words[0]
        gettf = words[1]
        gettime = (datetime) words[2]
        getopen = (double) words[3]
        gethigh = (double) words[4]
        getlow  = (double) words[5]
        getclose = (double) words[6]
        getcUpdown = (int) words[7]
        getfractal = (int) words[8]
        getzzId = (int) words[9]
        getzzUpdown = (int) words[10]
        getzzEndPrc = (double) words[11]

        print(getpair,"=",gettf,"=",gettime,"=",getopen,"=",gethigh,"=",getlow,"=",getclose,"=",getcUpdown,"=",getfractal,"=",getzzId,"=",getzzUpdown,"=",getzzEndPrc)

    #  =====================================================================
    # Prepare SQL query to INSERT a record into the database.
    sql = '''INSERT INTO `mytable` (pair,timeframe,time,open,close,high,low,updown,fractal,zzid,zzupdown,zzlength) \
         VALUES (getpair,gettf,gettime,getopen,getclose,gethigh,getlow,getcUpdown,getfractal,getzzId,getzzUpdown,getzzEndPrc)'''

    try:
       # Execute the SQL command
       cursor.execute(sql)
       # Commit your changes in the database
       db.commit()
    except:
       # Rollback in case there is any error
       db.rollback()

    # disconnect from server
    db.close()

fr.close()

谢谢

Answer 1

1. Python 中的类型转换是通过以下方式完成的:

   int(words[7])
   

   不是

   (int)words[7]
   

2. Python 中没有 double 类型。请改用float。

3. 您无法直接转换为日期时间。使用 datetime.strptime() 解析输入字符串:

   from datetime import datetime
   gettime = datetime.strptime(words[2], '%Y.%m.%d %H:%M:%S')
   

   您可能不需要这样做(在使用参数化查询时应该处理转换 - 见下文)，但您可以将其转换为 MySQLDB 支持的字符串格式。您可以使用 strftime() 来实现:

   gettime = gettime.strftime('%Y-%m-%d %H:%M:%S')
   

4. sql查询字符串需要替换实际值。你的查询插入变量的名称，而不是值(value)观。您可以(并且应该)使用如下所示的参数化查询:

   sql = 'INSERT INTO `mytable` (pair, timeframe, time, open, close, high, low, updown, fractal, zzid, zzupdown, zzlength) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
   cursor.execute(sql, (getpair, gettf, gettime, getopen, getclose, gethigh, getlow, getcUpdown, getfractal, getzzId, getzzUpdown, getzzEndPrc))
   

5. cursor.execute(sql, ...) 需要移至 for 循环体中。