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
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
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
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
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
