Awesome thanks. Yeah -- I can confirm no issues at all integrating with Redshift.
I've created/deleted tables, copied in 5GB of data from s3, and executed several queries against it all through sqlalchemy with the psycopg2 dialect. (I've been working in raw SQL for 7 years, so I'm only using sqlalchemy.sql.text() statements at the moment if that is at all relevant). On Tuesday, February 26, 2013 5:26:00 PM UTC-5, Michael Bayer wrote: > > > On Feb 26, 2013, at 4:58 PM, Randy Shults <[email protected]<javascript:>> > wrote: > > Any reason this wouldnt work? > > (Disclaimer: Redshift is my first intro to postgre -- and I've never had a > reason to use sqlalchemy before -- so this could be a really dumb > question). > > Seems to connect and query fine...if this doesn't work -- please let me > know why: > > def get_conn(): > conn = psycopg2.connect(host="<host>.us-east-1.redshift.amazonaws.com", > database="<db_name>", port="5439", user="<user>", password="<pass>") > conn.autocommit = True > return conn > > engine = > sqlalchemy.create_engine("postgresql+psycopg2://",creator=get_conn) > > > if that works, then it works. We do a bunch of poking around on the DB > on the first connect but this would suggest redshift is OK with all of that. > > > > > On Sunday, February 17, 2013 10:08:40 AM UTC-5, Michael Bayer wrote: >> >> there's a ticket to add in "AUTOCOMMIT" at >> http://www.sqlalchemy.org/trac/ticket/2072 so if that's all it needs we >> should be in good shape. >> >> Though we probably need to make sure it happens immediately for Amazon, >> b.c. otherwise psycopg2 is going to try to set the "serializable" isolation >> level as soon as we do anything on the connection. There's generally a >> lot of ways to orchestrate these behaviors in a dialect, though. >> >> >> >> >> >> On Feb 16, 2013, at 2:58 PM, matt g <[email protected]> wrote: >> >> Update: >> I dropped down to psycopg2 and was finally able to get a query to go >> through doing the following: >> >> conn = psycopg2.connect(**options) >> >> conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT) >> cur = conn.cursor() >> cur.execute("SELECT COUNT(*) FROM users;") >> print cur.fetchone() >> cur.close() >> conn.close() >> >> I noticed that in SA there isn't a ISOLATION_LEVEL_AUTOCOMMIT in the >> isolation lookup in the PGDialect_psycopg2 dialect. Is the best way to >> go forard to create my own dialect? >> >> thanks, >> Matt >> >> >> On Saturday, February 16, 2013 1:12:20 PM UTC-6, matt g wrote: >>> >>> After seeing the announcement for Amazon Redshift yesterday I thought I >>> would see how hard it's going to be to connect from psycopyg/SqlAlchemy. So >>> far I'm not having any luck. Below is what I'm trying and the error that >>> follows: >>> >>> engine = create_engine(URL('postgres', **options), poolclass=NullPool, >>> execution_options={'no_parameters': True}) >>> conn = engine.connect() >>> >>> Traceback (most recent call last): >>> File "redshift.py", line 14, in <module> >>> conn = engine.connect() >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >>> >>> line 2472, in connect >>> return self._connection_cls(self, **kwargs) >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >>> >>> line 878, in __init__ >>> self.__connection = connection or engine.raw_connection() >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >>> >>> line 2558, in raw_connection >>> return self.pool.unique_connection() >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/pool.py", >>> line 183, in unique_connection >>> return _ConnectionFairy(self).checkout() >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/pool.py", >>> line 387, in __init__ >>> rec = self._connection_record = pool._do_get() >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/pool.py", >>> line 802, in _do_get >>> return self._create_connection() >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/pool.py", >>> line 188, in _create_connection >>> return _ConnectionRecord(self) >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/pool.py", >>> line 273, in __init__ >>> pool.dispatch.first_connect.exec_once(self.connection, self) >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/event.py", >>> line 282, in exec_once >>> self(*args, **kw) >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/event.py", >>> line 291, in __call__ >>> fn(*args, **kw) >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", >>> >>> line 167, in first_connect >>> dialect.initialize(c) >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py", >>> >>> line 1000, in initialize >>> super(PGDialect, self).initialize(connection) >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/engine/default.py", >>> >>> line 171, in initialize >>> self._get_server_version_info(connection) >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py", >>> >>> line 1184, in _get_server_version_info >>> v = connection.execute("select version()").scalar() >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >>> >>> line 1449, in execute >>> params) >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >>> >>> line 1628, in _execute_text >>> statement, parameters >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >>> >>> line 1698, in _execute_context >>> context) >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >>> >>> line 1685, in _execute_context >>> context) >>> File >>> "/Users/matt/envs/health/lib/python2.7/site-packages/sqlalchemy/engine/default.py", >>> >>> line 334, in do_execute_no_params >>> cursor.execute(statement) >>> sqlalchemy.exc.ProgrammingError: (ProgrammingError) permission denied to >>> set parameter "transaction_isolation" to "serializable" >>> 'select version()' {} >>> >>> Any ideas? >>> >>> thanks, >>> Matt >>> >>> >>> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To post to this group, send email to [email protected]. >> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. >> For more options, visit https://groups.google.com/groups/opt_out. >> >> >> >> >> > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected] <javascript:>. > To post to this group, send email to [email protected]<javascript:> > . > Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. > For more options, visit https://groups.google.com/groups/opt_out. > > > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
