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)
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] <javascript:>>
> 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] <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.
