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.

1 Answer 1

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()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.