Parse query result to insert it into another database

Question

After running a query from a MySQL database I het the following result:

(datetime.date(2000, 11, 11), u'superuser', datetime.date(2000, 11,11),u'yes')
(datetime.date(2001, 11, 11), u'superuser', datetime.date(2001, 11,11),u'yes')
(datetime.date(2002, 11, 11), u'superuser', datetime.date(2002, 11,11),u'yes')
(datetime.date(2003, 11, 11), u'superuser', datetime.date(2003, 11,11),u'yes')

Those results comes from a databse which has four columns:

last_login | is_superuser | data_joined | is_active

My problem is that I need to insert those data in another database that has the same structure, but I need to parse the data in a way that MySQL will be able to accept the query:

 INSERT INTO  auth_user.auth_user (last_login, is_superuser, data_joined, 
 is_active) VALUES 
    ('2008-11-11', 'superuser', '2008-11-11', 'yes'),
    ('2008-11-11', 'superuser', '2008-11-11', 'yes'),
    ('2008-11-11', 'superuser', '2008-11-11', 'yes'),
    ('2008-11-11', 'superuser', '2008-11-11', 'yes')[...];

I am struggling to obtain this format from the previous one, does anyone has any suggestion how to approach this problem?

Related: https://stackoverflow.com/questions/42039794/inserting-a-list-holding-multiple-values-in-mysql-using-pymysql — Ilja Everilä, Jun 05 '18 at 12:26

Ilja Everilä · Accepted Answer · 2018-06-05T12:50:31.553

2

Use executemany() and a parameterized query. Given that the results from the previous query are stored as a list of tuples named results:

query = """INSERT INTO  auth_user.auth_user
           (last_login, is_superuser, data_joined, is_active)
           VALUES (%s, %s, %s, %s)"""

# If your driver's cursors don't work as context managers, wrap with
# contextlib.closing
with conn.cursor() as cursor:
    cursor.executemany(query, results)

conn.commit()

Your driver knows how to pass the usual Python objects, such as date, datetime, strings, and numbers when using parameterized queries.

Depending on the driver in use executemany() may or may not optimize passing multiple value tuples. For example MySQL Connector and PyMySQL optimize INSERT statements.

edited Jun 05 '18 at 12:50

answered Jun 05 '18 at 12:13

Ilja Everilä

50,538
7
126
127

This would work, but how can I gt ride of the datetime.date, u' and so on? – robsanna Jun 05 '18 at 12:20
It seems I get an attribute error when I try to execute this, the error message I get is __exit__ – robsanna Jun 05 '18 at 12:34
yes, i do: `try: logging.info(' Trying to execute the query...') cursor = db_connection.cursor() cursor.executemany(query_string, query_data) db_connection.commit() logging.info(' Query executed!') db_connection.close() return logging.info('Connection closed!') ` – robsanna Jun 05 '18 at 12:51
I was wrong with the logged query, basically everything I get no error, and everything looks fine, but the data are not getting copied. My driver is MySQL and for the connection I use `mysql.connector.Connect` – robsanna Jun 05 '18 at 12:59
Ok, I think I am doing something wrong (as always) it seems like that I am duplicating the same data on the same DB instead of copying it to another one... – robsanna Jun 05 '18 at 13:02

score 1 · Answer 2 · answered Jun 05 '18 at 12:13

Your results are tuples... just produce a suitable SQL query, and execute() it:

query = 'INSERT INTO auth_user.auth_user'                       \
          ' (last_login, is_superuser, data_joined, is_active)' \
          ' VALUES (%s, %s, %s, %s);'

for result in results:
    cursor.execute(query, result)

Parse query result to insert it into another database

2 Answers2