Hi,
I am a SQLAlchemy newbie trying to get started with an existing SQL
Server database. I am running on WinXP and using adodbapi to access the
DB. I am trying to use autoload to make a Table corresponding to one of
my DB tables.
The first problem I have is that my DB has NVARCHAR fields which
SQLAlchemy doesn't recognize. I get this error:
Traceback (most recent call last):
File "D:\Projects\CB\(misc)\Scripts\CbAlchemy.py", line 51, in ?
course_table = Table('COURSE', metadata, autoload=True,
schema='cbowner')
File "c:\downloads\python\sqlalchemy-0.2.8\lib\sqlalchemy\schema.py",
line 138, in __call__
metadata.engine.reflecttable(table)
File
"c:\downloads\python\sqlalchemy-0.2.8\lib\sqlalchemy\engine\base.py",
line 491, in reflecttable
self.dialect.reflecttable(conn, table)
File
"c:\downloads\python\sqlalchemy-0.2.8\lib\sqlalchemy\databases\mssql.py",
line 409, in reflecttable
coltype = ischema_names[type]
KeyError: u'nvarchar'
I added a simple MSUnicode type to mssql.py, basically copying what was
done for MSString and changing String to Unicode, varchar to nvarchar.
Is this a reasonable fix? Why is NVARCHAR not supported in the distribution?
Now it gets a little farther but I have this error:
Traceback (most recent call last):
File "D:\Projects\CB\(misc)\Scripts\CbAlchemy.py", line 51, in ?
course_table = Table('COURSE', metadata, autoload=True,
schema='cbowner')
File "c:\downloads\python\sqlalchemy-0.2.8\lib\sqlalchemy\schema.py",
line 138, in __call__
metadata.engine.reflecttable(table)
File
"c:\downloads\python\sqlalchemy-0.2.8\lib\sqlalchemy\engine\base.py",
line 491, in reflecttable
self.dialect.reflecttable(conn, table)
File
"c:\downloads\python\sqlalchemy-0.2.8\lib\sqlalchemy\databases\mssql.py",
line 452, in reflecttable
c = connection.execute(s)
File
"c:\downloads\python\sqlalchemy-0.2.8\lib\sqlalchemy\engine\base.py",
line 246, in execute
return Connection.executors[type(object).__mro__[-2]](self, object,
*multiparams, **params)
File
"c:\downloads\python\sqlalchemy-0.2.8\lib\sqlalchemy\engine\base.py",
line 270, in execute_clauseelement
return self.execute_compiled(elem.compile(engine=self.__engine,
parameters=param), *multiparams, **params)
File
"c:\downloads\python\sqlalchemy-0.2.8\lib\sqlalchemy\engine\base.py",
line 286, in execute_compiled
proxy(str(compiled), parameters)
File
"c:\downloads\python\sqlalchemy-0.2.8\lib\sqlalchemy\engine\base.py",
line 282, in proxy
self._execute_raw(statement, parameters, cursor=cursor,
context=context)
File
"c:\downloads\python\sqlalchemy-0.2.8\lib\sqlalchemy\engine\base.py",
line 321, in _execute_raw
self._execute(cursor, statement, parameters, context=context)
File
"c:\downloads\python\sqlalchemy-0.2.8\lib\sqlalchemy\engine\base.py",
line 340, in _execute
raise exceptions.SQLError(statement, parameters, e)
sqlalchemy.exceptions.SQLError: (DatabaseError)
-----------
Strategy 1: Traceback:Traceback (most recent call last):
File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 540,
in executeHelper
adoRetVal=self.cmd.Execute()
File
"C:\Python24\lib\site-packages\win32com\gen_py\EF53050B-882E-4776-B643-EDA472E8E3F2x0x2x7\_Command.py",
line 43, in Execute
, Parameters, Options)
File "C:\Python24\Lib\site-packages\win32com\client\__init__.py",
line 446, in _ApplyTypes_
return self._get_good_object_(
com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB
Provider for SQL Server', 'Cannot create new connection because in
manual or distributed transaction mode.', None, 0, -2147467259), None)
-----------
Strategy 2: Traceback:Traceback (most recent call last):
File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 540,
in executeHelper
adoRetVal=self.cmd.Execute()
File
"C:\Python24\lib\site-packages\win32com\gen_py\EF53050B-882E-4776-B643-EDA472E8E3F2x0x2x7\_Command.py",
line 43, in Execute
, Parameters, Options)
File "C:\Python24\Lib\site-packages\win32com\client\__init__.py",
line 446, in _ApplyTypes_
return self._get_good_object_(
com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB
Provider for SQL Server', 'Cannot create new connection because in
manual or distributed transaction mode.', None, 0, -2147467259), None)
-----------
Strategy 3: Traceback:Traceback (most recent call last):
File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 540,
in executeHelper
adoRetVal=self.cmd.Execute()
File
"C:\Python24\lib\site-packages\win32com\gen_py\EF53050B-882E-4776-B643-EDA472E8E3F2x0x2x7\_Command.py",
line 43, in Execute
, Parameters, Options)
File "C:\Python24\Lib\site-packages\win32com\client\__init__.py",
line 446, in _ApplyTypes_
return self._get_good_object_(
com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB
Provider for SQL Server', 'Cannot create new connection because in
manual or distributed transaction mode.', None, 0, -2147467259), None)
-----------
Strategy 4: Traceback:Traceback (most recent call last):
File "C:\Python24\lib\site-packages\adodbapi\adodbapi.py", line 540,
in executeHelper
adoRetVal=self.cmd.Execute()
File
"C:\Python24\lib\site-packages\win32com\gen_py\EF53050B-882E-4776-B643-EDA472E8E3F2x0x2x7\_Command.py",
line 43, in Execute
, Parameters, Options)
File "C:\Python24\Lib\site-packages\win32com\client\__init__.py",
line 446, in _ApplyTypes_
return self._get_good_object_(
com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB
Provider for SQL Server', 'Cannot create new connection because in
manual or distributed transaction mode.', None, 0, -2147467259), None)
--- ADODBAPI on command:SELECT table_constrain_fd87.constraint_type AS
table_constraints_constr_7c46, C.table_schema AS C_table_schema,
C.table_name AS C_table_name, C.column_name AS C_column_name,
R.table_schema AS R_table_schema, R.table_name AS R_table_name,
R.column_name AS R_column_name
FROM information_schema.table_constraints AS table_constrain_fd87 JOIN
information_schema.referential_constraints AS referential_con_3de7 ON
referential_con_3de7.constraint_name =
table_constrain_fd87.constraint_name JOIN
information_schema.constraint_column_usage AS C ON C.constraint_name =
referential_con_3de7.constraint_name JOIN
information_schema.constraint_column_usage AS R ON R.constraint_name =
referential_con_3de7.unique_constraint_name
WHERE referential_con_3de7.constraint_schema = ? AND C.table_name = ?
with parameters: ['cbowner', 'COURSE'] 'SELECT
table_constrain_fd87.constraint_type AS table_constraints_constr_7c46,
C.table_schema AS C_table_schema, C.table_name AS C_table_name,
C.column_name AS C_column_name, R.table_schema AS R_table_schema,
R.table_name AS R_table_name, R.column_name AS R_column_name \nFROM
information_schema.table_constraints AS table_constrain_fd87 JOIN
information_schema.referential_constraints AS referential_con_3de7 ON
referential_con_3de7.constraint_name =
table_constrain_fd87.constraint_name JOIN
information_schema.constraint_column_usage AS C ON C.constraint_name =
referential_con_3de7.constraint_name JOIN
information_schema.constraint_column_usage AS R ON R.constraint_name =
referential_con_3de7.unique_constraint_name \nWHERE
referential_con_3de7.constraint_schema = ? AND C.table_name = ?'
['cbowner', 'COURSE']
Tool completed with exit code 1
If I define the Table columns myself it seems to work, I can do a simple
select at least. So this is not a showstopper but it would be nice if
the autoload worked. Here is the connection string I use:
db = create_engine('mssql://cbowner:[EMAIL PROTECTED]/CurriculumBuilder')
I would be interested in general comments about the state of MS SQL
support in SQLAlchemy; is it pretty solid or is it still gelling?
Thanks,
Kent
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
