how to insert filename as a string variable into mysql database in python

Question

I have extracted filename from a certain directory. Filename is a string variable. Such as: 'LineNo-YYMMDD_hhmm'. Now I want to insert the filename into the existing MySQL DB. But couldn't find how to add as a str variable. This is what I had tried. Have some other way also checking net.

....

file_prefix = os.path.basename(name)[: - (len(filetail))]
(file_prefix is: LineName-200215_1619)
try: 
    connection = mysql.connector.connect(
                host='localhost', db = 'db_name', 
                user='usr', password='myPass')
   mysql_insert_query = " INSERT INTO fileHeader (headInfo) VALUES %s" % str(file_prefix)
   cursor = connection.cursor()
   cursor.execute(mysql_insert_query)
   connection.commit()
   cursor.close()

except mysql.connector.Error as error:
    print ("Failed to insert record into fileHeader table {}".format(error))

Getting the following error:

Failed to insert record into fileHeader table 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  
'LineName-200215_1619' at line 1

Lemon · Accepted Answer · 2020-02-17 09:56:26Z

1

add '' in your mysql_insert_query:

 mysql_insert_query = " INSERT INTO fileHeader (headInfo) VALUES ( '{0}')".format(file_prefix)

answered Feb 17, 2020 at 9:56

Lemon

1412 silver badges7 bronze badges

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

1 Comment

Fakir Over a year ago

Perfect! Thanks a lot.

Collectives™ on Stack Overflow

how to insert filename as a string variable into mysql database in python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related