On Feb 26, 2013, at 4:58 PM, Randy Shults <randy.c.shu...@gmail.com> 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 <mge...@gmail.com> 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 sqlalchemy+...@googlegroups.com. >> To post to this group, send email to sqlal...@googlegroups.com. >> 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy?hl=en. For more options, visit https://groups.google.com/groups/opt_out.