1

I am having an issue that I don't really have programming experience to resolve. I have an application that deploys an SQL table that may have more or less columns depending on user input. I am currently struggling with the insert command being more dynamic.

Here is an example of the function call with hand populated data. But this is how I believe that data needs to be presented.

table = "Barcode_data"
headers = ('time', 'Barcode', 'TestDintSensor', 'TestIntSensor2', 'TestRealSensor4', 'TestSintSensor3')
data = ('2022-01-21 08:18:24', 'BCR12345', '1', '2', '2.3', '4')
connection.insertIntoData(table, headers,data )

Here is what I have for a function

def insertIntoData(self,table,headers,values): # In Progress

header1 = headers[0]
header2 = headers[1]
header3 = headers[2]
header4 = headers[3]
header5 = headers[4]
header6 = headers[5]

value1 = values[0]
value2 = values[1]
value3 = values[2]
value4 = values[3]
value5 = values[4]
value6 = values[5]

sql = (f"INSERT INTO {table} ({header1}, {header2}, {header3}, {header4}, {header5}, {header6}) VALUES (%s,%s,%s,%s,%s,%s)")
data = (value1,value2,value3,value4,value5,value6)
print(sql,data)
self.cur.execute(sql, data)
self.conn.commit()

I can run this code and it will insert a field into the table. However there are two major flaws. Where I wrote header1, header2... should actually be {headers} so it would grow dynamically.

The issue with this is that the current way it works I will get a output that looks like (header1,header2,header3 etc... if I use {headers} i will get an output ('header1','header2','header3 etc... This creates a syntax error with the SQL.

The second issue is the %s,%s... in the VALUES section. I am not sure how to approach growing this. I could write a bunch of functions one for # of columns and then call the correct one based on the len(headers) but I think there must be a better way.

Kevin Lay
  • 21
  • 2

1 Answers1

1

I was able to get this to work. To format it how I needed I was able to use a .join function with a delimiter specified. This gave me the format I needed without the ''

For the values issue I created a loops to build a string based on the length of the headers. I added the index count to avoid on the first iteration adding the comma at the start.

def insertIntoData(self,table,headers,values): # In Progress
    delimiter = ','
    valueStr = ""
    index = 0

for header in headers:
    if index == 0:
        valueStr = "%s"
    else:
        valueStr = valueStr + ", %s"
    index = index +1

# INSERT INTO `Barcode_data` (`time`, `Barcode`, `TestDintSensor`, `TestIntSensor2`, `TestRealSensor4`, `TestSintSensor3`) VALUES ('2022-01-21 08:18:24', 'BCR12345', '1', '2', '2.3', '4')

sql = (f"INSERT INTO {table} ({delimiter.join(headers)}) VALUES ({valueStr})")
data = (values)
self.cur.execute(sql, data)
self.conn.commit()
Kevin Lay
  • 21
  • 2