Error in SQL INSERT INTO statement in Python script

Question

I'm an Mechanical Engineer with a little experience in C and C++ languages and no experience in Python or SQL labguages.

Recently, I started to work on stock prices analyses in order to optimise my portfolio. I started with an Excel file and several VBA macros. It works quite good but is very slow. So, I'm now trying to step up and set up a proper "stock prices" database on my server (based on this post).

In the "stock_prices" database, there is an 'exchange' table that stores all the markets identification codes (MIC) and related information. In order to update the exchange table, a python script will be launched once a month and it includes the below Python / SQL statements.

import pymysql.cursors
conn = pymysql.connect(host='localhost', user='xxx', password='yyy', database='stock_prices')
cursor = conn.cursor()
mic_data = pd.read_excel('https://www.iso20022.org/sites/default/files/'+ str(date.year) + '-' + str(format(date.month, '02d')) + '/ISO10383_MIC.xls', sheet_name='MICs List by Country', na_filter=False)
mic_data.columns = ['country', 'iso_country_code', 'mic', 'operating_mic', 'mic_type', 'name', 'acronym', 'city', 'website', 'status_date', 'status', 'creation_date', 'comments']
for row in mic_data.itertuples(index=False):
    cursor.execute("INSERT INTO exchange(country, iso_country_code, mic, operating_mic, mic_type, name, acronym, city, website, status_date, status, creation_date, comments) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE ", row)

Unfortunately, the "Insert INTO" statement returns an error : 1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1"

I have read several posts on this error (which seems quite common) and have tried the following modifications that all returned the same error:

cursor.execute("INSERT INTO exchange(country, iso_country_code, mic, operating_mic, mic_type, name, acronym, city, website, status_date, status, creation_date, comments) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE", row)

cursor.execute("INSERT INTO exchange(country, iso_country_code, mic, operating_mic, mic_type, name, acronym, city, website, status_date, status, creation_date, comments) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE;", row)

cursor.execute("""INSERT INTO exchange(country, iso_country_code, mic, operating_mic, mic_type, name, acronym, city, website, status_date, status, creation_date, comments) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE""", row)

I have also tried this modification :

cursor.execute("INSERT INTO exchange('country', 'iso_country_code', 'mic', 'operating_mic', 'mic_type', 'name', 'acronym', 'city', 'website', 'status_date', 'status', 'creation_date', 'comments') VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE", row)

with a slightly different result : 1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''country', 'iso_country_code', 'mic', 'operating_mic', 'mic_type', 'name', 'acro' at line 1"

However, I don't see anything special near 'country'.

Could anybody suggest modifications to the "INSERT INTO" statement that I could try ?

Best Regards,

Edit 31/03/2020 : Statement after correction

cursor.execute("INSERT INTO exchange(country, iso_country_code, mic, operating_mic, mic_type, name, acronym, city, website, status_date, status, creation_date, comments) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE country=%s, iso_country_code=%s, mic=%s, mic_type=%s, name=%s, acronym=%s, city=%s, website=%s, status_date=%s, status=%s, creation_date=%s, comments=%s", (row.country, row.iso_country_code, row.mic, row.operating_mic, row.mic_type, row.name, row.acronym, row.city, row.website, row.status_date, row.status, row.creation_date, row.comments,row.country, row.iso_country_code, row.mic, row.mic_type, row.name, row.acronym, row.city, row.website, row.status_date, row.status, row.creation_date, row.comments))

This is the query for the first line : INSERT INTO exchange(country, iso_country_code, mic, operating_mic, mic_type, name, acronym, city, website, status_date, status, creation_date, comments) VALUES('ALBANIA', 'AL', 'XALS', 'XALS', 'O', 'ALBANIA SECURITIES EXCHANGE', 'ALSE', 'TIRANA', 'WWW.ALSE.AL', 'APRIL 2019', 'ACTIVE', 'APRIL 2019', 'ALL CLASSES OF SECURITIES.') ON DUPLICATE KEY UPDATE — JeanYves
– JeanYves, Commented Mar 29, 2020 at 11:23

score 0 · Accepted Answer · 2020-03-29 15:43:46Z

0

There is no issue with the code I checked it the only issue you have is that ON DUPLICATE KEY UPDATE requires a condition take a look here

Edit: you cannot have strings regarding column fields you can use (`) backtick or just leave them as they are in the query below

Your query should look like this now:

INSERT INTO exchange(country, iso_country_code, mic, operating_mic, mic_type, name, acronym, city, website, status_date, status, creation_date, comments) 
VALUES('ALBANIA', 'AL', 'XALS', 'XALS', 'O', 'ALBANIA SECURITIES EXCHANGE', 'ALSE', 'TIRANA', 'WWW.ALSE.AL', 'APRIL 2019', 'ACTIVE', 'APRIL 2019', 'ALL CLASSES OF SECURITIES.') ON DUPLICATE KEY UPDATE operating_mic='XALN'

Python:

cursor.execute("INSERT INTO exchange(`country`, `iso_country_code`, `mic`, `operating_mic`, `mic_type`, `name`, `acronym`, `city`, `website`, `status_date`, `status`, `creation_date`, `comments`) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE operating_mic='XALN'", row)

OR

cursor.execute("INSERT INTO exchange(country, iso_country_code, mic, operating_mic, mic_type, name, acronym, city, website, status_date, status, creation_date, comments) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE operating_mic='XALN'", row)

edited Mar 29, 2020 at 15:43

answered Mar 29, 2020 at 11:39

user9201277

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

3 Comments

JeanYves Over a year ago

I have read the MySQLTutorial and tried this statement : INSERT INTO exchange('country', 'iso_country_code', 'mic', 'operating_mic', 'mic_type', 'name', 'acronym', 'city', 'website', 'status_date', 'status', 'creation_date', 'comments') VALUES('ALBANIA', 'AL', 'XALS', 'XALS', 'O', 'ALBANIA SECURITIES EXCHANGE', 'ALSE', 'TIRANA', 'WWW.ALSE.AL', 'APRIL 2019', 'ACTIVE', 'APRIL 2019', 'ALL CLASSES OF SECURITIES.') ON DUPLICATE KEY UPDATE operating_mic='XALN' The same error appears.

user9201277 Over a year ago

I updated the answer check it out and let me know if you figured it out!

JeanYves Over a year ago

Hello Midrizi, The corrected query doesn't generate any error anymore. I have added the final query in my initial post. I still hope that, one day, I can make this query shorter/clearer, but for now it does the job and I'm already very happy with that. Thanks a lot !

Collectives™ on Stack Overflow

Error in SQL INSERT INTO statement in Python script

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related