Question 20732 in Mysql Question posted in
The official MySQL documentation can be found here.

How to insert multiple values into MySQL database using Python Script

I have the following sample values;

lst = [{'title': 'Guld för Odermatt i schweizisk dubbel', 'summary': '', 'link':      '``https://www.dn.se/sport/guld-for-odermatt-i-schweizisk-dubbel/``', 'topic': ['empty', 'empty', 'empty', 'empty', 'empty', 'empty', 'SamhalleKonflikter', 'empty', 'empty', 'empty']}
, {'title': 'Bengt Hall blir ny tillförordnad vd på Malmö Opera', 'summary': '', 'link': '``https://www.dn.se/kultur/bengt-hall-blir-ny-tillforordnad-vd-pa-malmo-opera/``', 'topic': ['empty', 'empty', 'empty', 'empty', 'empty', 'empty', 'SamhalleKonflikter', 'empty', 'empty', 'empty']}
, {'title': 'Fyra gripna för grova narkotikabrott', 'summary': '', 'link': '``https://www.dn.se/sverige/fyra-gripna-for-grova-narkotikabrott/``', 'topic': ['empty', 'empty', 'empty', 'empty', 'empty', 'empty', 'SamhalleKonflikter', 'empty', 'empty', 'empty']}]

and I tired using the following script to insert them into my database;

# Connect to MySQL server

`cnxn = mysql.connector.connect(`
`host="localhost",`
`user="root",`
`password="password",`
`database="NewsExtractDb"`
`)`

# Create a cursor object
cursor = cnxn.cursor()

sql = "INSERT INTO database (title, summary, link, topic) VALUES (%s, %s, %s, %s)"
params = [(item['title'], item['summary'], item['link'], ', '.join(item['topic'])) for item in lst]
cursor.executemany(sql, params)cnxn.commit()

But I am keep getting this error;

File "C:Python311Libsite-packagesmysqlconnectorconnection_cext.py", line 616, in cmd_query
raise get_mysql_exception(
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘database (title, summary, link, topic) VALUES (‘Guld för Odermatt i schweizisk ‘ at line 1

I have tired re-write the code with a for loop statement instead of ‘executemany’;

sql = "INSERT INTO database (title, summary, link, topic) VALUES (%s, %s, %s, %s)"

for item in lst:values = (item['title'], item['summary'], item['link'], ', '.join(item['topic']))cursor.execute(sql, values)

But I still end up getting the same identical error which I cannot fix. Any ideas?

Tags:

2

Answers


  1. 0

    After "INSERT INTO" statement there should be valid table name. But in your query table name is "database" which is invalid identifier for table name. To insert data into a table, firstly that table must be created in the NewsExtractDb database. You can try this:

    use NewsExtractDb;

    create table newstable (
    title nvarchar(255),
    summary nvarchar(255),
    link nvarchar(255),
    topic nvarchar(500)
    );

    and change sql query as below:

    sql = "INSERT INTO newstable (title, summary, link, topic) VALUES (%s, %s, %s, %s)"

    After this your code shoud run without errors.

    Login or Signup to reply.
  2. 0

    I don’t know if you did this, but I like to take the SQL from my Python script & plug it in MySQL workbench (or Toad or whatever database IDE you use) to see if there’s an issue w/ my query or and not w/ the script. It appears that cursor.execute may not like that last parameter. It maybe because it is a list or because it has single quotes wrapped around each item in the list. When I do an insert, like below, it works. I used the database.schema convention for the table name and changed "database" to "ddatabase" because I figured "database" may be a reserved word:

    INSERT INTO test_db.ddatabase (title, summary, link, topic) 
VALUES ('Guld för Odermatt i schweizisk dubbel', '' , 
'``https://www.dn.se/sport/guld-for-odermatt-i-schweizisk-dubbel/``', 
'SamhalleKonflikter')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search