CODEWITHSUNDEEP
pythonmysql
Mho
Mho

Reputation: 89

Multiple insert to mysql is not working in python

I have a basic question where multiple insert to mysql table is not working via python code.

    for pair in zip(documentcount,publicationpaper,title,byline,section,length,url,graphic,language,subject,company,organization,ticker,industry,person,city,state,country,loaddate,copyrightinfo,MainText):
            mainlist.append(pair)

cur.executemany("""INSERT INTO financedatasetsample (DOCCOUNT,PUBLICATION,TITLE,BYLINE,SECTION,LENGTH,URL,GRAPHIC,LANGUAG,SUBJEC,COMPANY,ORGANIZATION,TICKER,INDUSTRY,PERSON,CITY,STATE,COUNTRY,LOADDATE,COPYRIGHT)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", (mainlist))

where all the elements inside zip are separate list and i am trying to append to the mainlist which is successfully done. but the problem arises when i try to execute the below executemany query where it is not inserting and throws me the below error "AttributeError: 'MySQLConverter' object has no attribute '_list_to_mysql'"

I tried another method where it is inserting only first row and not subsequent rows.

for p in mainlist:
            format_str = """INSERT INTO financedatasetsample (DOCCOUNT,PUBLICATION,TITLE,BYLINE,SECTION,LENGTH,URL,GRAPHIC,LANGUAG,SUBJEC,COMPANY,ORGANIZATION,TICKER,INDUSTRY,PERSON,CITY,STATE,COUNTRY,LOADDATE,COPYRIGHT,MAINTEXTBODY)
            VALUES ('{DocumentCount}', '{Publication_Type}', '{Title}', '{Byline}','{Section}', '{Length}', '{Url}', '{Graphic}','{Language}', '{Subject}', '{Company}', '{Organization}','{Tickersymbol}', '{Industry}', '{Person}', '{City}','{State}','{Country}', '{Load_Date}', '{Copyrightinfo}','{MainText}');"""
            sql_command1 = format_str.format(DocumentCount=p[0], Publication_Type=p[1], Title=p[2], Byline=p[3], Section=p[4],Length=p[5], Url=p[6], Graphic=p[7], Language=p[8],Subject=p[9], Company=p[10], Organization=p[11],Tickersymbol=p[12], Industry=p[13], Person=p[14], City=p[15],State=p[16], Country=p[17],Load_Date=p[18], Copyrightinfo=p[19], MainText=p[20])
            print(sql_command1)
            cur.execute(sql_command1)

Pls help me to resolve the issue.

Upvotes: 1

Views: 119

Answers (1)

Haifeng Zhang
Haifeng Zhang

Reputation: 31895

Correct way is:

c.executemany(
      """INSERT INTO breakfast (name, spam, eggs, sausage, price)
      VALUES (%s, %s, %s, %s, %s)""",
      [
      ("value1", "value2", "value3", "value4", "value5"),
      ("value11", "value22", "value33", "value44", "value55"),
      ] )

make sure your mainlist contains tuples which has the same number values as your statement columns

Upvotes: 1

Related Questions