Can't create a postgresql table using python

Question

I am trying to create tables out of json files containing the field names and types of each table of a database downloaded from Bigquery. The SQL request semt fine to me and but no table was created according to psql command-line interpreter typing \d So, to begin I've just tried with a simpler sql request that doesn't work neither, Here is the code :

import pandas as pd
import psycopg2

# information used to create a database connection
sqluser = 'postgres'
dbname = 'testdb'

pwd = 'postgres'
# Connect to postgres database
con = psycopg2.connect(dbname=dbname, user=sqluser, password=pwd )
curs=con.cursor()

q="""set search_path to public,public ;
CREATE TABLE tab1(
i INTEGER
);
"""

curs.execute(q)

q = """
SELECT table_name
FROM information_schema.tables
       WHERE table_schema='public'
       AND table_type='BASE TABLE';
    """

df = pd.read_sql_query(q, con)
print(df.head())
print("End of test")

The code written above displays this new table tab1, but actually this new table doesn't appear listed when typing \d within the psql command line interpreter. If I type in the psql interpreter :

SELECT table_name
      FROM information_schema.tables
       WHERE table_type='BASE TABLE';

it doesn't get listed neither , seems it's not actually created, Thanks in advance for your help

Ray Gllisse · Accepted Answer · 2021-04-18 19:52:35Z

2

There was a commit() call missing, that must be written after the table creation sql request, This code works:

import pandas as pd
import psycopg2

# information used to create a database connection
sqluser = 'postgres'
dbname = 'testdb'

pwd = 'postgres'
# Connect to postgres database
con = psycopg2.connect(dbname=dbname, user=sqluser, password=pwd )
curs=con.cursor()

q="""set search_path to public,public ;
CREATE TABLE tab1(
i INTEGER
);
"""

curs.execute(q)
con.commit()
q = """
SELECT table_name
FROM information_schema.tables
       WHERE table_schema='public'
       AND table_type='BASE TABLE';
    """

df = pd.read_sql_query(q, con)
print(df.head())
print("End of test")

answered Apr 18, 2021 at 19:52

Ray Gllisse

959 bronze badges

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

Collectives™ on Stack Overflow

Can't create a postgresql table using python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related