Redshift创建表无法通过Python工作 [英] Redshift create table not working via Python
问题描述
按照>使用IAM角色凭据使用Python卸载到S3 ，unload语句运行完美.我尝试过的其他命令也是如此，例如复制和选择语句.
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.
但是，我也尝试运行一个查询来创建一个表.create table查询运行时没有错误，但是当它进入select语句时，它抛出的错误为relation "public.test" does not exist.
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()
如果我直接在Redshift中运行相同的程序，那就很好了.
If I run the same directly in Redshift, it works just fine..
-编辑-
尝试过的一些事情:
-
从查询字符串中删除"\"
Removing "\" from query string
添加;"在查询字符串的末尾
adding ";" at the end of query string
将"public.test"更改为"test"
changing "public.test" to "test"
删除SetPath ="SET search_path TO％s"％SCHEMA和s.execute(SetPath)
removing SetPath = "SET search_path TO %s" % SCHEMA and s.execute(SetPath)
破坏create语句-产生预期的错误
breaking the create statement- generates expected error
在创建后从S3命令添加副本-运行没有错误，但同样没有创建表
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
添加r4 = s.execute(query4)-运行没有错误，但再次在Redshift中创建了表
adding r4 = s.execute(query4)- runs without error, but again created table not in Redshift
推荐答案
显然需要添加s.commit()才能创建表.复制命令(在创建表之后是可选的).基本上，它不会自动提交创建/更改命令！
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
这篇关于Redshift创建表无法通过Python工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
