1

I want to automatically transfer some of the data in table 1 of server 1 to table 2 of server 2 with a python script. Can this be done with the cursor class or is there any other way of doing is? I'm fairly new to Python so please correct me if there is anything wrong about what I am asking.

Michael Wong
  • 59
  • 2
  • 13
  • 1
    Is there any particular reason you want to use python to do this? You can do it with MySQL client programs (specifically `mysqldump` and `mysql` CLI) – Barranka Aug 13 '15 at 00:03

1 Answers1

1

To obtain partial data from table 1 of server 1 you can perform a query and use a cursor inside Python. You can then iterate over the cursor and insert the data into table 2 of server 2.

To insert the data into table 2 of server 2 there are some recommendations:

  1. Set autocommit off and commit every (say) 500 records. Remember to commit at the end. This way you can speed up the insertion. [0]
  2. You can also create insert statements to insert many value sets at once. Take into account memory usage, don't put thousands of value sets into a single insert. [1]

[0] http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlconnection-autocommit.html

[1] How to use python mysqldb to insert many rows at once

Community
  • 1
  • 1
Camilo Torres
  • 478
  • 2
  • 8