CODEWITHSUNDEEP
pythonmysqldatabasebulkinsertpymysql
Bethlee
Bethlee

Reputation: 945

How can I run bulk insert query using pymysql?

How can I run insert query script file using pymysql?

I made a insert query script file for big data. (data row count: 50,000)

The file name is bulkinsert.sql and it contains query like below.

INSERT INTO test( dt, user, usertype, ip) VALUES 
("2016-05-01", 3103945, , 1, "175.195.249.217"), 
("2016-05-01", 3103946, , 1, "175.195.249.218"), 
("2016-05-01", 3103947, , 1, "175.195.249.219"), 
................
................
................
("2016-05-01", 3104000, , 1, "175.195.249.500");

I used pymysql, but I can't find a way to insert or run sql file directly. Like below.

cursor.execute(bulkinsert.sql)

When I read their guide, I have to read the sql sentence and execute it. But my data row count is too many to read. Is their any way to import or run sql script in pymysql?

Upvotes: 1

Views: 1716

Answers (1)

Hooting
Hooting

Reputation: 1711

If you don't need the output of execution, maybe subprocess is an alternative approach. PS: replace the Password keyword with your own.

from subprocess import Popen, PIPE
process = Popen(['mysql', db, '-u', user, '-pPassword'],
                stdout=PIPE, stdin=PIPE)
output = process.communicate('source ' + filename)[0]

Upvotes: 1

Related Questions