Why pymysql not insert record into table?

Question

I am pretty new in python developing. I have a long python script what "clone" a database and add additional stored functions and procedures. Clone means copy only the schema of DB.These steps work fine.

My question is about pymysql insert exection: I have to copy some table contents into the new DB. I don't get any sql error. If I debug or print the created INSERT INTO command is correct (I've tested it in an sql editor/handler). The insert execution is correct becuse the result contain the exact row number...but all rows are missing from destination table in dest.DB... (Ofcourse DB_* variables have been definied!)

import pymysql

liveDbConn = pymysql.connect(DB_HOST, DB_USER, DB_PWD, LIVE_DB_NAME)
testDbConn = pymysql.connect(DB_HOST, DB_USER, DB_PWD, TEST_DB_NAME)

tablesForCopy = ['role', 'permission']
for table in tablesForCopy:
    with liveDbConn.cursor() as liveCursor:
        # Get name of columns
        liveCursor.execute("DESCRIBE `%s`;" % (table))
        columns = '';
        for column in liveCursor.fetchall():
            columns += '`' + column[0] + '`,'
        columns = columns.strip(',')
        
        # Get and convert values
        values = ''
        liveCursor.execute("SELECT * FROM `%s`;" % (table))
        for result in liveCursor.fetchall():
            data = []
            for item in result:
                if type(item)==type(None):
                    data.append('NULL')
                elif type(item)==type('str'):
                    data.append("'"+item+"'")
                elif type(item)==type(datetime.datetime.now()):
                    data.append("'"+str(item)+"'")
                else:  # for numeric values
                    data.append(str(item))
            v = '(' + ', '.join(data) + ')'
            values += v + ', '
    values = values.strip(', ')
    
    print("### table: %s" % (table))
    testDbCursor = testDbConn.cursor()
    testDbCursor.execute("INSERT INTO `" + TEST_DB_NAME + "`.`" + table + "` (" + columns + ") VALUES " + values + ";")
    print("Result: {}".format(testDbCursor._result.message))
    

liveDbConn.close()
testDbConn.close()

Result is:

### table: role 
Result: b"'Records: 16  Duplicates: 0  Warnings: 0"
### table: permission 
Result: b'(Records: 222  Duplicates: 0  Warnings: 0'

What am I doing wrong? Thanks!

Answer 1

You have 2 main issues here:

1. You don't use conn.commit() (which would be either be liveDbConn.commit() or testDbConn.commit() here). Changes to the database will not be reflected without committing those changes. Note that all changes need committing but SELECT, for example, does not.
2. Your query is open to SQL Injection. This is a serious problem.

Table names cannot be parameterized, so there's not much we can do about that, but you'll want to parameterize your values. I've made multiple corrections to the code in relation to type checking as well as parameterization.

for table in tablesForCopy:
    with liveDbConn.cursor() as liveCursor:        
        liveCursor.execute("SELECT * FROM `%s`;" % (table))
        name_of_columns = [item[0] for item in liveCursor.description]
        insert_list = []
        for result in liveCursor.fetchall():
            data = []
            for item in result:
                if item is None: # test identity against the None singleton
                    data.append('NULL')
                elif isinstance(item, str): # Use isinstance to check type
                    data.append(item)
                elif isinstance(item, datetime.datetime):
                    data.append(item.strftime('%Y-%m-%d %H:%M:%S'))
                else:  # for numeric values
                    data.append(str(item))
            insert_list.append(data)

    testDbCursor = testDbConn.cursor()
    placeholders = ', '.join(['`%s`' for item in insert_list[0]])
    testDbCursor.executemany("INSERT INTO `{}.{}` ({}) VALUES ({})".format(
                                                                TEST_DB_NAME, 
                                                                table, 
                                                                name_of_columns, 
                                                                placeholders),
                             insert_list)
    testDbConn.commit()

---

From this github thread, I notice that executemany does not work as expected in psycopg2; it instead sends each entry as a single query. You'll need to use execute_batch:

from psycopg2.extras import execute_batch

execute_batch(testDbCursor,
              "INSERT INTO `{}.{}` ({}) VALUES ({})".format(TEST_DB_NAME, 
                                                            table, 
                                                            name_of_columns, 
                                                            placeholders),
              insert_list)
testDbConn.commit()