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
3869
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
3869
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
3869
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
3869
asked Nov 8 at 11:02
Mr. President
3869
edited Nov 8 at 12:38
asked Nov 8 at 11:02
Mr. President
3869
asked Nov 8 at 11:02
Mr. President
3869
asked Nov 8 at 11:02
Mr. President
3869
3869
add a comment |
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
