1

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?

Bethlee
  • 825
  • 3
  • 17
  • 28

1 Answers1

1

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]
Hooting
  • 1,681
  • 11
  • 20
  • Thank you for helping me. Is it work when database is not in local database? – Bethlee May 04 '16 at 04:31
  • @Bethlee yes, add option of remote server ip by `-h REMOTE_SERVER_IP` – Hooting May 04 '16 at 04:46
  • I checked the option. Thank you. I have one more question, my local-pc didn't install mysql. Is it work? – Bethlee May 04 '16 at 05:08
  • I used popen to apply your comment. It is fast but have to install mysql connector in local pc – Bethlee May 04 '16 at 06:09
  • Thank you so much @Hooting~ I can solved my problem to use open("bulkinsert.sql").read() and execute it. http://stackoverflow.com/questions/1932298/cant-get-mysql-source-query-to-work-using-python-mysqldb-module Your solution is good to me also. Thank you! – Bethlee May 04 '16 at 06:12