Search code examples
python-3.xpsycopg2

create a schema in psycopg2 and set it as a default

I'm having a hard time setting up a schema name using psycopg2 library and use it as default for other operations (create/copy tables etc).

import psycopg2
from psycopg2.extras import RealDictCursor
import re

# Configuration Values
endpoint_db = 'localhost'
database_db = 'postgres'
username_db = 'postgres'
password_db = '****'
port_db = 5432


# Connect
connection = psycopg2.connect(
     dbname=database_db, 
     user=username_db,
     password=password_db,
     host=endpoint_db,
     port=port_db
 )

 # Set the cursor
connection.autocommit = True
cursor = connection.cursor(cursor_factory = RealDictCursor)

# Set the schema name
key = '36107.csv' # eventually this will be a file from a S3 bucket;
fips = re.sub(".csv", "", key) + "county" # create a schema named as the key code

# Create commands
create_schema_sql = """ CREATE SCHEMA IF NOT EXISTS \"%s\""""
alter_db_sql = """ ALTER DATABASE raster SET search_path TO \"%s\""""

# Call commands
cursor.execute(create_schema_sql, (fips,))
cursor.execute(alter_db_sql, (fips,))

When I'm calling the cursor.execute(create_schema_sql, (fips,)), that is creating indeed a schema in my database but with a single quote character in front/after the name ('county36107') which is undesirable and make my life more complicated later on.

If I am doing as mentioned in the documentation:

create_schema_sql = """ CREATE SCHEMA IF NOT EXISTS %s """
cursor.execute(create_schema_sql, (fips,))

That is gonna fail miserably:

SyntaxError: syntax error at or near "'36107county'"
LINE 1:  CREATE SCHEMA IF NOT EXISTS '36107county'

I'm assuming there is a solution as mentioned here: https://www.psycopg.org/psycopg3/docs/basic/params.html#index-1 -but I don't see how to solve this.

Secondly, when I'm trying to make that '36107county' as my default schema using the alter database set search_path to command, nothing happens. Is there a way to solve this?

Solution

  • A quick example showing use of sql module to build dynamic SQL and use of SET to change search_path for session.

    import re
import psycopg2
from psycopg2 import sql
from psycopg2.extras import RealDictCursor
con = psycopg2.connect("dbname=test host=localhost  user=postgres")
cur = con.cursor(cursor_factory = RealDictCursor))
fips = re.sub(".csv", "", key) + "county"

create_schema_sql = sql.SQL("CREATE SCHEMA IF NOT EXISTS {}").format(sql.Identifier(fips))
print(create_schema_sql.as_string(con))
CREATE SCHEMA IF NOT EXISTS "36107county"

cur.execute(create_schema_sql)
#pg_namespace is system catalog that holds schema information.
cur.execute("select nspname from pg_namespace where nspname = %s", [fips])
cur.fetchone()                                                                                                                                                            
RealDictRow([('nspname', '36107county')])


cur.execute("SET search_path = %s", [fips])
cur.execute("SHOW search_path")
cur.fetchone()                                                                                                                                                            
RealDictRow([('search_path', '"36107county"')])