0

Code:

import pandas as pd
df = pd.DataFrame(list(inverted_index.items()),columns = ['words','docids']) 
from pandas.io import sql
from sqlalchemy import create_engine
engine = create_engine("mysql+pymysql://{user}:{pw}@localhost/{db}"
                       .format(user="root",
                               pw="shreshre",
                               db="nltk"))
df.to_sql(con=engine, name='documents', if_exists='replace')

Output: image of my error page and code output

Here I want convert my inverted index, which is in dictionary type, into a dataframe and write it in MySQL. But I am receiving an error:

OperationalError: (pymysql.err.OperationalError) (1241, 'Operand should contain 1 column(s)')
[SQL: INSERT INTO documents (`index`, words, docids) VALUES (%(index)s, %(words)s, %(docids)s)]
[parameters: ({'index': 0, 'words': 'bank', 'docids': {0, 1, 2, 3, 4, 17, 18, 19, 20, 21, 22, 23, 25, 26, 27, 28, 37, 38, 39, 40, 41, 43, 44, 45, 46, 48, 52, 53, 54, 55, 56, 59, 60, 62, 64, 66, 67, 68, 69 ... (1314 characters truncated) ...  719, 720, 721, 722, 724, 726, 728, 733, 734, 735, 736, 737, 739, 740, 743, 746, 748, 752, 753, 755, 756, 757, 758, 759, 762, 765, 766, 767, 768, 772}}, {'index': 1, 'words': 'defin', 'docids': {0, 2, 354, 612, 773, 76, 84}}, {'index': 2, 'words': 'establish', 'docids': {0, 161, 391, 328, 330, 718, 719, 720, 722, 245, 217, 411, 156}}, {'index': 3, 'words': 'custodi', 'docids': {0, 405}}, {'index': 4, 'words': ',', 'docids': {0, 1, 2, 3, 8, 14, 17, 20, 22, 24, 25, 26, 27, 30, 31, 41, 43, 45, 48, 49, 51, 52, 54, 55, 59, 62, 63, 65, 67, 69, 70, 72, 73, 74, 76, 78, 79, 80, 81 ... (1428 characters truncated) ...  705, 706, 708, 710, 711, 712, 716, 718, 719, 721, 722, 724, 729, 730, 732, 735, 736, 741, 743, 745, 749, 756, 757, 758, 762, 766, 768, 769, 771, 773}}, {'index': 5, 'words': 'loan', 'docids': {0, 512, 517, 519, 538, 29, 33, 34, 557, 558, 47, 559, 564, 574, 578, 580, 70, 73, 76, 79, 616, 621, 113, 114, 115, 116, 117, 123, 124, 127, 128, 129, ... (75 characters truncated) ...  711, 200, 219, 227, 228, 234, 235, 241, 758, 771, 309, 310, 340, 343, 346, 349, 354, 365, 368, 380, 383, 384, 385, 386, 440, 447, 448, 451, 453, 474}}, {'index': 6, 'words': 'exchang', 'docids': {0, 416, 290, 354, 357, 425, 10, 302, 430, 405, 376, 415}}, {'index': 7, 'words': 'issu', 'docids': {0, 386, 419, 676, 390, 397, 302, 272, 274, 306, 722, 700, 350}}  ... displaying 10 of 1969 total bound parameter sets ...  {'index': 1967, 'words': '86', 'docids': {774}}, {'index': 1968, 'words': 'separ', 'docids': {774}})]

I am not able to understand the existing solution posted on Stackoverflow regarding a similar error. Someone please help me out.

john-hen
  • 4,410
  • 2
  • 23
  • 40
Shreya Dagupathi
  • 1
  • 1
  • Hi Shreya, it is better if you put your error messages and code directly into your question instead of a picture. – Jason D May 12 '21 at 12:29
  • Yeah i added them @Jason D Any solution for my error now would be of much help. – Shreya Dagupathi May 12 '21 at 12:47
  • Thanks Shreya, can you please post the hyperlink you are referring to regarding a similar error? – Jason D May 12 '21 at 12:51
  • https://stackoverflow.com/questions/22134987/mysqldb-throws-operand-should-contain-1-columns-on-insert-ignore-statement @Jason D – Shreya Dagupathi May 12 '21 at 12:52
  • TypeError: Object of type Series is not JSON serializable. I actually tried that too know i am recieving this error of json is not serializable.I recieved the same error when i tried some other methods to write the same thing into mysql. – Shreya Dagupathi May 12 '21 at 12:55
  • i tried serializing it```import json from json import JSONEncoder inverted_index1=dict(inverted_index) print(type(inverted_index1)) new = json.dumps(inverted_index1,cls=setEncoder) #print(new) json_object = json.loads(new) pairs = json_object.items() print(type(pairs)) for key, value in pairs: s1=key s2=value values = [[item] for item in s2] sql = u"INSERT INTO documents(words,docids) VALUES (%s,%s)" val = (s1,values) mycursor.execute(sql, val) mydb.commit() ``` – Shreya Dagupathi May 12 '21 at 12:58
  • but it shows me list type is also not acceptable in mysql again. I tried converting dictionary to array , list , string , json format , and even dataframe in orde to write in mysql.But it all turned out in vain.Only column names are created values arent inserted.@Jason D – Shreya Dagupathi May 12 '21 at 13:01

0 Answers0