Insert python lists data into sql table instance

Question

I keep getting a sql syntax error when I run the script to import the data into sql.

here is the data in question,

filtered_interfaces = (['interface Gi1/0/7']
filtered_tech =  ['description TECH_5750'])
cab =  u'10.210.44.5'

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 '['description TECH_5750'],['interface Gi1/0/7'],10.20.94.5)' at line 1")

Ive attempted dictation and tried to match values as it gets placed in to the database, I have also tried %s inplace of {}.

Im getting the same error as above on both of these methods.

sql = "INSERT INTO device (hostname, cab, localint) VALUES ({0},{1},{2})".format(filtered_tech,filtered_interface,cab)

mycursor.execute(sql)

Long story short this application logs into switches at our DC's. It looks for CDP LLDP devices then spits out all that info into an array. I then filter the info looking for hostname and local interface. The goal is to import that data into sql (Its different on each switch). Then retrieve data on a html page and edit the hostname if applicable. I had the editable piece working but it wasn't scalable hints the sql additions. Apologies if this is an easy fix im not a programmer by trade, just trying to make life easier and learn python.

Tim Biegeleisen · Accepted Answer · 2019-05-28 16:25:52Z

1

If you are using MySQL behind your Python script, then you should be following the usage pattern in the documentation for prepared statements:

params = (filtered_tech, filtered_interface, cab,)
sql = """INSERT INTO device (hostname, cab, localint)
         VALUES (%s, %s, %s)"""
cursor.execute(sql, params)

# retrieve data here

answered May 28, 2019 at 16:25

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

1 Comment

pcam Over a year ago

works perfectly, one more question when i run that insert can i do some sort of .append to an existing query ? Right now its appending a new row with the same data.

Collectives™ on Stack Overflow

Insert python lists data into sql table instance

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