Unable to insert multiple rows to sql table from python-CodePudding

Even though I pass a list as params I am getting the below error while executing the query

TypeError: ('Params must be in a list, tuple, or Row', 'HY000')

I am trying to pass multiple rows to sql table using executemany

Please find my code below and help, I am new to python.

query = """INSERT INTO TableTest (Summary) Values (%s)"""
val = [('SPHXNORF2ASW1'),('CHNSIRSDB1USAA'),('NKOLGTPRAVPNVM'),('STAMHO3WANCEG'),('SPHXNORWANCEG1'),('SPHXNORWANCE'),('STAMHO3WANCEG')]
       
     
                 
      
cursor = conn.cursor()

cursor.executemany(query,val)

conn.commit()   ```

CodePudding user response：

In python, putting a value inside '()' does not make it tuple. So, this code

val = [('SPHXNORF2ASW1'),('CHNSIRSDB1USAA'),('NKOLGTPRAVPNVM'),('STAMHO3WANCEG'),('SPHXNORWANCEG1'),('SPHXNORWANCE'),('STAMHO3WANCEG')]

will be converted to

['SPHXNORF2ASW1', 'CHNSIRSDB1USAA', 'NKOLGTPRAVPNVM', 'STAMHO3WANCEG', 'SPHXNORWANCEG1', 'SPHXNORWANCE', 'STAMHO3WANCEG']

If you want to specify it as a tuple, add a trailing comma like below or use '[]' instead of tuple.

Use:

val = [('SPHXNORF2ASW1',),('CHNSIRSDB1USAA',),('NKOLGTPRAVPNVM',),('STAMHO3WANCEG',),('SPHXNORWANCEG1',),('SPHXNORWANCE',),('STAMHO3WANCEG',)]

Or:

[ ['SPHXNORF2ASW1'], ['CHNSIRSDB1USAA'], ['NKOLGTPRAVPNVM'], ['STAMHO3WANCEG'], ['SPHXNORWANCEG1'], ['SPHXNORWANCE'], ['STAMHO3WANCEG']]

CodePudding user response：

I recommend you to learn python first. It's a very easy and simple error. Basically you are writing your code in a wrong way, it should be like this:

query = "INSERT INTO TableTest (Summary) Values(?)"
vals = [('SPHXNORF2ASW1'),('CHNSIRSDB1USAA'),('NKOLGTPRAVPNVM'),('STAMHO3WANCEG'),('SPHXNORWANCEG1'),('SPHXNORWANCE'),('STAMHO3WANCEG')]
cursor = conn.cursor()

# you could just do a for-loop instead of using executemany()
for val in vals:
    cursor.execute(query, val)

conn.commit()

Basically for-loops executes a statement until the each and every item in a list is executed. So it will execute the statement 7 times as the list has 7 items.