How to dynamically add data from a list to a database (PostgreSQL)

Question

DataBase is already created(PostgreSQL)

There is a list:

data = 
['param_1', 0], 
['amp', 0], 
['voltage', 1], 
['params', 1], 
['antenna', 1], 
['freq', 0.00011000000085914508]

I tried that

import psycopg2
from psycopg2 import sql 

    with psycopg2.connect(dbname='db_name', user='postgres',
                      password='123', host='localhost') as conn:
        conn.autocommit = True
        with conn.cursor() as cur:
            query = "INSERT INTO table_name (%s) VALUES (%s);"
            cur.executemany(query, data)

I need to insert values in the table in the database that contains fields named: 'param_1': 'paRam_2' e.t.c.

How do I generate a query string?

I will be happy for any help, thanks in advance, stack.

So you want to insert a single row with those numeric values written to the corresponding columns? — Gord Thompson
– Gord Thompson, Commented Oct 16, 2019 at 19:20

Gord Thompson · Accepted Answer · 2019-11-12 13:52:45Z

3

Parameter substitution can only be used to pass column values, not column names, so we'll need to build a list of column names to insert into the SQL command text. Specifically, we'll need to

build a comma-separated string of column names
build a comma-separated string of parameter placeholders
create the INSERT command, including the two items above
create a tuple of (numeric) parameter values
execute the command

That would look something like this:

# create environment
# ~~~~~~~~~~~~~~~~~~
data = (
['param_1', 0],
['amp', 0], 
['voltage', 1], 
['params', 1], 
['antenna', 1], 
['freq', 0.00011000000085914508]
)

# example code
# ~~~~~~~~~~~~
columns = ','.join([f'"{x[0]}"' for x in data])
print(columns)
# "param_1","amp","voltage","params","antenna","freq"

param_placeholders = ','.join(['%s' for x in range(len(data))])
print(param_placeholders)
# %s,%s,%s,%s,%s,%s

sql = f"INSERT INTO table_name ({columns}) VALUES ({param_placeholders})"
print(sql)
# INSERT INTO table_name ("param_1","amp","voltage","params","antenna","freq") VALUES (%s,%s,%s,%s,%s,%s)

param_values = tuple(x[1] for x in data)
print(param_values)
# (0, 0, 1, 1, 1, 0.00011000000085914508)

cur.execute(sql, param_values)

edited Nov 12, 2019 at 13:52

answered Oct 16, 2019 at 21:54

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kirill Vladi Over a year ago

Thank you very very much

Kirill Vladi · Accepted Answer · 2019-11-16 11:57:56Z

Thank's @GordThompson for his response. It's been very helpful and based on your response I have created two functions: - One for reading string of falues.
- Second to insert values into the table.

I would like to notice that psycopg2 did not fight for a dynamic lookup of the table name in the query string generation

import psycopg2

def insert_to(self, table_name: str, data: dict):
    # формирование строки запроса
    columns = ','.join([f'"{x}"' for x in data])
    param_placeholders = ','.join(['%s' for x in range(len(data))])
    query = f'INSERT INTO "{table_name}" ({columns}) VALUES ({param_placeholders})'
    param_values = tuple(x for x in data.values())
    try:
        self.cur.execute(query, param_values)
    except Exception as e:
        log.exception(f'\r\nException: {e}')
    else:
        log.warning(f'INSERT INTO "{table_name}" {data}')


def read_from(self, table_name: str, dict_for_get_pk: dict) -> any:
    # формирование строки запроса
    columns = ','.join([f'"{x}"' for x in dict_for_get_pk])
    param_placeholders = ','.join(['%s' for x in range(len(dict_for_get_pk))])
    query = f'SELECT * FROM "{table_name}" WHERE ({columns}) = ({param_placeholders})'
    param_values = tuple(x for x in dict_for_get_pk.values())
    try:
        self.cur.execute(query, param_values)
    except Exception as e:
        log.exception(f'\r\nException: {e}')
    else:
        db_values = self.cur.fetchall()
        if db_values:
            return db_values[0]
        else:
            return None

Collectives™ on Stack Overflow

How to dynamically add data from a list to a database (PostgreSQL)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related