Loading large mysql select statements into python
up vote
2
down vote
favorite
I have two mysql databases called database A and database B. I want to use python to copy all the data from a table in database A to a table in database B. The datatable holds approximately 5M rows. I have written the following code:
connection = make_connection(source_db)
c = connection.cursor()
batch_size = 100000
while True:
batch = c.fetchmany(batch_size)
print('Extracted a batch.')
if not batch:
break
insert_data(batch)
connection.close()
I ran this code and it takes minutes for fetchmany() to extract a single batch. I've tried to use fetchall() without the while True statement but this takes even longer.
Question: How do I successfully speed up this process/what is the best way to load large select statements into python?
mysql python-3.x
asked Nov 8 at 11:02
Mr. President
3949
add a comment |
up vote
2
down vote
favorite
I have two mysql databases called database A and database B. I want to use python to copy all the data from a table in database A to a table in database B. The datatable holds approximately 5M rows. I have written the following code:
connection = make_connection(source_db)
c = connection.cursor()
batch_size = 100000
while True:
batch = c.fetchmany(batch_size)
print('Extracted a batch.')
if not batch:
break
insert_data(batch)
connection.close()
I ran this code and it takes minutes for fetchmany() to extract a single batch. I've tried to use fetchall() without the while True statement but this takes even longer.
Question: How do I successfully speed up this process/what is the best way to load large select statements into python?
mysql python-3.x
asked Nov 8 at 11:02
Mr. President
3949
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
I have two mysql databases called database A and database B. I want to use python to copy all the data from a table in database A to a table in database B. The datatable holds approximately 5M rows. I have written the following code:
connection = make_connection(source_db)
c = connection.cursor()
batch_size = 100000
while True:
batch = c.fetchmany(batch_size)
print('Extracted a batch.')
if not batch:
break
insert_data(batch)
connection.close()
I ran this code and it takes minutes for fetchmany() to extract a single batch. I've tried to use fetchall() without the while True statement but this takes even longer.
Question: How do I successfully speed up this process/what is the best way to load large select statements into python?
mysql python-3.x
asked Nov 8 at 11:02
Mr. President
3949
I have two mysql databases called database A and database B. I want to use python to copy all the data from a table in database A to a table in database B. The datatable holds approximately 5M rows. I have written the following code:
connection = make_connection(source_db)
c = connection.cursor()
batch_size = 100000
while True:
batch = c.fetchmany(batch_size)
print('Extracted a batch.')
if not batch:
break
insert_data(batch)
connection.close()
I ran this code and it takes minutes for fetchmany() to extract a single batch. I've tried to use fetchall() without the while True statement but this takes even longer.
Question: How do I successfully speed up this process/what is the best way to load large select statements into python?
mysql python-3.x
mysql python-3.x
asked Nov 8 at 11:02
Mr. President
3949
asked Nov 8 at 11:02
Mr. President
3949
edited Nov 8 at 12:38
asked Nov 8 at 11:02
Mr. President
3949
asked Nov 8 at 11:02
Mr. President
3949
asked Nov 8 at 11:02
Mr. President
3949
3949
add a comment |
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53206405%2floading-large-mysql-select-statements-into-python%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
