Pyodbc & SQL Server-TypeError: not all arguments converted during string formatting

Question

I'm trying to read from one DB and then write to another on a different machine. I'm getting a TypeError: not all arguments converted during string formatting

Here is my code:

import pyodbc
#read from db
read_db = pyodbc.connect(driver='{SQL Server}', host='hostname', databse='DBa', user='user', password='pwd')
cur = read_db.cursor()
cur.execute("SELECT * FROM origin_table")

#write to db
write_db = pyodbc.connect(driver='{SQL Server}', host='hostname', databse='DBb', user='user', password='pwd')

#get rows from read  
rows = cur.fetchall()

#make list, init
read_list = []
i=0

#add row, write
for row in rows:
    read_list.append(row)
    params = ['%s' for item in read_list]
    sql = "INSERT INTO destination_table (col1, col2, col3, col4, col5, col6, col7) VALUES (%%s,%%s,%%s,%%s,%%s,%%s,%%s);" % ','.join(params)
    cur2 = write_db.cursor()
    cur2.execute(sql, params)

Here is what the output from the read looks like:

('Fname', 'Lname', u'THIS', 'THAT', Decimal('0'), datetime.datetime(2015, 6, 20, 3, 26, 47), u'THE OTHER')

I've tried many different solutions I've found and getting only the TypeError: is the closest I seem to get. Do I need to do some sort of explicit type conversion/specification?

When I triple quote the SQL and remove the escape %, like this:

"""INSERT INTO destination_table (col1, col2, col3, col4, col5, col6, col7) VALUES (%s,%s,%s,%s,%s,%s,%s);""" % ','.join(params)

The error I get is TypeError: not enough arguments for format string

Updated: Changed the ','.join(params) to tuple(params) (as suggested below) and I am still getting a TypeError: not enough arguments for format string

Answer 1

The whole ... % tuple(params) part wasn't needed. I just put the list of parameters in with cur2.excecute after getting some ...not enough parameters... errors.

This is the fixed block:

for row in rows:
    read_list.append(row)
    sql = "INSERT INTO destination_table (col1, col2, col3, col4, col5, col6, col7) VALUES (?,?,?,?,?,?,?);"
    cur2 = write_db.cursor()
    cur2.execute(sql, read_list[i])

And the whole thing for temporary logging use in the console:

import time
import pyodbc

read_list = []
i=0

read_db = pyodbc.connect(driver='{SQL Server}', host='read_host', databse='read_db', user='read_user', password='read_pw')
cur = read_db.cursor()
cur.execute("SELECT * FROM read_table")
rows = cur.fetchall()

write_db = pyodbc.connect(driver='{SQL Server}', host='write_host', databse='write_db', user='write_user', password='write_pw')

while True:
    start_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime())
    print(10*"/-/")
    print("Start " + str(start_time))
    for row in rows:
        read_list.append(row)
        sql = """INSERT INTO write_table (col1, col2, col3, col4, col5, col6, col7) VALUES (?,?,?,?,?,?,?);"""
        cur2 = write_db.cursor()
        cur2.execute(sql, read_list[i])
        cur2.commit()
        i+=1
    print("End, wait 30 minutes")
    time.sleep(1800)
    i=0

Answer 2

The problem you are having is that params should be a tuple.

"""INSERT INTO destination_table (col1, col2, col3, col4, col5, col6, col7) VALUES (%s,%s,%s,%s,%s,%s,%s);""" % tuple(params)

Using the join function will try to convert "params" into a single variable which is a string. So it will try to push it all into your first "%s".

To verify your input "params" have enough elements, try printing it out (or use debugger). To print:

for row in rows:
    read_list.append(row)
    params = ['%s' for item in read_list]
    print(params)
    query = """INSERT INTO destination_table (col1, col2, col3, col4, col5, col6, col7) VALUES (%s,%s,%s,%s,%s,%s,%s);""" % tuple(params)
    print(query)