You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Request to AWS: provide a software engineer to support sqlalchemy-redshift #253
In looking at the README, I noticed that this project explicitly calls-out compatibility with sqlalchemy-redshift. However, the last AWS maintainer of this project, @Brooke-white, left the AWS Redshift team almost two years ago, and this repo has been left abandoned ever since. This seems like an oversight on the part of the AWS Redshift team, given how critical it is for Redshift's success to have a good set of Python tools for interacting with Redshift!
My request then is for the AWS employees on this repo to reach out to AWS Redshift management and either find employees to maintain the sqlalchemy-redshift package, or if this is not an AWS priority, to then archive sqlalchemy-redshift and remove it from this project's README file, preferably providing alternative supported methods for accessing Redshift from Python.
Thank you for your attention to this important matter!
-Matt
Reacted by Dave Parr, stefanks, raphaelauv, Clément, Nate LeMahieu , Paul Garner, jj.lee, lululombard, Benson Lindsey, Saud Al Saud and 12 more
This code would work, but it doesn't allow you to use metadata.create_all() to create table properly (because there's no dist key and sort key definition here), and also cannot use redshift specific data type. However, it works in 95% of use case. That's why I created https://github.com/MacHu-GWU/simple_aws_redshift-project to let it work with sqlalchemy and also does the authentication part of redshift-connector.
# -*- coding: utf-8 -*-"""Example: use sqlalchemy to work with AWS Redshift"""importsqlalchemyassaimportsqlalchemy.ormasormimportsqlalchemy.dialectsimportsqlalchemy.dialects.postgresql.psycopg2classRedshiftPostgresDialect(
sqlalchemy.dialects.postgresql.psycopg2.PGDialect_psycopg2,
):
# We need this to turn on statement caching# See warnning:## SAWarning: Dialect postgresql:psycopg2 will not make use of SQL compilation# caching as it does not set the 'supports_statement_cache' attribute to ``True``.# This can have significant performance implications including some# performance degradations in comparison to prior SQLAlchemy versions.# Dialect maintainers should seek to set this attribute to True after# appropriate development and testing for SQLAlchemy 1.4 caching support.# Alternatively, this attribute may be set to False which will disable this warning.# (Background on this warning at: https://sqlalche.me/e/20/cprf)supports_statement_cache=True# This method in the Base class has a line# ``std_string = connection.exec_driver_sql("show standard_conforming_strings").scalar()``# we override it to skip this line# See error:## sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedObject)# unrecognized configuration parameter "standard_conforming_strings"def_set_backslash_escapes(self, connection):
self._backslash_escapes="off"driver="redshift_custom"sqlalchemy.dialects.registry.register(
"redshift_custom", __name__, "RedshiftPostgresDialect"
)
defcreate_engine(
host: str,
port: int,
database: str,
username: str,
password: str,
test_connection: bool=True,
) ->sa.Engine:
url=f"{driver}://{username}:{password}@{host}:{port}/{database}"# print(url) # for debug onlyengine=sa.create_engine(url)
iftest_connection:
withengine.connect() asconn:
sql="SELECT 1;"stmt=sa.text(sql)
result=conn.execute(stmt).fetchall()
# print(result) # for debug onlyreturnengine# ------------------------------------------------------------------------------# ORM Model# ------------------------------------------------------------------------------classBase(orm.DeclarativeBase):
passclassDepartment(Base):
__tablename__="department"id: orm.Mapped[str] =orm.mapped_column(primary_key=True)
name: orm.Mapped[str] =orm.mapped_column(nullable=False, unique=True)
employees: orm.Mapped[list["Employee"]] =orm.relationship(
"Employee",
back_populates="department",
cascade="all, delete-orphan",
)
classEmployee(Base):
__tablename__="employee"id: orm.Mapped[str] =orm.mapped_column(primary_key=True)
name: orm.Mapped[str] =orm.mapped_column(nullable=False)
department_id: orm.Mapped[str] =orm.mapped_column(
sa.ForeignKey("department.id"),
nullable=False,
)
department: orm.Mapped[Department] =orm.relationship(
"Department",
back_populates="employees",
)
# create a view, which counts the number of employees in each department# sqlalchemy cannot map view for insert, only for querydepartment_summary=sa.Table(
"department_summary",
Base.metadata,
sa.Column("department_id", sa.String, primary_key=True),
sa.Column("employee_count", sa.Integer),
# 这里不需要 autoload=True,因为我们会手动创建 viewautoload_with=None,
)
defdrop_all(engine: sa.Engine):
withengine.connect() asconn:
sql="DROP VIEW IF EXISTS department_summary"stmt=sa.text(sql)
conn.execute(stmt)
conn.commit()
Base.metadata.drop_all(
engine,
tables=[
Department.__table__,
Employee.__table__,
],
checkfirst=True,
)
defcreate_all(engine: sa.Engine):
Base.metadata.create_all(
engine,
tables=[
Department.__table__,
Employee.__table__,
],
checkfirst=True,
)
withengine.connect() asconn:
sql="""CREATE OR REPLACE VIEW department_summary ASSELECT d.id AS department_id, COUNT(e.id) AS employee_countFROM department d LEFT JOIN employee e ON e.department_id = d.idGROUP BY d.id""".strip()
stmt=sa.text(sql)
conn.execute(stmt)
conn.commit()
definsert_sample_data(engine: sa.Engine):
withorm.Session(engine) asses:
hr=Department(id="dept-1", name="HR")
eng=Department(id="dept-2", name="Engineering")
ses.add_all([hr, eng])
ses.commit()
new_emps= [
{"id": "e-1", "name": "Alice", "department_id": hr.id},
{"id": "e-2", "name": "Bob", "department_id": hr.id},
{"id": "e-3", "name": "Carol", "department_id": eng.id},
]
foremp_datainnew_emps:
emp=Employee(**emp_data)
ses.add(emp)
ses.commit()
defselect_sample_data(engine: sa.Engine):
withorm.Session(engine) asses:
# query ORM modelsdepts=ses.query(Department).all()
fordindepts:
print(f"{d.name} employees: {[e.nameforeind.employees]}")
# query viewstmt=sa.select(
department_summary.c.department_id,
department_summary.c.employee_count,
).order_by(department_summary.c.department_id)
forrowinses.execute(stmt):
print(f"Dept {row.department_id} has {row.employee_count} employees")
defrun_demo(engine: sa.Engine):
metadata=sa.MetaData()
metadata.reflect(bind=engine)
fortableinmetadata.sorted_tables:
print(f"{table=}")
forcolintable.columns.values():
print(f" {col=}")
# drop_all(engine)# create_all(engine)# insert_sample_data(engine)# select_sample_data(engine)engine.connect()
if__name__=="__main__":
fromsimple_aws_redshift.apiimportRedshiftServerlessConnectionParamsfromsimple_aws_redshift.tests.settingsimportget_settingssettings=get_settings()
params=RedshiftServerlessConnectionParams.new(
redshift_serverless_client=settings.bsm.redshiftserverless_client,
namespace_name=settings.namespace_name,
workgroup_name=settings.workgroup_name,
)
host=params.hostport=params.portdatabase=params.databaseusername=settings.admin_usernamepassword=settings.admin_passwordengine=create_engine(
host=host,
port=port,
database=database,
username=username,
password=password,
test_connection=True,
)
run_demo(engine)
Activity
MacHu-GWU commentedon Jun 16, 2025
This code would work, but it doesn't allow you to use metadata.create_all() to create table properly (because there's no dist key and sort key definition here), and also cannot use redshift specific data type. However, it works in 95% of use case. That's why I created https://github.com/MacHu-GWU/simple_aws_redshift-project to let it work with sqlalchemy and also does the authentication part of redshift-connector.