Table creation/deletetion checkfirst parameter in mssql+pyodbc

[Michael Kvyatkovskiy]

Hello.

I'm using Microsoft Sql Server 2008, sqlalchemy 0.7.1.

When I'm trying to create table using metadata.create_all method with checkfirst=True, an axception is raised.
I set option convert_unicode=True in engine initialization. Here is the backtrace:


...
 File "...", line 32, in _dropTables
    checkfirst=True)
  File "C:\Programs\x64\python\lib\site-packages\sqlalchemy\schema.py", line 2442, in drop_all
    tables=tables)
  File "C:\Programs\x64\python\lib\site-packages\sqlalchemy\engine\base.py", line 2178, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "C:\Programs\x64\python\lib\site-packages\sqlalchemy\engine\base.py", line 1857, in _run_visitor
    **kwargs).traverse_single(element)
  File "C:\Programs\x64\python\lib\site-packages\sqlalchemy\sql\visitors.py", line 86, in traverse_single
    return meth(obj, **kw)
  File "C:\Programs\x64\python\lib\site-packages\sqlalchemy\engine\ddl.py", line 114, in visit_metadata
    if self._can_drop_table(t)]
  File "C:\Programs\x64\python\lib\site-packages\sqlalchemy\engine\ddl.py", line 137, in _can_drop_table
    table.name, schema=table.schema)
  File "C:\Programs\x64\python\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1141, in has_table
    c = connection.execute(s)
  File "C:\Programs\x64\python\lib\site-packages\sqlalchemy\engine\base.py", line 1358, in execute
    params)
  File "C:\Programs\x64\python\lib\site-packages\sqlalchemy\engine\base.py", line 1491, in _execute_clauseelement
    compiled_sql, distilled_params
  File "C:\Programs\x64\python\lib\site-packages\sqlalchemy\engine\base.py", line 1599, in _execute_context
    context)
  File "C:\Programs\x64\python\lib\site-packages\sqlalchemy\engine\base.py", line 1592, in _execute_context
    context)
  File "C:\Programs\x64\python\lib\site-packages\sqlalchemy\engine\default.py", line 325, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]The data types nvarchar and ntext are incompatible in the equal to operator. (402) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)') u'SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] \nFROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] \nWHERE [COLUMNS_1].[TABLE_NAME] = ? AND [COLUMNS_1].[TABLE_SCHEMA] = ?' (u'table_name', u'schema_name')


When I execute this query in SSMS, everything is OK:


SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME]
FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1]
WHERE [COLUMNS_1].[TABLE_NAME] = N'table_name' AND [COLUMNS_1].[TABLE_SCHEMA] = N'schema_name'

Is there a way to solve this problem?
Thank you.

[Michael Bayer]

On Jul 28, 2011, at 10:34 AM, Michael Kvyatkovskiy wrote:

> Hello.
>
> I'm using Microsoft Sql Server 2008, sqlalchemy 0.7.1.
>
> When I'm trying to create table using metadata.create_all method with checkfirst=True, an axception is raised.
> I set option convert_unicode=True in engine initialization. Here is the backtrace:

It appears you're running your script on windows so convert_unicode should not be needed, and it is in fact sending unicode strings directly as pyodbc supports this on windows.

the test case you'd like to try is (see http://code.google.com/p/pyodbc/wiki/ConnectionStrings for connect strings) :

import pyodbc
conn = pyodbc.connect("DRIVER={SQL Server};SERVER=cloak;DATABASE=test;UID=user;PWD=password")
cursor = conn.cursor()
cursor.execute("select * from [INFORMATION_SCHEMA].[COLUMNS] where [COLUMNS_1].[TABLE_NAME]=?", ' (u'table_name',))
print cursor.fetchall()

if this doesn't work, try removing the u'', however it really shouldn't matter. I'd ask on the Pyodbc list if the above statement is producing a problem.

[Michael Kvyatkovskiy]

Thank you for the response!

On 28 июл, 19:07, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Jul 28, 2011, at 10:34 AM, Michael Kvyatkovskiy wrote:
>

> the test case you'd like to try is (seehttp://code.google.com/p/pyodbc/wiki/ConnectionStringsfor connect strings) :

>
> import pyodbc
> conn = pyodbc.connect("DRIVER={SQL Server};SERVER=cloak;DATABASE=test;UID=user;PWD=password")
> cursor = conn.cursor()
> cursor.execute("select * from [INFORMATION_SCHEMA].[COLUMNS] where [COLUMNS_1].[TABLE_NAME]=?", ' (u'table_name',))
> print cursor.fetchall()
>
> if this doesn't work, try removing the u'', however it really shouldn't matter.  I'd ask on the Pyodbc list if the above statement is producing a problem.

Yes, this test case produces the same exception, regardless of the
presence of the u" symbol:

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server

Driver][SQL Server]The data types nvarchar and ntext are incompatible
in th
e equal to operator. (402) (SQLExecDirectW); [42000] [Microsoft][ODBC
SQL Server Driver][SQL Server]Statement(s) could not be prepared. (818
0)')

By the way, when I try to run unparameterized query like in the
statement below, it completes successfully:

cursor.execute("select * from [INFORMATION_SCHEMA].[COLUMNS] where

[COLUMNS_1].[TABLE_NAME]='table_name'")

[Michael Kvyatkovskiy]

Hello!

Is there any new information about my problem?

Thank you for your support!

[Michael Bayer]

you need to take your problem to the PyODBC tracker/mailing list.  The problem can be reproduced without using SQLAlchemy.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/FL6xRQr3ZzwJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.