[sqlalchemy] Read only connection with Table reflection

2007-08-29 Thread Jason Koelker 

I am attempting to use SQLAlchemy's table reflection to access a MSSQL
2k database.  However the user I can connect with only had
db_datareader access.  When SQLAlchemy tries to load the schema, it
attempts a rollback at the end, which the user does not have
permission to execute:

2007-08-29 11:32:24,400 INFO sqlalchemy.engine.base.Engine.0x..cL SET
nocount ON
2007-08-29 11:32:24,400 INFO sqlalchemy.engine.base.Engine.0x..cL None
2007-08-29 11:32:24,416 INFO sqlalchemy.engine.base.Engine.0x..cL
SELECT [COLUMNS_50b4].[TABLE_SCHEMA], [COLUMNS_50b4].[TABLE_NAME],
[COLUMNS_50b4].[COLUMN_NAME], [COLUMNS_50b4].[IS_NULLABLE],
[COLUMNS_50b4].[DATA_TYPE], [COLUMNS_50b4].[ORDINAL_POSITION],
[COLUMNS_50b4].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_50b4].
[NUMERIC_PRECISION], [COLUMNS_50b4].[NUMERIC_SCALE], [COLUMNS_50b4].
[COLUMN_DEFAULT]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_50b4]
WHERE [COLUMNS_50b4].[TABLE_NAME] = ? AND [COLUMNS_50b4].
[TABLE_SCHEMA] = ? ORDER BY [COLUMNS_50b4].[ORDINAL_POSITION]
2007-08-29 11:32:24,416 INFO sqlalchemy.engine.base.Engine.0x..cL
['Table', 'dbo']
2007-08-29 11:32:24,416 INFO sqlalchemy.engine.base.Engine.0x..cL
ROLLBACK

Traceback (most recent call last):
  File stdin, line 1, in module
  File /home/jkoelker/lib/python2.5/site-packages/Elixir-0.3.0-
py2.5.egg/elixir/entity.py, line 317, in __init__
desc.setup()
  File /home/jkoelker/lib/python2.5/site-packages/Elixir-0.3.0-
py2.5.egg/elixir/entity.py, line 110, in setup
self.setup_table()
  File /home/jkoelker/lib/python2.5/site-packages/Elixir-0.3.0-
py2.5.egg/elixir/entity.py, line 221, in setup_table
*args, **kwargs)
  File /home/jkoelker/lib/python2.5/site-packages/SQLAlchemy-0.3.10-
py2.5.egg/sqlalchemy/schema.py, line 175, in __call__
metadata._get_engine(raiseerr=True).reflecttable(table)
  File /home/jkoelker/lib/python2.5/site-packages/SQLAlchemy-0.3.10-
py2.5.egg/sqlalchemy/engine/base.py, line 806, in reflecttable
self.dialect.reflecttable(conn, table)
  File /home/jkoelker/lib/python2.5/site-packages/SQLAlchemy-0.3.10-
py2.5.egg/sqlalchemy/databases/mssql.py, line 495, in reflecttable
c = connection.execute(s)
  File /home/jkoelker/lib/python2.5/site-packages/SQLAlchemy-0.3.10-
py2.5.egg/sqlalchemy/engine/base.py, line 517, in execute
return Connection.executors[c](self, object, *multiparams,
**params)
  File /home/jkoelker/lib/python2.5/site-packages/SQLAlchemy-0.3.10-
py2.5.egg/sqlalchemy/engine/base.py, line 557, in
execute_clauseelement
return self.execute_compiled(elem.compile(dialect=self.dialect,
parameters=param), *multiparams, **params)
  File /home/jkoelker/lib/python2.5/site-packages/SQLAlchemy-0.3.10-
py2.5.egg/sqlalchemy/engine/base.py, line 568, in execute_compiled
self._execute_raw(context)
  File /home/jkoelker/lib/python2.5/site-packages/SQLAlchemy-0.3.10-
py2.5.egg/sqlalchemy/engine/base.py, line 581, in _execute_raw
self._execute(context)
  File /home/jkoelker/lib/python2.5/site-packages/SQLAlchemy-0.3.10-
py2.5.egg/sqlalchemy/engine/base.py, line 596, in _execute
self._autorollback()
  File /home/jkoelker/lib/python2.5/site-packages/SQLAlchemy-0.3.10-
py2.5.egg/sqlalchemy/engine/base.py, line 497, in _autorollback
self._rollback_impl()
  File /home/jkoelker/lib/python2.5/site-packages/SQLAlchemy-0.3.10-
py2.5.egg/sqlalchemy/engine/base.py, line 475, in _rollback_impl
raise exceptions.SQLError(None, None, e)
sqlalchemy.exceptions.SQLError: (Error) ('HY000', '[HY000] [FreeTDS]
[SQL Server]Could not perform COMMIT or ROLLBACK (0)') None None

Is there any way to use reflection with a read only connection?

Happy Hacking!

7-11


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Re: Read only connection with Table reflection

2007-08-29 Thread Jason Koelker 

On Aug 29, 7:27 pm, Michael Bayer [EMAIL PROTECTED] wrote:
 On Aug 29, 2007, at 1:55 PM, Jason Koelker wrote:

  I am attempting to use SQLAlchemy's table reflection to access a MSSQL
  2k database.  However the user I can connect with only had
  db_datareader access.  When SQLAlchemy tries to load the schema, it
  attempts a rollback at the end, which the user does not have
  permission to execute:

  Is there any way to use reflection with a read only connection?

 ive never heard of a DB connection where the user doesnt have
 ROLLBACK permission (considering any transaction is local to that
 user anyway !?).  theres no write operations occuring during a
 reflection otherwise.


Chalk that up to my inability to administrate windows/mssql. ;)

 While i'll accept tickets/patches to get a dont_issue_rollbacks
 create_engine() flag going, most expedient in your case would be to
 allow your user at least the ability to issue a ROLLBACK.

Roger, I'll figure out how to give just that permissions to the user.

On a side note I did have to change the way SQLAlchemy was passing
the port to the pyodbc library for FreeTDS to be happy.  Instead of:

if 'port' in keys:
connectors.append('Server=%s,%d' % (keys.get('host'),
keys.get('port')))
else:
connectors.append('Server=%s' % keys.get('host'))

What worked for me is:

if 'port' in keys:
connectors.append('Port=%d' % keys.get('port'))
connectors.append('Server=%s' % keys.get('host'))

I looked on technet it looks like the comma syntax is an ado/windows
thing.

Happy Hacking!

7-11


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---