TypeError: not all arguments converted during string formatting Error in tuple python

Question

Im trying to save bunch of tuples in DB

 cursor = cnx.cursor()
         query = """INSERT INTO `TableA`
                     (`clientid`,
                    `createddatetime`,
                    `siteid`,...)
                 VALUES(?,?,?,...)"""

    cursor.executemany(query, listTosave)

My listTosave contains list of tuples like;

[('AS0001', '1170', 1, '1', 'Unknown', 442, 1, datetime.datetime(2018, 5, 28, 23, 0), datetime.datetime(2018, 3, 15, 11, 15), datetime.datetime(2018, 3, 15, 10, 56), datetime.datetime(2018, 5, 28, 23, 18, 26), datetime.datetime(2018, 5, 28, 23, 59, 22), Decimal('15177.3184'), Decimal('15185.7562'), Decimal('8.4378'), Decimal('1313.0547'), Decimal('1313.6179'), Decimal('0.5632'), Decimal('0.0000'), Decimal('0.0000'), Decimal('0.0000'), Decimal('0.0000'), Decimal('0.0000'), Decimal('0.0000'), Decimal('24.6518'), Decimal('24.6518'), 15101.7062, 0.0, 0.0, 0.0, 24.6563), (........... )]

When I try to save I get;

 File "/tmp/pymodules/pymysql/cursors.py", line 194, in executemany
  File "/tmp/pymodules/pymysql/cursors.py", line 194, in <genexpr>
  File "/tmp/pymodules/pymysql/cursors.py", line 163, in execute
  File "/tmp/pymodules/pymysql/cursors.py", line 142, in mogrify
TypeError: not all arguments converted during string formatting

Why do I get this error?

Edit : I converted datetime objects/decimal objects also to string. My new list is like;

[('AS0001', '1170', '1', '1', 'Unknown', '442', '1', '2018-05-28 23:00:00', '2018-03-15 11:15:00', '2018-03-15 10:56:00', '2018-05-28 23:18:26', '2018-05-28 23:59:22', '15177.3184', '15185.7562', '8.4378', '1313.0547', '1313.6179', '0.5632', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '24.6518', '24.6518', '15101.7062', '0.0', '0.0', '0.0', '24.6563'), (.....)]

But still I get same error

Shivam Singh · Accepted Answer · 2018-05-31 03:46:22Z

1

Here is a minimal example that I got working.

query = "INSERT INTO `pet`(`name`,`owner`) values(%s,%s)"
listTosave = [('some','shsjhs'),('sosos','shshsh')]
cursor.executemany(query, listTosave)

Make sure that you have a list of tuples, and that you use %s in query string

answered May 31, 2018 at 3:46

Shivam Singh

1,6241 gold badge11 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScottMcC · Accepted Answer · 2018-05-31 01:09:31Z

1

At a guess from the error that you're returning it could be to do with the datetime objects that are stored in your list, not converting to a string representation correctly. Wrapping these in str() might be the cause of your issue.

Note the following example:

>>> import datetime
>>> datetime.datetime.now()
datetime.datetime(2018, 5, 31, 8, 42, 48, 172575)
>>> str(datetime.datetime.now())
'2018-05-31 08:42:53.192586'

Another option might be able to get rid your error by using json.dumps the list element to a json string format. But it depends on how you want to store your data in your database. See the following:

>>> import json
>>> element = (
... 'AS0001', '1170', '1', '1', 'Unknown', '442', '1', '2018-05-28 23:00:00', '2018-03-15 11:15:00', '2018-03-15 10:56:00',
... '2018-05-28 23:18:26', '2018-05-28 23:59:22', '15177.3184', '15185.7562', '8.4378', '1313.0547', '1313.6179', '0.5632',
... '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '24.6518', '24.6518', '15101.7062', '0.0', '0.0', '0.0',
... '24.6563')
>>> json.dumps(element)
'["AS0001", "1170", "1", "1", "Unknown", "442", "1", "2018-05-28 23:00:00", "2018-03-15 11:15:00", "2018-03-15 10:56:00", "2018-05-28 23:18:26", "2018-05-28 23:59:22", "15177.3184", "15185.7562", "8.4378", "1313.0547", "1313.6179", "0.5632", "0.0000", "0.0000", "0.0000", "0.0000", "0.0000", "0.0000", "24.6518", "24.6518", "15101.7062", "0.0", "0.0", "0.0", "24.6563"]'

When retrieving this data you could then use json.loads to load it back into a python object format

edited May 31, 2018 at 1:09

answered May 31, 2018 at 0:43

ScottMcC

4,5201 gold badge32 silver badges39 bronze badges

5 Comments

Ratha Over a year ago

I did ..Now my new list is like [('AS0001', '1170', '1', '1', 'Unknown', '442', '1', '2018-05-28 23:00:00', '2018-03-15 11:15:00', '2018-03-15 10:56:00', '2018-05-28 23:18:26', '2018-05-28 23:59:22', '15177.3184', '15185.7562', '8.4378', '1313.0547', '1313.6179', '0.5632', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '24.6518', '24.6518', '15101.7062', '0.0', '0.0', '0.0', '24.6563'), (.....)] But same error

ScottMcC Over a year ago

Hmmm.. my other suggestion would to perhaps dump the list element into a json object? I'll update my answer to suit

OneCricketeer Over a year ago

Why convert a tuple back and forth from JSON? you could call list() over a tuple to get a list or map(str, element) to get all strings

Ratha Over a year ago

After using json.dump My new list is looking like ['["AS0001", "1170", "1", "1", "Unknown", "442", "1", "2018-05-28 23:00:00", "2018-03-15 11:15:00", "2018-03-15 10:56:00", "2018-05-28 23:18:26", "2018-05-28 23:59:22", "15177.3184", "15185.7562", "8.4378", "1313.0547", "1313.6179", "0.5632", "0.0000", "0.0000", "0.0000", "0.0000", "0.0000", "0.0000", "24.6518", "24.6518", "15101.7062", "0.0", "0.0", "0.0", "24.6563"]','[.......]'] I'm passing this list to save to DB. But error is still same :(. Some other fact causes issue? Is this correct // cursor.executemany(query, listTosave)//

Ratha Over a year ago

After changing VALUES(?,?,?,...) to VALUES(%s, %s,%s,..) I get //TypeError: not enough arguments for format string// but my tuple contains correct number of arguments.

Collectives™ on Stack Overflow

TypeError: not all arguments converted during string formatting Error in tuple python

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related