Write dictionnary with tuple containing parameters as unique value for features into postgresql table

Question

In Python 2.7, let a dictionary with features' IDs as keys.
There are thousands of features.
Each feature has a single value, but this value is a tuple containing 6 parameters for the features (for example; size, color, etc.)

On the other hand I have a postgreSQL table in a database where these features parameters must be saved.
The features' IDs are already set in the table (as well as other informations about these features).
The IDs are unique (they are random (thus not serial) but unique numbers).
There is 6 empty columns with names: "param1", "param2", "param3", ..., "param6".
I already have a tuple containing these names:

columns = ("param1", "param2", "param3", ..., "param6")

The code I have doesn't work for saving these parameters in their respective columns for each feature:

# "view" is the dictionary with features's ID as keys() 
# and their 6 params stored in values().
values  = [view[i] for i in view.keys()]
columns =  ("param1","param2","param3","param4","param5","param6")


conn = psycopg2.connect("dbname=mydb user=username password=password")
curs = conn.cursor() 

curs.execute("DROP TABLE IF EXISTS mytable;")
curs.execute("CREATE TABLE IF NOT EXISTS mytable (LIKE originaltable including defaults including constraints including indexes);")
curs.execute("INSERT INTO mytable SELECT * from originaltable;")

insertstatmnt  = 'INSERT INTO mytable (%s) values %s'
alterstatement = ('ALTER TABLE mytable '+
                  'ADD COLUMN param1 text,'+
                  'ADD COLUMN param2 text,'+
                  'ADD COLUMN param3 real,'+
                  'ADD COLUMN param4 text,'+
                  'ADD COLUMN param5 text,'+
                  'ADD COLUMN param6 text;'
                  )
curs.execute(alterstatement) # It's working up to this point.
curs.execute(insertstatmnt, (psycopg2.extensions.AsIs(','.join(columns)), tuple(values))) # The problem seems to be here.

conn.commit() # Making change to DB !
curs.close()
conn.close()

Here's the error I have:

curs.execute(insert_statement, (psycopg2.extensions.AsIs(','.join(columns)), tuple(values)))

ProgrammingError: INSERT has more expressions than target columns

I must miss something.
How to do that properly?

Hundreds of MB...! Shortly: `values` is initially a list containing, for each feature, the 6 values for the 6 parameters. The 6 values pro feature (they are thousands of features) are stored as a single tuple within the list. Each of the 6 elements of the tuple is a string, except one which is a number. E.g. : `type(values)` -> `list` and `type(value[5617])` -> `tuple`, and `type(values[5617][0])` to `type(values[5617][5])` -> `str` except for one which is `float`. There may be `NoneType` elsewhere also. — swiss_knight, Jun 06 '17 at 17:05

score 1 · Answer 1 · answered Jun 06 '17 at 14:10

When using '%s' to get the statement as what I think you want, you just need to change a couple things.

Ignoring c.execute(), this statement is by no means wrong, but it does not return what you are looking for. Using my own version, this is what I got with that statement. I also ignored psycopg2.extensions.AsIs() because, it is just a Adapter conform to the ISQLQuote protocol useful for objects whose string representation is already valid as SQL representation.

>>> values = [ i for i in range(0,5)] #being I dont know the keys, I just made up values.

>>> insertstatmnt, (','.join(columns), tuple(vlaues))
>>> ('INSERT INTO mytable (%s) values %s', ('param1,param2,param3,param4,param5,param6', (0, 1, 2, 3, 4)))

As you can see, what you entered returns a tuple with the values.

>>> insertstatmnt % (','.join(columns), tuple(values))
>>> 'INSERT INTO mytable (param1,param2,param3,param4,param5,param6) values (0, 1, 2, 3, 4)'

Where as, this returns a string that is more likely to be read by the SQL. The values obviously do not match the specified ones. I believe the problem you have lies within creating your string.

Reference for pycopg2: http://initd.org/psycopg/docs/extensions.html

the 2nd won't work as `curs.execute()` need 2 input args separated by a comma, not a percent sign: `TypeError: unsupported operand type(s) for &: 'str' and 'tuple'` — swiss_knight, Jun 06 '17 at 16:58
@s.k the percent sign creates the string, what package does execute come from? I maybe thinking of a different execute — Jack West, Jun 07 '17 at 01:30

score 0 · Answer 2 · answered Jun 06 '17 at 18:06

As I took the syntax of the psycopg2 command from this thread: Insert Python Dictionary using Psycopg2

and as my values dictionary doesn't exactly follow the same structure as the mentioned example (I also have 1 key as ID, like in this example, but mine has only 1 corresponding value, as a tuple containing my 6-parameters, thus "nested 1 lever deeper" instead of directly 6 values corresponding to the keys) I need to loop through all features to execute one SQL statement per feature:

[curs.execute(insertstatmnt, (psycopg2.extensions.AsIs(', '.join(columns)), i)) for i in tuple(values)].

This, is working.

Write dictionnary with tuple containing parameters as unique value for features into postgresql table

2 Answers2