MSSQL, pyodbc & linux
Christophe de VIENNE
I have checked out the trunk and am trying to run the unittests under
linux using pyodbc.
So far, the connection to the database is working, although I had to
name the freetds odbc driver "{SQL Server}", including the {}, so the
driver manager nows which one to use.
My problem is the following, and I have to idea where it comes from :
Any test from engine/bind fail with the following error (only the
query changes) :
DBAPIError: (ProgrammingError) ('42000', "[42000] [FreeTDS][SQL
Server]Could not find stored procedure 'S'. (2812); [42000]
[FreeTDS][SQL Server]Could not find stored procedure 'S'. (2812)")
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] \nFROM [INFORMATION_SCHEMA].[COLUMNS] AS
[COLUMNS_1] \nWHERE [COLUMNS_1].[TABLE_NAME] = ? AND
[COLUMNS_1].[TABLE_SCHEMA] = ?' ['test_table', 'dbo']
Any hint ?
Thanks
Christophe
Arnar Birgisson
I don't know the mssql parts of SA, but it looks like somewhere the
statement is being treated as a collection (statment[0] or similar) so
only the 'S' from 'SELECT...' is being executed. 'Could not find
stored procedure 'S'.' is exactly the error ms-sql gives if one
executes a statment with just an 'S'.
Arnar
Christophe de VIENNE
I found something :
Using ipython, I could reprodure the error, by calling a
[engine.connect].execute(q, r), when q is a... unicode string. writing
...execute(q.encode('utf-8'), r) solved the problem.
I am looking at pyodbc internals to see if the problem comes from there.
Christophe
Paul Johnston
Good find. For now, I suggest you edit mssql.py and change MSSQLDialect_pyodbc.supports_unicode_statements to return False. Or even something list "os.name == 'winnt'".
Longer term, if PyODBC can support unicode statements on Unix, that's amazing!
Paul
Christophe de VIENNE
> Chris,
>
> Good find. For now, I suggest you edit mssql.py and change
> MSSQLDialect_pyodbc.supports_unicode_statements to return
> False. Or even something list "os.name == 'winnt'".
Thanks, that will save me some time :-)
>
> Longer term, if PyODBC can support unicode statements on Unix, that's
> amazing!
Having a look at the implementation, the problem comes from that
SQLWCHAR, define in sqltypes.h is not wchar_t in some cases (not mine
at least).
This lead to a 2 bytes-wide characters string read like a simple char*
string. Then the statement sent to the server is only "S"...
To fix all this, one has to have a better understanding of how
[unix|i]ODBC handle unicode.
Regards,
Christophe
Christophe de VIENNE
> 2007/8/13, Paul Johnston <p...@pajhome.org.uk>:
> >
> > Longer term, if PyODBC can support unicode statements on Unix, that's
> > amazing!
>
> Having a look at the implementation, the problem comes from that
> SQLWCHAR, define in sqltypes.h is not wchar_t in some cases (not mine
> at least).
> This lead to a 2 bytes-wide characters string read like a simple char*
> string. Then the statement sent to the server is only "S"...
I had a closer look. On my platform, I have :
sizeof(wchar_t)=4, sizeof(SQLWCHAR)=2
In fact on linux most python are using UCS4 internally, while the odbc
implementations uses UCS2 (which is why they don't use wchar_t).
I don't know what is the cleaner way to convert back to UCS-2 before
calling SQLxxxW functions so I'll leave it here for now.
Lukasz Szybalski
<cdev...@gmail.com> wrote:
>
> Hi all,
>
> I have checked out the trunk and am trying to run the unittests under
> linux using pyodbc.
> So far, the connection to the database is working, although I had to
> name the freetds odbc driver "{SQL Server}", including the {}, so the
> driver manager nows which one to use.
Sorry if this thread is old, but could somebody give me few pointers here....
How does your connection sting looks like. I am trying to connect to
mssql via odbc with no luck. I am able to connect easly with
pyodbc,and ceodbc but not with sqlalchemy.
import sqlalchemy
import pyodbc
eng = sqlalchemy.create_engine("mssql:///?dsn=mydsn,UID=myusername,PWD=mypass",module=pyodbc)
eng.echo=True
metadata=sqlalchemy.BoundMetaData(eng)
I get
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/strategies.py",
line 57, in connect
raise exceptions.DBAPIError("Connection failed", e)
sqlalchemy.exceptions.DBAPIError: (Connection failed) (TypeError)
function takes at least 1 argument (0 given)
what is the proper statement to include usr and pwd in sqlalchemy?
("mssql:///?dsn=mydsn,UID=myusername,PWD=mypass",module=pyodbc) (does not work)
('mssql:///?dsn=mydsn;UID=myusername;PWD=mypass',module=pyodbc) (does not work)
but I can easily do this and it works:
import pyodbc
cnxn = pyodbc.connect("DSN=MYDBNAME;UID=USERNAME;PWD=PASSWORD")
cursor = cnxn.cursor()
cursor.execute('select * from mytable')
a=cursor.fetchall()
print 'pyodbc',a
Thanks,
Lucas
Paul Johnston
eng = sqlalchemy.create_engine("mssql:///?dsn=mydsn,UID=myusername,PWD=mypass",module=pyodbc)
eng = sqlalchemy.create_engine("mssql://myusername:mypass@/?dsn=mydsn",module=pyodbc)
Paul
Lukasz Szybalski
Still the same error.
File "/usr/lib/python2.4/site-packages/sqlalchemy/pool.py", line 111,
in create_connection
return _ConnectionRecord(self)
File "/usr/lib/python2.4/site-packages/sqlalchemy/pool.py", line
149, in __init__
self.connection = self.__connect()
File "/usr/lib/python2.4/site-packages/sqlalchemy/pool.py", line
174, in __connect
connection = self.__pool._creator()
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/strategies.py",
line 57, in connect
raise exceptions.DBAPIError("Connection failed", e)
sqlalchemy.exceptions.DBAPIError: (Connection failed) (TypeError)
function takes at least 1 argument (0 given)
Lucas
Randall Smith
You shouldn't need to define a dsn. This "should" work:
e = sa.create_engine('mssql://user:pass@localhost:1433/tempdb')
This actually doesn't work (for me), but it's something minor. For my
setup using unixodbc and tdsodbc, you can connect using pyodbc like so:
pyodbc.connect('DRIVER={SQL
Server};Server=localhost;Database=tempdb;UID=user;PWD=pass')
but, SA is producing this:
pyodbc.connect('Driver={SQL
Server};Server=localhost;Database=tempdb;UID=user;PWD=pass')
which fails.
Can you see this difference? It's the case of Driver. Apparently
Server and Database are not case sensitive, but Driver is. I don't know
what software is buggy yet. If you do know, please respond.
I plan to be extensively using and testing Linux/ODBC/pyodbc/SA, so I'd
be happy to work with any exiting effort to improve SA's SQL Server support.
Randall
Rick Morrison
Can you see this difference? It's the case of Driver. Apparently
Server and Database are not case sensitive, but Driver is.
Thanks for the report. I'll try a quick hack later today to see if the uppercase string works on Windows as well; we can knock this one out easily.
I plan to be extensively using and testing Linux/ODBC/pyodbc/SA, so I'd
be happy to work with any exiting effort to improve SA's SQL Server support.
That's great news, and we're glad for the help. What version of MSSQL will you be working with?
Rick
Randall Smith
> That's great news, and we're glad for the help. What version of MSSQL
> will you be working with?
2005 currently. I hope to get access to other versions though.
Randall
Rick Morrison
> I'll try a quick hack later today to see if the uppercase string works on Windows as well; we can knock this one out easily.
Windows seems to be fine with the uppercase string; that's committed in r4379.
Lukasz Szybalski
>
> Paul Johnston wrote:
> > Hi,
> >
> > eng =
> > sqlalchemy.create_engine("mssql:///?dsn=mydsn,UID=myusername,PWD=mypass",module=pyodbc)
> >
> >
> > Try this:
> > eng =
> > sqlalchemy.create_engine("mssql://myusername:mypass@/?dsn=mydsn",module=pyodbc)
> >
> > Paul
>
> You shouldn't need to define a dsn. This "should" work:
>
> e = sa.create_engine('mssql://user:pass@localhost:1433/tempdb')
>
So what you are saying here is that sqlalchemy will figure out what
driver to use? pyodbc or other?
I know in 0.3 version I would get:
File "/usr/lib/python2.4/site-packages/sqlalchemy/engine/strategies.py",
line 52, in create
raise exceptions.InvalidRequestError("Cant get DBAPI module for
dialect '%s'" % dialect)
sqlalchemy.exceptions.InvalidRequestError: Cant get DBAPI module for
dialect '<sqlalchemy.databases.mssql.MSSQLDialect object at
0xb7807dac>'
I have tried this with svn version and I get:
>>> pm = sqlalchemy.Table('17000Z', metadata, autoload=True)
Traceback (most recent call last):
File "<stdin>", line 1, in ?
File "sqlalchemy/schema.py", line 110, in __call__
return type.__call__(self, name, metadata, *args, **kwargs)
File "sqlalchemy/schema.py", line 226, in __init__
_bind_or_error(metadata).reflecttable(self,
include_columns=include_columns) File "sqlalchemy/engine/base.py",
line 1271, in reflecttable
conn = self.contextual_connect()
File "sqlalchemy/engine/base.py", line 1239, in contextual_connect
return Connection(self, self.pool.connect(),
close_with_result=close_with_result, **kwargs)
File "sqlalchemy/pool.py", line 178, in connect
agent = _ConnectionFairy(self)
File "sqlalchemy/pool.py", line 321, in __init__
rec = self._connection_record = pool.get()
File "sqlalchemy/pool.py", line 188, in get
return self.do_get()
File "sqlalchemy/pool.py", line 613, in do_get
con = self.create_connection()
File "sqlalchemy/pool.py", line 153, in create_connection
return _ConnectionRecord(self)
File "sqlalchemy/pool.py", line 216, in __init__
self.connection = self.__connect()
File "sqlalchemy/pool.py", line 279, in __connect
connection = self.__pool._creator()
File "sqlalchemy/engine/strategies.py", line 80, in connect
raise exceptions.DBAPIError.instance(None, None, e)
SystemError: 'finally' pops bad exception
Lucas
Rick Morrison
Sort of. Attempts are made to import appropriate modules until one doesn't fail. The order is: [pyodbc, mssql, adodbapi]
> I have tried this with svn version and I get
I've tried it with Windows, and the normal DB-URL syntax works. The traceback you give suggests some kind of pydodbc error, but doesn't seem to provide any details. If you're on Linux, I would suspect your FreeTDS / unixodbc / iodbc setup. What sort of error you get when trying to connect using pyodbc without SQLAlchemy?
Lukasz Szybalski
I usually use dsn and pyodbc. If I use the command Randall emailed:
>>> pyodbc.connect('DRIVER={SQL
Server};Server=localhost;UID=user;PWD=pass') Traceback (most recent
call last):
File "<stdin>", line 1, in ?
<type 'instance'>: ('IM002', '[IM002] [unixODBC][Driver Manager]Data
source name not found, and no default driver specified (0)')
>>> pyodbc.connect('DRIVER={TDS};Server=localhost;UID=user;PWD=pass')
<pyodbc.Connection object at 0xb7d0b1e0>
>>> a=pyodbc.connect('DRIVER={TDS};Server=localhost;UID=user;PWD=pass')
>>> cursor=a.cursor()
>>> cursor.execute('select * from 17000Z')
<pyodbc.Cursor object at 0xb7d02db0>
>>> b=cursor.fetchall()
As you can see I can connect via pyodbc if I pick TDS as a driver. Is
there a way to tell sqlalchemy ti use TDS driver?
e = sa.create_engine('mssql://user:pass@localhost:1433/tempdb')
(DRIVER={TDS}
Lucas
Rick Morrison
Unixodbc drivers are here:
http://www.unixodbc.org/drivers.html
I'm not really up on my unixodbc config, so I don't know where or how the driver names are configured.
> As you can see I can connect via pyodbc if I pick TDS as a driver. Is
I've added a keyword parameter 'driver' to the MSSQL/pyodbc dialect in r4386. Will substitute in the ODBC connection string, defaults to 'SQL Server'. Give that a try if you can't otherwise resolve the naming issue.
Rick
Randall Smith
I'm not certain about this, but it may be the way it's defined in
/etc/odbcinst.ini. Mine looks like this:
[SQL Server]
Description = TDS driver (Sybase/MS SQL)
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
CPTimeout =
CPReuse =
Hope that works for you.
Randall
Randall Smith
>
> e = sa.create_engine('mssql://user:pass@localhost:1433/tempdb')
>
In case someone picks up on SQL Server running on localhost on Linux,
I'm forwarding port 1433 to a Windows host in this example.
WinXP on Qemu with user-net and tcp redirect (1433) with SQL Server
Express installed. It's a decent way to test for a Linux junkie.
Randall
Lukasz Szybalski
You didn't say how am I supposed to use it in create engine?
What will be create_engine that is equivelent to:
pyodbc.connect('DRIVER={TDS};Server=localhost;UID=user;PWD=pass')
Like this:
sqlalchemy.create_engine('mssql://user:pass@localhost:1433/?driver=TDS')????
Anyway. I went into sqlalchemy/databases/mssql.py
changed the line 791 to: self.drivername = params.get('driver', 'TDS')
but I still get the:
Traceback (most recent call last):
File "<stdin>", line 1, in ?
Rick Morrison
You didn't say how am I supposed to use it in create engine?
Anyway. I went into sqlalchemy/databases/mssql.py
changed the line 791 to: self.drivername = params.get('driver', 'TDS')
but I still get the:
OK, continuing, then try adding on line 826 (right before the function return) this statement
print ';'.join(connectors)
Re-run your program, note the string output, and try using that connection string to connect via raw pyodbc, and let's see that error message.
Lukasz Szybalski
>
> >
> >
> >
> > You didn't say how am I supposed to use it in create engine?
> >
>
> http://www.sqlalchemy.org/docs/04/dbengine.html#dbengine_options
>
>
> >
> > Anyway. I went into sqlalchemy/databases/mssql.py
> > changed the line 791 to: self.drivername = params.get('driver', 'TDS')
> > but I still get the:
> >
> >
>
> OK, continuing, then try adding on line 826 (right before the function
> return) this statement
>
> print ';'.join(connectors)
>
Here is what I get.
The problem seems to be in a port part of a connection.
Here is what your print statement shows:
DRIVER={SQL Server};Server=xxxx,1433;Database=xxx;UID=xxx;PWD=xxx
>>> e.echo=True
>>> metadata=sqlalchemy.MetaData(e)
>>> pm = sqlalchemy.Table('xxxx', metadata, autoload=True)
File "sqlalchemy/engine/strategies.py", line 80, in connect
raise exceptions.DBAPIError.instance(None, None, e)
SystemError: 'finally' pops bad exception
import pyodbc
c=pyodbc.connect('DRIVER={SQL
Server};Server=xxx,1433;Database=xxx;UID=xxx;PWD=xxx')
Traceback (most recent call last):
File "<stdin>", line 1, in ?
<type 'instance'>: ('08001', '[08001] [unixODBC][FreeTDS][SQL
Server]Unable to connect to data source (0)')
I also tried
>>> c=pyodbc.connect("DRIVER={SQL
Server};Server=xxx:1433;Database=xxx;UID=xxx;PWD=xx")
Traceback (most recent call last):
File "<stdin>", line 1, in ?
<type 'instance'>: ('08001', '[08001] [unixODBC][FreeTDS][SQL
Server]Unable to connect to data source (0)')
Finally I tried:
>>> c=pyodbc.connect("DRIVER={SQL
Server};Server=xxx;Database=xxx;UID=xxx;PWD=xx")
I am able to connect.
So I modified line 816:
connectors.append('Server=%s,%d' % (keys.get('host'), keys.get('port')))
to
connectors.append('Server=%s' % (keys.get('host')))
and I am able to connect to mssql on unixodbc via sqlalchemy.
I tried looking at pyodbc page to find proper way to include port, but
I couldn't find one.
Lucas
Rick Morrison
...would you please try this format on line 816:
connectors.append('Server=%s;Port=%d' % (keys.get('host'), keys.get('port')))
...I'll try it on Windows here.
Rick
Rick Morrison
...I'll try it on Windows here.
Update: it seems to work fine on Windows.
Lukasz Szybalski
> I think I remember a "Port=" in the ODBC connection string.
>
> ...would you please try this format on line 816:
>
> connectors.append('Server=%s;Port=%d' % (keys.get('host'),
> keys.get('port')))
You are correct:
Port=
fixes it.
If you guys could fix this in svn, and point me to some instructions
on how to install sqlalchemy in virtual / home directory that would be
great.
Lucas
Rick Morrison
You are correct:
Port=
fixes it.
If you guys could fix this in svn, and point me to some instructions
on how to install sqlalchemy in virtual / home directory that would be
great.
OK, great, then r4387 rolls up everything covered in this thread and works on Windows: please give it a try on Linux.
The only virtual environment I've worked with is virtualenv - download and docs at http://pypi.python.org/pypi/virtualenv
Read over the docs, I think you'll find it's pretty straightforward.
Rick
Lucas
Lukasz Szybalski
>
> >
> >
> > You are correct:
> > Port=
> > fixes it.
> >
> >
> > If you guys could fix this in svn, and point me to some instructions
> > on how to install sqlalchemy in virtual / home directory that would be
> > great.
> >
> >
> >
> >
>
>
> OK, great, then r4387 rolls up everything covered in this thread and works
> on Windows: please give it a try on Linux.
Yes it does work.
The last 2 issue in this thread are driver name, and documentation update.
1. "SQL Server" might be a default in windows, but in linux all
howto/docs use a default name which is either TDS or FREETDS. This
means either sqlalchemy documentation will have to tells unixodbc
users to change their driver name like:
"
[SQL Server]
Description = FreeTDS Driver for Linux & MSSQL on Win32
Driver = /usr/lib/odbc/libtdsodbc.so
Setup = /usr/lib/odbc/libtdsS.so
"
or sqlalchemy connection string will have to provide some way to add a
driver name to connection string.
sqlalchemy.create_engine('mssql://user:pass@localhost:1433/?driver=TDS')
I would prefer a second way because on linux a user might not be an
administrator and might not have privileges to change drivers name,
and all dsn that use that driver.
2. Could you add unixODBC instructions to sqlalchemy docs, namely:
a)Required: pyodbc, unixodbc, tdsodbc
b)sqlalchemy.create_engine('mssql://user:pass@localhost:1433/?driver=TDS')
Thanks you.
Lucas
Rick Morrison
or sqlalchemy connection string will have to provide some way to add a
driver name to connection string.
sqlalchemy.create_engine('mssql://user:pass@localhost:1433/?driver=TDS')
That capability, with that very keyword, is in SVN trunk, part of the last round of changes. BTW, this string is malformed: it's missing a database name.
2. Could you add unixODBC instructions to sqlalchemy docs, namely:
a)Required: pyodbc, unixodbc, tdsodbc
b)sqlalchemy.create_engine('mssql://user:pass@localhost:1433/?driver=TDS')
It would be better if you yourself would submit some text that you'd like to see here. After just having gone through the configuration procedure, you are just as qualified as anyone to describe the process. I'd be happy to review anything submitted.
Rick
Lukasz Szybalski
Under:
http://www.sqlalchemy.org/docs/04/documentation.html#dbengine_establishing
after:
"# oracle will feed host/port/SID into cx_oracle.makedsn
oracle_db = create_engine('oracle://scott:ti...@127.0.0.1:1521/sidname')"
add:
#mssql connection string. (On Linux using unixODBC you will have to
provide a driver name, on Windows driver name defaults to 'SQL Server'
)
mssql_db=create_engine('mssql://user:pass@hostname:1433/mydatabase?driver=TDS')
I would also fix the mysql line from:
# mysql
mysql_db = create_engine('mysql://localhost/foo')
to
# mysql connection string
mysql_db = create_engine('mysql://user:pass@hostname/mydatabase')
Lucas
Lukasz Szybalski
I guess my last question is how do I use sqlalchemy now? Is there a
release that is coming out soon that would include unixodbc support or
should I use svn? If I use svn how do I know it works?
Should I wait until these changes are in some branch? and chek out that branch?
Lucas
Rick Morrison
> Under:
> http://www.sqlalchemy.org/docs/04/documentation.html#dbengine_establishing
> <snip changes>
Thanks, I'll add these.
I guess my last question is how do I use sqlalchemy now? Is there a
release that is coming out soon that would include unixodbc support or
should I use svn? If I use svn how do I know it works?
Use SVN for now, 0.4.5 will be the next release. Mike can speak to timing better than I can, but I get the feeling that it's a rather "heavy" release and is not imminent - although I did see a merge to trunk just yesterday.
As far as unixodbc/iodbc support, it's a situation that it still developing, you are a bit on the leading edge here. It's mostly a matter of getting unit tests to pass in that environment to ensure continued quality going forward, something I don't have a lot of time for these days, help is as always, very welcome.
Should I wait until these changes are in some branch? and chek out that branch?
My guess is that there will be no 0.4.5 branch, and trunk will be where you want to stay until 0.4.5.
Paul Johnston
>Under:
>http://www.sqlalchemy.org/docs/04/documentation.html#dbengine_establishing
>after:
>"# oracle will feed host/port/SID into cx_oracle.makedsn
>oracle_db = create_engine('oracle://scott:ti...@127.0.0.1:1521/sidname')"
>
>
It's great to see you getting MSSQL to work on Unix. I know someone else
tried this and had some good success, the unit tests ran nearly as
cleanly as on Windows. As for the connection info, I suggest you add it
to http://www.sqlalchemy.org/trac/wiki/DatabaseNotes initially (it's a
wiki) - if this is useful to a lot of people it may make its way to the
official docs.
Paul
Lukasz Szybalski
> you are a bit on the leading edge here. It's mostly a matter of getting unit
> tests to pass in that environment to ensure continued quality going forward,
Unfortunately I am working with existing sql server which runs in
production. I can get data out of it but I can not use it as a
developing server. I don't have any other sql server running at this
time so I can't really do unit tests.
I'll try to see if I have a copy somewhere and install it on my laptop
but that might take a while.
Lucas