Create table using definition variable

Question

I'm trying to create a table in PostgreSQL using python psycopg2. So when I execute: create_table(location) it makes the location table. I've looked at various tutorials around the web. but I just can't get it to work, any ideas?

import psycopg2

def create_table(table_name):
    try:
        conn = psycopg2.connect("dbname=db host=localhost user=user password=pw")
    except:
        print("I am unable to connect to the database") 

    cur = conn.cursor()
    try:
        cur.execute('''
        CREATE TABLE public.s% (
                id serial NOT NULL,
                x double precision,
                y double precision,
                z double precision,
                geom geometry, 
                dist double precision,
                CONSTRAINT test21_pkey PRIMARY KEY (id))
                ''')
    except:
        print("Error!")

    conn.commit()
    conn.close()
    cur.close()

Ilja Everilä · Accepted Answer · 2018-10-07 09:47:00Z

1

The s% in your original code looks like an attempt at %-formatting, though the conversion specifier should be %s and you do not actually format the string. Since you are using psycopg2 you should use the SQL string composition tools offered by it:

from psycopg2 import sql

stmt = sql.SQL('''CREATE TABLE public.{} (
    id serial NOT NULL,
    x double precision,
    y double precision,
    z double precision,
    geom geometry, 
    dist double precision,
    CONSTRAINT test21_pkey PRIMARY KEY (id))
    ''')

cur.execute(stmt.format(sql.Identifier(table_name)))

Note that stmt.format() is not str.format(), but SQL.format(). This handles quoting the identifier correctly, if necessary.

edited Oct 7, 2018 at 9:47

answered Oct 7, 2018 at 9:40

Ilja Everilä

53.4k9 gold badges138 silver badges142 bronze badges

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

Comments

Arihant · Accepted Answer · 2018-10-07 09:33:28Z

0

Try the below,

try:
        cur.execute('
        CREATE TABLE public.{} (
                id serial NOT NULL,
                x double precision,
                y double precision,
                z double precision,
                geom geometry, 
                dist double precision,
                CONSTRAINT test21_pkey PRIMARY KEY (id))
                '.format(table_name))

edited Oct 7, 2018 at 9:33

answered Oct 7, 2018 at 9:31

Arihant

7455 silver badges14 bronze badges

Collectives™ on Stack Overflow

Create table using definition variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related