On Feb 26, 2013, at 4:58 PM, Randy Shults <[email protected]> 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].
> 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].
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.
