使用python跨不同数据库的MySQL合并表

Question

我有一个数据库db1，它包含表tbl1、tbl2和tbl3。

我还有一个空的数据库db2。

如何创建合并表mrg1，它从数据库db1合并tbl1、tbl2和tbl3的内容，但使用python/mysql连接器存储在数据库db2中？

这个问题与wroberts question相同，但使用python。

这是我尝试过的：

    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 ("")

结果显示成功，表数加倍，但当我从mysql查看时，没有发生任何更改。

    ('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,)

在上面运行之后，从mysql查看：

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

Answer 1

如果在程序末尾添加此语句：

cnx1.commit()

您将将更改提交到数据库。