Convert query result types when using fetchall()

Question

I am writing a Python database data backup script targeting an instance of Microsoft SQL Server 2012. My goal is to be able to easily backup a database's data to an existing empty schema. I am using pymssql as my database interface.

Here is the section of code I am having problems with:

def run_migrate(s_conn, d_conn): 
    source_cursor = s_conn.cursor()
    dest_cursor = d_conn.cursor()
    source_cursor.execute("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES order by TABLE_NAME asc")
    res_tables = source_cursor.fetchall()
    tables = [i[0] for i in res_tables]
    for i in range(len(tables)): 
        query_columns = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + str(tables[i]) + "'"
        l.log.debug(query_columns)
        source_cursor.execute(query_columns)
        res_columns = source_cursor.fetchall()
        columns = [i[0] for i in res_columns]
        query_data = 'SELECT * FROM ' + tables[i]
        l.log.debug(query_data)
        source_cursor.execute(query_data)
        res_results = source_cursor.fetchall()
        l.log.info('Extracted ' + str(len(results))  + ' results from ' + tables[i])

My problem is that when i use res_results = fetchall() I get back a list that looks like the following:

<class 'list'>: [(Decimal('102'), datetime.datetime(2016, 3, 29, 13, 53, 20, 593000), '1459281200561077E9152BF85FCBC33E6689E9DCB61705985137701459280466827', 'cable summary ', 3600000, 0, None, None, None, None, None, None, 0, 0, None, None, Decimal('333'), Decimal('100'), Decimal('107'), None, None)]

I am looking to form a parameterized INSERT statement out of this data to run against d_conn.execute() but how can I convert the data types on the fly without knowing beforehand each columns data type and how to handle it?

For example I would need the convert the list:

<class 'list'>: [
(Decimal('102'), 
datetime.datetime(2016, 3, 29, 13, 53, 20, 593000),
'1459281200561077E9152BF85FCBC33E6689E9DCB61705985137701459280466827', 
'cable summary ', 
3600000, 
0, 
None, 
None, 
None, 
None, 
None, 
None, 
0, 
0, 
None, 
None, 
Decimal('333'), 
Decimal('100'), 
Decimal('107'), 
None, 
None)]

into the SQL INSERT statement:

INSERT INTO [ALERT] VALUES
   (102
   ,'2016-03-29 13:53:20.593'
   ,1
   ,'cable summary'
   ,3600000
   ,0
   ,NULL
   ,NULL
   ,NULL
   ,NULL
   ,NULL
   ,NULL
   ,0
   ,0
   ,NULL
   ,NULL
   ,333
   ,100
   ,107
   ,NULL
   ,NULL)

I want to be able to do this with any datatypes I pass it as I have a few hundred tables to do this with and don't want to write a function for each one. Any help is appreciated.

Answer 1

For example I would need the convert the list ... into the [literal] SQL INSERT statement ...

Actually, no you wouldn't, at least not if you really did use a parameterized query for the INSERT. Python's DB-API specifies generic parameter placeholders and relies on the database access layer (e.g., pymssql) to figure out how to handle parameters based on the type of parameter values supplied.

So, you can simply take the list of rows (which contain values of the appropriate type) from the source table and use those rows as parameter values for an executemany on the target table, like so:

# set up test
create_stmt = """\
CREATE TABLE {} (
    intCol INT PRIMARY KEY,
    nvarcharCol NVARCHAR(50),
    datetimeCol DATETIME,
    decimalCol DECIMAL(18,4),
    nullableCol INT
    )
"""
crsr.execute(create_stmt.format("#MainTable"))
load_test_data = """\
INSERT INTO #MainTable (intCol, nvarcharCol, datetimeCol, decimalCol) VALUES
    (1, N'test 1', '2016-01-01', 1.11),
    (2, N'test 2', '2016-02-02', 2.22),
    (3, N'test 3', '2016-03-03', 3.33)
"""
crsr.execute(load_test_data)
crsr.execute(create_stmt.format("#BackupTable"))

# perform the copy ...
read_stmt = """\
SELECT * FROM #MainTable
"""
crsr.execute(read_stmt)
source_rows = crsr.fetchall()
# ... using a true parameterized query ...
write_stmt = """\
INSERT INTO #BackupTable
        (intCol, nvarcharCol, datetimeCol, decimalCol, nullableCol)
    VALUES
        (%s, %s, %s, %s, %s)
"""
# ... inserting all rows at once using list returned by fetchall() from source table
crsr.executemany(write_stmt, source_rows)

#check results
crsr.execute("SELECT * FROM #BackupTable")
print("Rows from #BackupTable:")
for row in crsr.fetchall():
    print(row)

... producing:

Rows from #BackupTable:
(1, 'test 1', datetime.datetime(2016, 1, 1, 0, 0), Decimal('1.1100'), None)
(2, 'test 2', datetime.datetime(2016, 2, 2, 0, 0), Decimal('2.2200'), None)
(3, 'test 3', datetime.datetime(2016, 3, 3, 0, 0), Decimal('3.3300'), None)