1

I am trying to submit data to a Sqlite db through python with executemany(). I am reading data from a JSON file and then placing it into the db. My problem is that the JSON creation is not under my control and depending on who I get the file from, the order of values is not the same each time. The keys are correct so they correlate with the keys in the db but I can't just toss the values at the executemany() function and have the data appear in the correct columns each time.

Here is what I need to be able to do.

keyTuple = (name, address, telephone)

listOfTuples = [(name1, address1, telephone1),
                (name2, address2, telephone2),
                (...)]

cur.executemany("INSERT INTO myTable(?,?,?)", keysTuple"
                "VALUES(?,?,?)", listOfTuples)

The problem I have is that some JSON files have order of "name, telephone, address" or some other order. I need to be able to input my keysTuple into the INSERT portion of the command so I can keep my relations straight no matter what order the JSON file come in without having to completely rebuild the listOfTuples. I know there has got to be a way but what I have written doesn't match the right syntax for the INSERT portion. The VALUES line works just fine, it uses each element in listofTuples.

Sorry if I am not asking with the correct verbage. FNG here and this is my first post. I have look all over the web but it only produces the examples of using ? in the VALUE portion, never in the INSERT INTO portion.

Aaron Bowman
  • 13
  • 2

2 Answers2

0

You cannot use SQL parameters (?) for table/column names.

But when you already have the column names in the correct order, you can simply join them in order to be able to insert them into the SQL command string:

>>> keyTuple = ("name", "address", "telephone")
>>> "INSERT INTO MyTable(" +  ",".join(keyTuple) + ")"
'INSERT INTO MyTable(name,address,telephone)'
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Perfect, this is exactly what I needed. I had tried to write them to a string such as "'name1', 'address1', 'telephone1'" but it kept telling me it was expecting 3 names and only getting one because I was trying to submit the string variable with all three names included. This concatenation process is just what I needed. I will give it a try. Thank you – Aaron Bowman Aug 20 '17 at 23:56
-1

Try this

Example if you have table named products with the following fields:

Prod_Name Char( 30 ) UOM Char( 10 ) Reference Char( 10 ) Const Float Price Float

list_products = [('Garlic', '5 Gr.', 'Can', 1.10, 2.00), ('Beans', '8 On.', 'Bag', 1.25, 2.25), ('Apples', '1 Un.', 'Unit', 0.25, 0.30), ] c.executemany('Insert Into products Values (?,?,?,?,?)', list_products )

Felix Ramirez
  • 74
  • 1
  • 8