Redshift create table not working via Python

Question

As per Unload to S3 with Python using IAM Role credentials, the unload statement worked perfectly. So did other commands I tried, like copy and select statements.

However, I also tried to run a query which creates a table.. The create table query runs without error, but when it gets to the select statement, it throws an errors that relation "public.test" does not exist.

Any idea why is the table not created properly? Query below:

import sqlalchemy as sa
from sqlalchemy.orm import sessionmaker
import config
import pandas as pd

#>>>>>>>> MAKE CHANGES HERE >>>>>>>>
DATABASE = "db"
USER = "user"
PASSWORD = getattr(config, 'password') #see answer by David Bern https://stackoverflow.com/questions/43136925/create-a-config-file-to-hold-values-like-username-password-url-in-python-behave/43137301
HOST = "host"
PORT = "5439"
SCHEMA = "public"      #default is "public"

########## connection and session creation ##########
connection_string = "redshift+psycopg2://%s:%s@%s:%s/%s" % (USER,PASSWORD,HOST,str(PORT),DATABASE)
engine = sa.create_engine(connection_string)
session = sessionmaker()
session.configure(bind=engine)
s = session()
SetPath = "SET search_path TO %s" % SCHEMA
s.execute(SetPath)

--create table example
query2 = '''\ 
create table public.test (
id integer encode lzo,
user_id integer encode lzo,
created_at timestamp encode delta32k,
updated_at timestamp encode delta32k
)
distkey(id)
sortkey(id)
'''

r2 = s.execute(query2)

--select example
query4 = '''\ 
select * from public.test
'''

r4 = s.execute(query4)

########## create DataFrame from SQL query output ##########
df = pd.read_sql_query(query4, connection_string)

print(df.head(50))

########## close session in the end ##########
s.close()

If I run the same directly in Redshift, it works just fine..

--Edit--

Some of the things tried:

• Removing "\" from query string

• adding ";" at the end of query string

• changing "public.test" to "test"

• removing SetPath = "SET search_path TO %s" % SCHEMA and s.execute(SetPath)

• breaking the create statement- generates expected error

• adding copy from S3 command after create- runs without error, but again no table created

• adding a column to create statement that doesnt exist in the file that is generated from the copy command- generates expected error

• adding r4 = s.execute(query4)- runs without error, but again created table not in Redshift

Answer 1

Apparently need to add s.commit() in order to create the table.. If you are populating it via copy command or insert into: then add it after the copy command (after the create table is optional). Basically, it does not auto commit for create/alter commands!

http://docs.sqlalchemy.org/en/latest/orm/session_basics.html#session-faq-whentocreate http://docs.sqlalchemy.org/en/latest/core/connections.html#understanding-autocommit