Read from csv file and put to Mysql with different field type using python

Question

i have table field of "datetime", "integer", "double", "string (varchar)" type in Mysql, how can i convert from string (from csv file) to each type of Mysql field using python ? example of my csv file :

EURUSD;M5;2011.01.05 02:10:00;1.33193;1.33193;1.33112;1.33135;0;-1;0;0;1.33215

My python code is like this :

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

Thanks p

mhawke · Accepted Answer · 2015-03-11 05:44:28Z

1

Type conversion in Python is done by:
```
int(words[7])
```
not
```
(int)words[7]
```
There is no double type in Python. Use float instead.
You can not convert directly to datetime. Use datetime.strptime() to parse the input string:
```
from datetime import datetime
gettime = datetime.strptime(words[2], '%Y.%m.%d %H:%M:%S')
```
You might not need to do this (conversion should be handled when using parameterised queries - see below), but you can convert that to a string format that MySQLDB supports. You can use strftime() for that:
```
gettime = gettime.strftime('%Y-%m-%d %H:%M:%S')
```

The sql query string needs to substitute the the actual values. Your query is inserting the names of the variables, not the values. You can (and should) use a parameterised query like this:

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

cursor.execute(sql, ...) needs to be moved into the body of the for loop.

edited Mar 11, 2015 at 5:44

answered Mar 11, 2015 at 5:06

mhawke

87.5k10 gold badges122 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user2919408 Over a year ago

Thanks a lot mhawke. I can not vote up, so just do a comment here. Tq.

user2919408 Over a year ago

Another question, regarding point (5): About "for" loop, in php we can do it in {}, like : for (i=0, i<10; i++) { xxxxxxxxxxxx } how to do that in python ?

mhawke Over a year ago

@user2919408 : no problem. You may not be able to upvote, but you can accept the answer if it answers your question.

mhawke Over a year ago

@user2919408: in general PHP uses {} to determine the scope of the for loop. Python, however, uses indentation to determine the scope. So, in your code, you would need to indent the lines for executing the SQL statement to be at the same level of indentation as the print statement. You could move the SQL statement "template" string outside of the loop because it will be the same on every iteration.

user2919408 Over a year ago

Got it, thanks a lot mhawke. You are really great ..... And how can i "accept" the answer ? I can not find any icon / link ... You have given great answer to my question. Thanks

Collectives™ on Stack Overflow

Read from csv file and put to Mysql with different field type using python

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related