Multiple insert to mysql is not working in python

Question

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.

Run for column in (documentcount,publicationpaper,title,byline,section,length,url,graphic,language,subject,company,organization,ticker,industry,person,city,state,country,loaddate,copyrightinfo,MainText): print(set(map(type, column))) so we can verify that all the values are of sensible types. — Alex Hall
– Alex Hall, Commented Dec 5, 2016 at 21:15

Haifeng Zhang · Accepted Answer · 2016-12-05 21:16:37Z

1

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

answered Dec 5, 2016 at 21:16

Haifeng Zhang

32.1k21 gold badges95 silver badges133 bronze badges

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

Collectives™ on Stack Overflow

Multiple insert to mysql is not working in python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related