Load csv into mysql using Python syntax error [duplicate]

Question

I have a TSV file with the following structure:

#DAteTime    Userid    tweet (each separated by \t)
2009-06-07 02:07:41 http://twitter.com/hokiepokie728    @fabro84 'Before the Storm' is a new song from the Jonas Brothers that is going to be on their new album. miley has a duet with nick on it!
2009-06-07 02:07:42 'http://twitter.com/annieng'    is in LA now

How can I import this file into a MySQL table called tweet with python? I have the following table structure:

1   DT  datetime
2   USER    varchar(141)    
3   TWEET   varchar(141)    
4   ID  bigint(20)          AUTO_INCREMENT

Well, the file size is around 3 GB. The query that am trying is: import MySQLdb db = MySQLdb.connect("localhost","root","", "TWEETS" ) sql = """LOAD DATA LOCAL INFILE 'sample.txt' INTO TABLE tweet FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' OPTIONALLY ENCLOSED BY '\' ESCAPED BY '\\' (DT,USER,TWEET)""" cursor = db.cursor() try: cursor.execute(str(MySQLdb.escape_string(sql))) #cursor.execute(sql) db.commit() except MySQLdb.IntegrityError: db.rollback() logging.warn("Failed to insert values ") db.close() — user3623182
– user3623182, Commented Jul 25, 2014 at 11:49

Peeyush · Accepted Answer · 2014-07-25 07:15:23Z

0

with mysql-connector-python 1.2.2 you can do it like this

import mysql.connector
from mysql.connector.constants import ClientFlag

cnx = mysql.conector.connect(client_flags=[ClientFlag.LOCAL_FILES], ....)

cur = cnx.cursor()
cur.execute("LOAD DATA LOCAL INFILE '</path/to/data/file>' INTO "
            "TABLE tweet")

answered Jul 25, 2014 at 7:15

Peeyush

7264 silver badges11 bronze badges

Add a comment |

Collectives™ on Stack Overflow

Load csv into mysql using Python syntax error [duplicate]

1 Answer 1

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Linked

Related