[sqlalchemy] Re: MSSQL, pyodbc & linux
> 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, 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 --~--~-~--~~~---~--~~ 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: MSSQL, pyodbc & linux
Hi Lukasz,
>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:[EMAIL PROTECTED]: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
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
> > > Under: > > > http://www.sqlalchemy.org/docs/04/documentation.html#dbengine_establishing > > > > 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. Rick --~--~-~--~~~---~--~~ 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: MSSQL, pyodbc & linux
On Tue, Apr 1, 2008 at 10:09 AM, Lukasz Szybalski <[EMAIL PROTECTED]> wrote:
> On Tue, Apr 1, 2008 at 9:26 AM, Rick Morrison <[EMAIL PROTECTED]> wrote:
> >
> >
> > > or sqlalchemy connection string will have to provide some way to add a
> > > driver name to connection string.
> > >
> > >
> > > sqlalchemy.create_engine('mssql://user:[EMAIL
> PROTECTED]: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:[EMAIL
> PROTECTED]: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.
>
> 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:[EMAIL PROTECTED]: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:[EMAIL
> PROTECTED]: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:[EMAIL PROTECTED]/mydatabase')
>
> Lucas
>
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
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
On Tue, Apr 1, 2008 at 9:26 AM, Rick Morrison <[EMAIL PROTECTED]> wrote:
>
>
> > or sqlalchemy connection string will have to provide some way to add a
> > driver name to connection string.
> >
> >
> > sqlalchemy.create_engine('mssql://user:[EMAIL PROTECTED]: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:[EMAIL
> > PROTECTED]: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.
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:[EMAIL PROTECTED]: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:[EMAIL
PROTECTED]: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:[EMAIL PROTECTED]/mydatabase')
Lucas
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
> or sqlalchemy connection string will have to provide some way to add a
> driver name to connection string.
>
> sqlalchemy.create_engine('mssql://user:[EMAIL PROTECTED]: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:[EMAIL PROTECTED]: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
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
On Mon, Mar 31, 2008 at 4:57 PM, Rick Morrison <[EMAIL PROTECTED]> wrote:
>
> >
> >
> > 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:[EMAIL PROTECTED]: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:[EMAIL PROTECTED]:1433/?driver=TDS')
Thanks you.
Lucas
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
> > > 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 > > > > --~--~-~--~~~---~--~~ 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: MSSQL, pyodbc & linux
On Mon, Mar 31, 2008 at 3:50 PM, Rick Morrison <[EMAIL PROTECTED]> wrote:
> 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
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
> > > ...I'll try it on Windows here. > Update: it seems to work fine on Windows. --~--~-~--~~~---~--~~ 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: MSSQL, pyodbc & linux
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')))
...I'll try it on Windows here.
Rick
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
On Mon, Mar 31, 2008 at 2:07 PM, Rick Morrison <[EMAIL PROTECTED]> wrote:
>
> >
> >
> >
> > 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=,1433;Database=xxx;UID=xxx;PWD=xxx
>>> e.echo=True
>>> metadata=sqlalchemy.MetaData(e)
>>> pm = sqlalchemy.Table('', 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 "", line 1, in ?
: ('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 "", line 1, in ?
: ('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
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
>
>
> 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)
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.
>
> Traceback (most recent call last):
> File "", 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
>
> >
>
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
On Mon, Mar 31, 2008 at 12:21 PM, Rick Morrison <[EMAIL PROTECTED]> wrote:
> It looks to me as if you have unixodbc installed, but don't have a SQL
> Server driver installed, or it's named differently.
>
> 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
>
> > there a way to tell sqlalchemy to use TDS driver?
>
> 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.
>
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:[EMAIL PROTECTED]: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 "", 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
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
Randall Smith wrote:
>
> e = sa.create_engine('mssql://user:[EMAIL PROTECTED]: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
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
Lukasz Szybalski wrote:
> On Mon, Mar 31, 2008 at 9:53 AM, Rick Morrison <[EMAIL PROTECTED]> wrote:
>>> So what you are saying here is that sqlalchemy will figure out what driver
>> to use? pyodbc or other?
>>
>>
>> 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?
>>
>
> 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 "", line 1, in ?
> : ('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')
>
a=pyodbc.connect('DRIVER={TDS};Server=localhost;UID=user;PWD=pass')
cursor=a.cursor()
cursor.execute('select * from 17000Z')
>
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:[EMAIL PROTECTED]:1433/tempdb')
>
> (DRIVER={TDS}
>
> Lucas
>
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
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
It looks to me as if you have unixodbc installed, but don't have a SQL Server driver installed, or it's named differently. 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 > there a way to tell sqlalchemy to use TDS driver? 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 --~--~-~--~~~---~--~~ 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: MSSQL, pyodbc & linux
On Mon, Mar 31, 2008 at 9:53 AM, Rick Morrison <[EMAIL PROTECTED]> wrote:
>
> > So what you are saying here is that sqlalchemy will figure out what driver
> to use? pyodbc or other?
>
>
> 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?
>
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 "", line 1, in ?
: ('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')
>>> a=pyodbc.connect('DRIVER={TDS};Server=localhost;UID=user;PWD=pass')
>>> cursor=a.cursor()
>>> cursor.execute('select * from 17000Z')
>>> 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:[EMAIL PROTECTED]:1433/tempdb')
(DRIVER={TDS}
Lucas
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
> So what you are saying here is that sqlalchemy will figure out what driver to use? pyodbc or other? 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? Rick --~--~-~--~~~---~--~~ 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: MSSQL, pyodbc & linux
On Sat, Mar 29, 2008 at 10:50 PM, Randall Smith <[EMAIL PROTECTED]> wrote:
>
> 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:[EMAIL PROTECTED]: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 ''
I have tried this with svn version and I get:
>>> pm = sqlalchemy.Table('17000Z', metadata, autoload=True)
Traceback (most recent call last):
File "", 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
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
> > > 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. --~--~-~--~~~---~--~~ 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: MSSQL, pyodbc & linux
Rick Morrison wrote: > 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 --~--~-~--~~~---~--~~ 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: MSSQL, pyodbc & linux
> 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 --~--~-~--~~~---~--~~ 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: MSSQL, pyodbc & linux
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:[EMAIL PROTECTED]: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
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
On Tue, Mar 25, 2008 at 9:21 AM, Paul Johnston <[EMAIL PROTECTED]> 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)
>
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
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
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 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: MSSQL, pyodbc & linux
On Mon, Aug 13, 2007 at 4:07 AM, Christophe de VIENNE
<[EMAIL PROTECTED]> 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
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
2007/8/13, Christophe de VIENNE <[EMAIL PROTECTED]>: > 2007/8/13, Paul Johnston <[EMAIL PROTECTED]>: > > > > 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. --~--~-~--~~~---~--~~ 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: MSSQL, pyodbc & linux
2007/8/13, Paul Johnston <[EMAIL PROTECTED]>: > 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 --~--~-~--~~~---~--~~ 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: MSSQL, pyodbc & linux
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'".
Longer term, if PyODBC can support unicode statements on Unix, that's
amazing!
Paul
On 8/13/07, Christophe de VIENNE <[EMAIL PROTECTED]> wrote:
>
>
> Hi again,
>
> 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
>
> >
>
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
Hi again,
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
--~--~-~--~~~---~--~~
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: MSSQL, pyodbc & linux
On 8/13/07, Christophe de VIENNE <[EMAIL PROTECTED]> wrote:
> 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 ?
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
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
