I can't create table by getting column names from a list?(postgresql/psycopg2)

Question

I have prepared two sample lists below. My goal is to create a table with these two lists in postgresql.id will be bigserial primary key.but I keep getting errors. how do you think i can do that?

My example list and code:

my_column_name = ['id','first name','surname','age']

data= [{'Jimmy', 'wallece', 17}]


connection = psycopg2.connect(user = "postgres",
                              password = "Sabcanuy.1264",
                              host="127.0.0.1",
                              port="5432",
                              database="postgres")

cursor = connection.cursor()


create_table_query = '''CREATE TABLE unit_category_report (ID BIGSERIAL  PRIMARY KEY , 
my_columne_name); '''

Answer 1

Strings cannot access variables and their values.

I’m not 100% sure this will work but you can try:

my_column_name =['id','first_name','surname','age']

create_table_query = '''CREATE TABLE unit_category_report (ID BIGSERIAL  PRIMARY KEY , %s); ''' % (my_column_name)

Or...

create_table_query = '''CREATE TABLE unit_category_report (ID BIGSERIAL  PRIMARY KEY , {0}); '''.format(my_column_name)

You may have to switch to double quotes from the triple single quote.