List of tuples with strings not all arguments converted during string formatting

Question

I am trying to insert hundreds of rows into a MySQL db at once. There are two types of records, unanswered calls and answered calls. I am putting all records into a list of tuples, and each record is it's own tuple, so that I can use the executemany function. I am getting a TypeError: not all arguments converted during string formatting, and I don't understand why.

answered = []
        unanswered = []
        insertQuery =             """ INSERT INTO cdr (recno, relcause, starttime, answertime, endtime, releasecausetext, releasecausecode, 1streleasedialog,
                                      origtrunk, callingnumber, orighost, callednumber, desthost, origcallid, origremotepayloadip, origremotepayloadport,
                                      origlocalpayloadip, origlocalpayloadport, termtrunk, termsourcenumber, termsourcehost, termdestnumber, termdesthostname,
                                      termcallid, termremotepayloadip, termremotepayloadport, termlocalpayloadip, termlocalpayloadport, duration, postdialdelay,
                                      ringtime, durationms, routetableused, origtidalias, termtidalias, termpddms, reasoncause, mappedcausecode, mappedreasoncause,
                                      reasoncausetext, origmos, termmos) VALUES ('%s'); """


        for y in cdrList:
            #Check to make sure record does not exist
            sqlQuery = "select * from cdr where recno = %d and origcallid = %s;" % (int(y[0]), y[13])
            if cursor.execute(sqlQuery):
                print("Record exists")
            else:
                if y[7]=='NA':
                    unanswered.append((y[0], y[5],extractSqlDate(y[6]), 'null',  extractSqlDate(y[8]), y[10], y[11], y[12], y[13], y[15], y[16], y[17], y[18], y[19], y[20], y[21], y[22], y[23], y[32], y[34], y[35], y[36], y[37], y[38], y[39], y[40], y[41], y[42], y[53], y[54], y[55], y[56], y[60], y[66], y[67], y[71], y[78], y[79], y[80], y[81], y[85], y[88]))
                else:
                    answered.append((y[0], y[5],extractSqlDate(y[6]), extractSqlDate(y[7]), extractSqlDate(y[8]), y[10], y[11], y[12], y[13], y[15], y[16], y[17], y[18], y[19], y[20], y[21], y[22], y[23], y[32], y[34], y[35], y[36], y[37], y[38], y[39], y[40], y[41], y[42], y[53], y[54], y[55], y[56], y[60], y[66], y[67], y[71], y[78], y[79], y[80], y[81], y[85], y[88]))
        try:
            print(answered)
            cursor.executemany(insertQuery, answered)
            cursor.executemany(insertQuery, unanswered)
            db.commit()
            print("Record inserted successfully")
        except MySQLdb.Error as e:
            print(e)

I have confirmed that each element in each tuple in the list is a string:

Successfully connected to database
/PATH/20190610/20190610-0015-1750147245-1750147250.cdr
[('1750147245', '0001', '2019-06-10 00:10:50', '2019-06-10 00:10:59', '2019-06-10 00:11:13', 'Normal BYE', ' 200', 'O', '001102', '+tn', 'ip', '+tn', 'ip', '273418599_83875291@ip', 'ip', '20530', 'ip', '11944', '000020', '+tn', 'ip', 'tn', 'ip', '4121333-0-2851866068@ip', 'ip', '16840', 'ip', '11946', '13', '1', '8', '13450', '50', 'C - Peerless C6933_04 Origin', 'P - Thirdlane 6', '1150', '', '200', '', '', '0', '0')]

Don't use string interpolation for SQL queries. Also you seem to have only a single target `%s`, but are expecting quite a few values. — jonrsharpe, Jul 01 '19 at 15:19
@jonrsharpe according to this post [link](https://stackoverflow.com/questions/1455602/printing-tuple-with-string-formatting-in-python), you only use a single %s when referencing a tuple of strings. If not string interpolation, what is the correct way to insert this many values in SQL? — Josh Eblin, Jul 01 '19 at 15:23
iam pretty sure you can handle this with more simple `INSERT INTO ... SELECT ...` [syntax](https://dev.mysql.com/doc/refman/8.0/en/insert-select.html) — Raymond Nijland, Jul 01 '19 at 15:31
@RaymondNijland I'm not selecting anything from a database. these records are coming from a csv file — Josh Eblin, Jul 01 '19 at 15:39
*" I'm not selecting anything from a database"* the MySQL tag and seeing a SELECT/execute in the code is very contradictory to that comment.. Maybe you should explain your user case beter and show some example records from the CSV and what the results should be? — Raymond Nijland, Jul 01 '19 at 15:56
@RaymondNijland the select statement is to make sure I'm not inserting a duplicate record. There is a sample from the csv file at the end of my post. — Josh Eblin, Jul 01 '19 at 16:16

score 0 · Answer 1 · answered Jul 01 '19 at 20:39

0

I found the problem. The tuple was returning strings, so the insert query was trying to insert values like this: ''value''. I removed the ' around the %s, and, based on @jonrsharpe's comment, added %s for each other value, and it worked.

answered Jul 01 '19 at 20:39

Josh Eblin

95
1
9

List of tuples with strings not all arguments converted during string formatting

1 Answers1