How to insert multiple values ​​into MySQL database using Python script

Question

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']}]

I'm tired of 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 keep getting this error;

File "C:\Python311\Lib\site-packages\mysql\connector\connection_cext.py", line 616, in cmd_query throw get_mysql_Exception( mysql.connector.errors.ProgrammingError: 1064 (42000): There is an error in your SQL syntax; check the manual for your MySQL server version to see what is used near the "Database (title, summary, link, subject) value" Correct syntax ("Guld för Odermatt i schweizisk" in line 1

I'm tired of rewriting code with for loops 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 with the same error and can't fix it. Any ideas?

Answer 1

The "INSERT INTO" statement should be followed by a valid table name. But in your query, the table name is "database" which is an invalid identifier for a table name. To insert data into a table, you must first create the table 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 the sql query as follows:

sql = "Insert news table (title, abstract, link, topic) values ​​(%s, %s, %s, %s)"

After this, your code should run normally.