connecting to redshift

[matt g]

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

[matt g]

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

[Michael Bayer]

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.

--
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.
 
 

[Randy Shults]

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)

[Michael Bayer]

On Feb 26, 2013, at 4:58 PM, Randy Shults <randy.c...@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.

[Randy Shults]

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).