postgresql 如何使用 SQLAlchemy 连接到 Amazon Redshift 中的集群？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35004936/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to connect to a cluster in Amazon Redshift using SQLAlchemy?
提问by Chris
In Amazon Redshift's Getting Started Guide, it's mentioned that you can utilize SQL client tools that are compatible with PostgreSQL to connect to your Amazon Redshift Cluster.
在 Amazon Redshift 的入门指南 中，提到您可以利用与 PostgreSQL 兼容的 SQL 客户端工具连接到您的 Amazon Redshift 集群。
In the tutorial, they utilize SQL Workbench/J client, but I'd like to utilize python (in particular SQLAlchemy). I've found a related question, but the issue is that it does not go into the detail or the python script that connects to the Redshift Cluster.
在本教程中，他们使用 SQL Workbench/J 客户端，但我想使用 python（特别是 SQLAlchemy）。我发现了一个相关的问题，但问题是它没有详细介绍连接到 Redshift 集群的 python 脚本。
I've been able to connect to the cluster via SQL Workbench/J, since I have the JDBC URL, as well as my username and password, but I'm not sure how to connect with SQLAlchemy.
我已经能够通过 SQL Workbench/J 连接到集群，因为我有 JDBC URL 以及我的用户名和密码，但我不确定如何连接 SQLAlchemy。
Based on this documentation, I've tried the following:
基于此文档，我尝试了以下操作：
from sqlalchemy import create_engine
engine = create_engine('jdbc:redshift://shippy.cx6x1vnxlk55.us-west-2.redshift.amazonaws.com:5439/shippy')
ERROR:
错误：
Could not parse rfc1738 URL from string 'jdbc:redshift://shippy.cx6x1vnxlk55.us-west-2.redshift.amazonaws.com:5439/shippy'
采纳答案by Joe Harris
I don't think SQL Alchemy "natively" knows about Redshift. You need to change the JDBC "URL" string to use postgres.
我不认为 SQL Alchemy “本机”了解 Redshift。您需要更改 JDBC“URL”字符串以使用postgres.
jdbc:postgres://shippy.cx6x1vnxlk55.us-west-2.redshift.amazonaws.com:5439/shippy
Alternatively, you may want to try using sqlalchemy-redshiftusing the instructions they provide.
或者，您可能想尝试使用sqlalchemy-redshift他们提供的说明。
回答by Jasper Croome
I was running into the exact same issue, and then I remembered to include my Redshift credentials:
我遇到了完全相同的问题，然后我记得包含我的 Redshift 凭据：
eng = create_engine('postgres://[LOGIN]:[PWORD]@shippy.cx6x1vnxlk55.us-west-2.redshift.amazonaws.com:5439/shippy
回答by Anshik
sqlalchemy-redshift is works for me, but after few days of reserch packages (python3.4):
sqlalchemy-redshift 对我有用，但经过几天的研究包（python3.4）：
SQLAlchemy==1.0.14 sqlalchemy-redshift==0.5.0 psycopg2==2.6.2
SQLAlchemy==1.0.14 sqlalchemy-redshift==0.5.0 psycopg2==2.6.2
First of all, I checked, that my query is working workbench (http://www.sql-workbench.net), then I force it work in sqlalchemy (this https://stackoverflow.com/a/33438115/2837890helps to know that auto_commit or session.commit() must be):
首先，我检查了我的查询是工作台（http://www.sql-workbench.net），然后我强制它在 sqlalchemy 中工作（这个https://stackoverflow.com/a/33438115/2837890 有帮助要知道 auto_commit 或 session.commit() 必须是）：
db_credentials = (
'redshift+psycopg2://{p[redshift_user]}:{p[redshift_password]}@{p[redshift_host]}:{p[redshift_port]}/{p[redshift_database]}'
.format(p=config['Amazon_Redshift_parameters']))
engine = create_engine(db_credentials, connect_args={'sslmode': 'prefer'})
connection = engine.connect()
result = connection.execute(text(
"COPY assets FROM 's3://xx/xx/hello.csv' WITH CREDENTIALS "
"'aws_access_key_id=xxx_id;aws_secret_access_key=xxx'"
" FORMAT csv DELIMITER ',' IGNOREHEADER 1 ENCODING UTF8;").execution_options(autocommit=True))
result = connection.execute("select * from assets;")
print(result, type(result))
print(result.rowcount)
connection.close()
And after that, I forced to work sqlalchemy_redshiftCopyCommand perhaps bad way, looks little tricky:
在那之后，我被迫使用sqlalchemy_redshiftCopyCommand 可能是不好的方式，看起来有点棘手：
import sqlalchemy as sa
tbl2 = sa.Table(TableAssets, sa.MetaData())
copy = dialect_rs.CopyCommand(
assets,
data_location='s3://xx/xx/hello.csv',
access_key_id=access_key_id,
secret_access_key=secret_access_key,
truncate_columns=True,
delimiter=',',
format='CSV',
ignore_header=1,
# empty_as_null=True,
# blanks_as_null=True,
)
print(str(copy.compile(dialect=RedshiftDialect(), compile_kwargs={'literal_binds': True})))
print(dir(copy))
connection = engine.connect()
connection.execute(copy.execution_options(autocommit=True))
connection.close()
We make just that I made with sqlalchemy, excute query, except comine query by CopyCommand. I have not see some profit :(.
我们只是用 sqlalchemy 做的，执行查询，除了通过 CopyCommand 的 comine 查询。我没有看到一些利润:(。
回答by Jie
The following works for me with Databricks on all kinds of SQLs
以下适用于我在所有类型的 SQL 上使用 Databricks
import sqlalchemy as SA
import psycopg2
host = 'your_host_url'
username = 'your_user'
password = 'your_passw'
port = 5439
url = "{d}+{driver}://{u}:{p}@{h}:{port}/{db}".\
format(d="redshift",
driver='psycopg2',
u=username,
p=password,
h=host,
port=port,
db=db)
engine = SA.create_engine(url)
cnn = engine.connect()
strSQL = "your_SQL ..."
try:
cnn.execute(strSQL)
except:
raise
回答by Achilleus
import sqlalchemy as db
engine = db.create_engine('postgres://username:password@url:5439/db_name')
This worked for me
这对我有用
