Python Forum
pymysql.err.ProgrammingError : 1064 You have an error in your SQL syntax
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
pymysql.err.ProgrammingError : 1064 You have an error in your SQL syntax
#1
I tried all the possible ways to get out this error.. I still didn't understand where its going wrong, below is my code,

mysql_conn = mysql_getConn('db')
cur = mysql_conn.cursor()
cur._defer_warnings=True
mysql_conn.escape_string("")

def load_table():
    for fname in files_path:
        filename_ext = os.path.basename(fname)
        l_filename, file_extension = os.path.splitext(filename_ext)

        with open(fname,'r') as loop_files:
            csvreader = csv.reader(loop_files,delimiter='|')
            fields=next(csvreader)
            fields_1 = map(lambda x: '`'+x+'`',fields)
            f_str=",".join(fields_1)

            # find the length of the columns and add that many place hollders #
            #place_holder = ','.join(['"'+'%s'+'"'] * len(fields))
            place_holder = ','.join(['%s'] * len(fields))

            insert_into = "INSERT INTO "
            obracket ="("
            cbracket =")"
            values = "VALUES"
            semi_colon=";"

            f_insert_into = f'{insert_into}'
            f_obracket=f'{obracket}'
            f_cbracket=f'{cbracket}'
            f_values=f'{values}'
            f_semi_colon=f'{semi_colon}'
            sql_format = f_insert_into + f'{database_name}'+'.'+ f'{l_filename}' + f_obracket + f'{f_str}' +\ 
                                                                            f_cbracket
            sql_format_values = f_values + f_obracket + f'{place_holder}' +f_cbracket + f_semi_colon
            sql_format= '"' + f'{sql_format + sql_format_values}' +'"'

            t_tup =("Feb-2016","25%","10%","11%")
            print(sql_format,t_tup)
            cur.execute(sql_format,(t_tup))
            mysql_conn.commit()
In this code while cursor executing I am getting the error pymysql.err.ProgrammingError: (1064, '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 \'"INSERT INTO tbl(column1,columns2\' at line 1')

Note: Variable t_tup I will pass it from the file dynamically, here for testing purpose I am hard coding it
Reply
#2
Such a silly mistake I have made

Before

sql_format = "INSERT INTO " + database_name + "." + l_filename + "(" + f_str + ")" 
I changed this to

f'INSERT INTO {l_filename} ({f_str}) VALUES ({place_holder})'

In my question from line 21 to 35 is a wrong way of formatting SQL query. That caused the issue, hope this might help someone.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Syntax error for "root = Tk()" dlwaddel 15 1,013 Jan-29-2024, 12:07 AM
Last Post: dlwaddel
Photo SYNTAX ERROR Yannko 3 334 Jan-19-2024, 01:20 PM
Last Post: rob101
  Syntax error while executing the Python code in Linux DivAsh 8 1,454 Jul-19-2023, 06:27 PM
Last Post: Lahearle
  Code is returning the incorrect values. syntax error 007sonic 6 1,137 Jun-19-2023, 03:35 AM
Last Post: 007sonic
  Error 1064 (42000) when executing UPDATE SQL gratiszzzz 7 1,366 May-22-2023, 02:38 PM
Last Post: buran
  syntax error question - string mgallotti 5 1,251 Feb-03-2023, 05:10 PM
Last Post: mgallotti
  Syntax error? I don't see it KenHorse 4 1,194 Jan-15-2023, 07:49 PM
Last Post: Gribouillis
  Syntax error tibbj001 2 847 Dec-05-2022, 06:38 PM
Last Post: deanhystad
  Python-for-Android:p4a: syntax error in main.py while compiling apk jttolleson 2 1,777 Sep-17-2022, 04:09 AM
Last Post: jttolleson
  Mysql Syntax error in pymysql ilknurg 4 2,291 May-18-2022, 06:50 AM
Last Post: ibreeden

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020