MySQL MERGE table across separate databases using python

Question

I have a database db1 which contains tables tbl1, tbl2, and tbl3.

I also have an empty database db2.

How can I create a MERGE table mrg1 which merges the contents of tbl1, tbl2, and tbl3 from database db1, but is stored in database db2 using python/mysql connector?

This question is the same as wroberts question but using python.

This what I've tried:

    import mysql.connector

    db1 = 'testaa'
    cnx1 = mysql.connector.connect(user='nev', password='***',host='127.0.0.1',database=db1)
    cursor1 = cnx1.cursor()

    db2 = 'vic'
    cnx2 = mysql.connector.connect(user='nev', password='***',host='127.0.0.1',database=db2)
    cursor2 = cnx2.cursor()

   cnx = [cnx1,cnx2]
   print ("cnx1",cnx1)
   print ("cnx2",cnx2)

   tables = ["RESA","TESTA"]

   # count rows in each table
   for db in cnx:
       cursor = db.cursor()
       for tab in tables:
           query = ("SELECT count(*) FROM %s") % tab
           cursor.execute(query)
           for (count) in cursor:
               print("{} {} {}".format(db,query,count))
    print ("")


    #  merge the tables from vic into testaa
    for tab in tables:
        query = ('insert into {}.{} (select * from {}.{})'.format(db1,tab,db2,tab))
        cursor1.execute(query)

    # run count again to check results
    for db in cnx:
        cursor = db.cursor()
        for tab in tables:
            query = ("SELECT count(*) FROM %s") % tab
            cursor.execute(query)
            for (count) in cursor:
                print("{} {} {}".format(db,query,count))
     print ("")

The results indicate success, table counts double, but when I view from mysql no changes have occurred.

    ('cnx1', <mysql.connector.connection.MySQLConnection object at 0x100f9dd90>)
    ('cnx2', <mysql.connector.connection.MySQLConnection object at 0x100fad310>)
    <mysql.connector.connection.MySQLConnection object at 0x100f9dd90> SELECT count(*) FROM RESA (3,)
    <mysql.connector.connection.MySQLConnection object at 0x100f9dd90> SELECT count(*) FROM TESTA (19,)

    <mysql.connector.connection.MySQLConnection object at 0x100fad310> SELECT count(*) FROM RESA (3,)
    <mysql.connector.connection.MySQLConnection object at 0x100fad310> SELECT count(*) FROM TESTA (19,)

    <mysql.connector.connection.MySQLConnection object at 0x100f9dd90> SELECT count(*) FROM RESA (6,)
    <mysql.connector.connection.MySQLConnection object at 0x100f9dd90> SELECT count(*) FROM TESTA (38,)

    <mysql.connector.connection.MySQLConnection object at 0x100fad310> SELECT count(*) FROM RESA (3,)
    <mysql.connector.connection.MySQLConnection object at 0x100fad310> SELECT count(*) FROM TESTA (19,)

View from mysql after running above:

    mysql> use testaa;
    Database changed
    mysql> select count(*) from resa;
    +----------+
    | count(*) |
    +----------+
    |        3 |
    +----------+
    1 row in set (0.00 sec)

Mikpa · Accepted Answer · 2016-04-21 07:46:07Z

1

if you add this statement at the end of your program:

cnx1.commit()

You will commit your changes to the database.

answered Apr 21, 2016 at 7:46

Mikpa

1,93215 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL MERGE table across separate databases using python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related