CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
Ray Gllisse
Ray Gllisse

Reputation: 85

Can't create a postgresql table using python

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

Upvotes: 1

Views: 566

Answers (1)

Ray Gllisse
Ray Gllisse

Reputation: 85

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")

Upvotes: 2

Related Questions