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

Remove the "\" character from your query string. Multi-line strings start and end with triple quotes or triple single quotes. I think the backslash character is breaking your query. However, you should be getting an error if this is true. When you look at Redshift, do you see any new tables? — John Hanley
– John Hanley, Commented Jan 12, 2018 at 19:38
@JohnHanley The "\" character allows me to unload/create/select so that shouldn't be it, but after removing it still same issue. No the table is not being created from the script above. However running the create statement directly in redshift does create the table — user8834780
– user8834780, Commented Jan 12, 2018 at 19:54
Try changing "public.test" to "test" OR comment out the two lines that set the schema: SetPath = "SET search_path TO %s" % SCHEMA s.execute(SetPath) — John Hanley
– John Hanley, Commented Jan 12, 2018 at 19:59
@JohnHanley tried both- no go. Does it work if you run the same on your end? — user8834780
– user8834780, Commented Jan 12, 2018 at 20:03
Deliberately break the CREATE Table sql statement and verify that you are getting an error message. Example change "CREATE" to XXCREATE". — John Hanley
– John Hanley, Commented Jan 12, 2018 at 20:05

user8834780 · Accepted Answer · 2018-01-12 21:44:30Z

3

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

edited Jan 12, 2018 at 21:44

answered Jan 12, 2018 at 21:25

user8834780

1,6805 gold badges25 silver badges52 bronze badges

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

1 Comment

John Hanley Over a year ago

Thank you for posting the solution. I cannot believe that I overlooked the commit part. My code always includes the commit, I just did not notice it missing in the code.

Collectives™ on Stack Overflow

Redshift create table not working via Python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related