CODEWITHSUNDEEP
pythonmysql
AK91
AK91

Reputation: 731

Error on appending dictionary to mysql table

I'm querying a table in a mysql database and want to append results to another table in the same database, but I'm getting an error that I can't seem to shake off.

Thus far I've

Code:

import pymysql
import pandas as pd

dbServerName    = "..."
dbUser          = "..."
dbPassword      = "..."
dbName          = "..."

connObject = pymysql.connect(dbServerName, dbUser, dbPassword, dbName)
sqlQuery = """select id, uniqueUserId, time from searchResult where time>'2018-02-01' and time<'2018-03-2' limit 5"""
df = pd.read_sql(sqlQuery, connObject)
df['time'] = df['time'].astype(str)
# df.to_sql('test', con=conn, if_exists='append')
dfDict = df.to_dict(orient='list')

cursor = connObject.cursor()
table = 'test'
placeholders = ', '.join(['%s'] * len(dfDict))
columns = ', '.join(dfDict.keys())
sql = "INSERT INTO %s (%s) VALUES (%s)" % (table, columns, placeholders)
cursor.execute(sql, list(dfDict.values()))

I expect the code to append the dictionary to the table named 'test'. But instead I receive the following error message:

---------------------------------------------------------------------------
InternalError                             Traceback (most recent call last)
<ipython-input-39-86395db9a7b9> in <module>
      4 columns = ', '.join(dfDict.keys())
      5 sql = "INSERT INTO %s (%s) VALUES (%s)" % (table, columns, placeholders)
----> 6 cursor.execute(sql, list(dfDict.values()))

InternalError: (1241, 'Operand should contain 1 column(s)')

dfDict contents looks something like this:

{'id': [39457,
  39458,
  39459,
  39460,
  39461,
  ...],
  'time': ['2018-03-01 00:00:05',
  '2018-03-01 00:00:09',
  '2018-03-01 00:00:10',
  '2018-03-01 00:00:15',
  '2018-03-01 00:00:17',
  ...],
  'uniqueUserId': ['123abc-x123-y123-z123-1234567xyz',
  '123abc-x1415-y3264-z1343-13452xyz',
  '3413dgwe-x1143-124124-4214af-125wfag',
  'lk23h5l-l23k5h-2l3jk4-l15h-1po5j',
  'a987sfa-23kh4-n21m4nb1-1j5lkj2b3-kho7v62o',
  ...]}

Other info:

Any ideas as to what I'm doing wrong?

Upvotes: 1

Views: 184

Answers (2)

Booboo
Booboo

Reputation: 44128

Your last line should be:

cursor.executemany(sql, args)

where args is computed thus:

v = list(dfDict.values())
args = [[v[j][i] for j in range(len(v))] for i in range(len(v[0]))]

Yielding for args:

[[39457, '2018-03-01 00:00:05', '123abc-x123-y123-z123-1234567xyz'], [39458, '2018-03-01 00:00:09', '123abc-x1415-y3264-z1343-13452xyz'], ...

Upvotes: 2

kb-0
kb-0

Reputation: 98

If you want to put multiple new tuples into a relation in one go, I am quite sure you need to use MySQLCursor.executemany() instead of MySQLCursor.execute().

https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html

Alternatively you can iterate through the three lists in that dictionary and do a single MySQLCursor.execute() for each set of data.

Upvotes: 0

Related Questions