[sqlalchemy] Re: MSSQL pyodbc connection string - broken for me!
This has been bandied back and forth for months, and I think it's becoming clear that having sqla map dburl's to ODBC connection strings is a losing battle. Yet another connection argument is not sounding very attractive to me. Perhaps a simple reductionist policy for ODBC connections would be best. The user would either specify a DSN, which would be passed to the driver, or give a full ODBC connection string (presumably via a keyword arg) which would be used verbatim. Anything else seems to degrade to the kind of error-prone heuristics we see here. It is a big change from the current behavior of trying to fit in with the way that the dburl works for other dialects, though. Jason's dialect refactor is going to confront this problem head-on as well. Any thoughts regarding this from that perspective? 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 connection string - broken for me!
On Dec 10, 1:27 pm, Rick Morrison [EMAIL PROTECTED] wrote: This has been bandied back and forth for months, and I think it's becoming clear that having sqla map dburl's to ODBC connection strings is a losing battle. Yet another connection argument is not sounding very attractive to me. Perhaps a simple reductionist policy for ODBC connections would be best. The user would either specify a DSN, which would be passed to the driver, or give a full ODBC connection string (presumably via a keyword arg) which would be used verbatim. Anything else seems to degrade to the kind of error-prone heuristics we see here. You make a good point about heuristics. That describes the current implementation as well as my initial proposal involving argument values of 'windows' and 'freetds' and stuff. I'm glad you pointed that out. For the reasons I'll add below, I still favor a connection argument, but the argument values should indicate the outcome and not try to attribute behaviors to platforms when there's a lack of full understanding about what those behaviors are. I still can't think of good names. I dislike the idea of relying heavily on DSNs since I don't want SA to tell people how to manage their systems. Giving full support to DSN- less connections let's SA work with existing systems. Depending on a keyword argument seems troublesome, too, since there are so many existing configurations using SA that count on being able to specify connection information through a dburl. sqlalchemy-migrate is the one that I'm working with now. I may be overstating this, but the sense that I have is that using keyword arguments to specify connection information is something that most people don't do. I do it all the time so I'm glad it's there, but it's always felt like a workaround. It is a big change from the current behavior of trying to fit in with the way that the dburl works for other dialects, though. Jason's dialect refactor is going to confront this problem head-on as well. Any thoughts regarding this from that perspective? I don't know what this wll look like, so maybe the discussion is moot. I look forward to hearing about it. --~--~-~--~~~---~--~~ 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 connection string - broken for me!
On Dec 10, 2008, at 2:49 PM, desmaj wrote: I dislike the idea of relying heavily on DSNs since I don't want SA to tell people how to manage their systems. Giving full support to DSN- less connections let's SA work with existing systems. DSNs would eliminate these issues for SQLAlchemy. DSNs are the recommended way to connect with ODBC and im always confused why people don't use them. Back when I used to use ODBC heavily, it was the *only* way to connect. All of this host/port stuff with ODBC client libraries is unknown to me. this is just my 2c, im not here to say how it should be done or not. I would think that the standard SQLA host/port connect pattern should work as well if we just are aware of what kind of client library we're talking to. If we can't autodetect that, then we just use a keyword argument ?connect_type=FreeTDS. We can document all the different connect types somewhere and just adapt to all the newcomers that way. With DSN being the default. I definitely do not want to start allowing raw connect strings through create_engine() - if you need to do that, use the creator() function. Whats the status of 0.5, is DSN the default in trunk 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 connection string - broken for me!
On Wed, Dec 10, 2008 at 1:49 PM, desmaj [EMAIL PROTECTED] wrote:
On Dec 10, 1:27 pm, Rick Morrison [EMAIL PROTECTED] wrote:
This has been bandied back and forth for months, and I think it's becoming
clear that having sqla map dburl's to ODBC connection strings is a losing
battle. Yet another connection argument is not sounding very attractive to
me.
Perhaps a simple reductionist policy for ODBC connections would be best. The
user would either specify a DSN, which would be passed to the driver, or
give a full ODBC connection string (presumably via a keyword arg) which
would be used verbatim. Anything else seems to degrade to the kind of
error-prone heuristics we see here.
You make a good point about heuristics. That describes the current
implementation as well as my initial proposal involving argument
values of 'windows' and 'freetds' and stuff. I'm glad you pointed that
out. For the reasons I'll add below, I still favor a connection
argument, but the argument values should indicate the outcome and not
try to attribute behaviors to platforms when there's a lack of full
understanding about what those behaviors are. I still can't think of
good names.
I dislike the idea of relying heavily on DSNs since I don't want SA to
tell people how to manage their systems. Giving full support to DSN-
less connections let's SA work with existing systems.
Depending on a keyword argument seems troublesome, too, since there
are so many existing configurations using SA that count on being able
to specify connection information through a dburl. sqlalchemy-migrate
is the one that I'm working with now. I may be overstating this, but
the sense that I have is that using keyword arguments to specify
connection information is something that most people don't do. I do it
all the time so I'm glad it's there, but it's always felt like a
workaround.
It is a big change from the current behavior of trying to fit in with the
way that the dburl works for other dialects, though. Jason's dialect
refactor is going to confront this problem head-on as well. Any thoughts
regarding this from that perspective?
I don't know what this wll look like, so maybe the discussion is moot.
I look forward to hearing about it.
I've been using the following connection string with unixodbc for a
year now since 0.4.6 version.
sqlalchemy.create_engine(mssql://user:[EMAIL
PROTECTED]:1433/databasename?driver=TDSodbc_options='TDS_Version=8.0')
which gets translated into :
pyodbc.connect(SERVER=;UID=xxx;PWD=xxx;DRIVER={TDS};TDS_Version=7.0)
It works great for me.
So what is not working exactly? Can you provide an example:
Have you setup the unixodbc/freetds already?
http://lucasmanual.com/mywiki/unixODBC
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 connection string - broken for me!
On Wed, Dec 10, 2008 at 2:05 PM, Michael Bayer [EMAIL PROTECTED] wrote: On Dec 10, 2008, at 2:49 PM, desmaj wrote: I dislike the idea of relying heavily on DSNs since I don't want SA to tell people how to manage their systems. Giving full support to DSN- less connections let's SA work with existing systems. DSNs would eliminate these issues for SQLAlchemy. DSNs are the recommended way to connect with ODBC and im always confused why people don't use them. Back when I used to use ODBC heavily, it was the *only* way to connect. All of this host/port stuff with ODBC client libraries is unknown to me. this is just my 2c, im not here to say how it should be done or not. I would think that the standard SQLA host/port connect pattern should work as well if we just are aware of what kind of client library we're talking to. If we can't autodetect that, then we just use a keyword argument ?connect_type=FreeTDS. We can document all the different connect types somewhere and just adapt to all the newcomers that way. With DSN being the default. I definitely do not want to start allowing raw connect strings through create_engine() - if you need to do that, use the creator() function. Whats the status of 0.5, is DSN the default in trunk now ? In my opinion sqlalchemy should provide dsn and dsn-less connections options. I know it does dns-less and I'm not sure what the status is on dsn connections. If both are available I think that is great, if you force users to use one or another that would cause problems for me especially if you don't allow dns-less connections. 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 connection string - broken for me!
On Dec 10, 3:05 pm, Michael Bayer [EMAIL PROTECTED] wrote: this is just my 2c, im not here to say how it should be done or not. I would think that the standard SQLA host/port connect pattern should work as well if we just are aware of what kind of client library we're talking to. If we can't autodetect that, then we just use a keyword argument ?connect_type=FreeTDS. We can document all the different connect types somewhere and just adapt to all the newcomers that way. With DSN being the default. I definitely do not want to start allowing raw connect strings through create_engine() - if you need to do that, use the creator() function. As Rick said, autodetection of this stuff is error-prone. Maybe 'connect_type' can work, but I'd want to get all the mssql/ pyodbc folks on board before that got finalized. I don't know enough about FreeTDS to stand behind any configuration and say that it should work in all cases. If connect type is decided as the right approach, though, then I will work something up for that. Whats the status of 0.5, is DSN the default in trunk now ? DSN is the first choice in MSSQLDialect_pyodbc.make_connect_string right now. Thanks to Rick, Mike and Lukasz for helping me out and weighing in on this. --~--~-~--~~~---~--~~ 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 connection string - broken for me!
Whats the status of 0.5, is DSN the default in trunk now ? DSN is the first choice in MSSQLDialect_pyodbc.make_connect_string right now. That's not what I see. I just pulled the 0.5 trunk, which I haven't been tracking lately. Still uses the 'dsn' keyword build a connection string with DSN, otherwise defaults to the former dsn-less connection string behavior. What Mike is taking about is an idea initially advanced by Marc-Andre Lemburg to make the 'host' portion of the dburl to represent a DSN for pyodbc connections (any supplied database name would be ignored) for the 0.5 trunk, and have the existing dsn-less connection behavior become a keyword-only kind of thing. I made a patch for that many moons ago, but never committed it; it makes a lot of sense, and the 0.5 release would be an appropriate time to introduce this kind of compatibilty-breaking behavior. But there's been a rather surprising lack of fans for the idea, and I would expect to hear some grumbling from users if/when we do it. --~--~-~--~~~---~--~~ 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 connection string - broken for me!
DSN is the one keyword for host that is universally recognized as part of odbc proper, so it makes sense that host/port would be the exception case - especially considering it seems like we now have to pick among many formats for propagating host/port and are going to require some kind of host_format/connect_type or something for the host/port case anyway. lets just nail this down so 0.5 can go out soon. lets also add awesome docs to our awesome new doc system. On Dec 10, 2008, at 4:42 PM, Rick Morrison wrote: Whats the status of 0.5, is DSN the default in trunk now ? DSN is the first choice in MSSQLDialect_pyodbc.make_connect_string right now. That's not what I see. I just pulled the 0.5 trunk, which I haven't been tracking lately. Still uses the 'dsn' keyword build a connection string with DSN, otherwise defaults to the former dsn- less connection string behavior. What Mike is taking about is an idea initially advanced by Marc- Andre Lemburg to make the 'host' portion of the dburl to represent a DSN for pyodbc connections (any supplied database name would be ignored) for the 0.5 trunk, and have the existing dsn-less connection behavior become a keyword-only kind of thing. I made a patch for that many moons ago, but never committed it; it makes a lot of sense, and the 0.5 release would be an appropriate time to introduce this kind of compatibilty-breaking behavior. But there's been a rather surprising lack of fans for the idea, and I would expect to hear some grumbling from users if/when we do it. --~--~-~--~~~---~--~~ 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 connection string - broken for me!
On Wed, Dec 10, 2008 at 3:42 PM, Rick Morrison [EMAIL PROTECTED] wrote:
Whats the status of 0.5, is DSN the default in trunk now ?
DSN is the first choice in MSSQLDialect_pyodbc.make_connect_string
right now.
That's not what I see. I just pulled the 0.5 trunk, which I haven't been
tracking lately. Still uses the 'dsn' keyword build a connection string with
DSN, otherwise defaults to the former dsn-less connection string behavior.
What Mike is taking about is an idea initially advanced by Marc-Andre
Lemburg to make the 'host' portion of the dburl to represent a DSN for
pyodbc connections (any supplied database name would be ignored) for the 0.5
trunk, and have the existing dsn-less connection behavior become a
keyword-only kind of thing.
I made a patch for that many moons ago, but never committed it; it makes a
lot of sense, and the 0.5 release would be an appropriate time to introduce
this kind of compatibilty-breaking behavior.
Rick, Michael could you guys give me an example of the connection string then?
If I understand this correctly:
You guys are saying that dsn=... would replace the host:port or
host,port,databasename?
Would this change allow to supply odbc options just like I can
currently with TDS_version=7.0 which can only be supplied at a
connection?
http://www.freetds.org/userguide/odbcconnattr.htm
I'm not clear as far as what functionality you guys want to break:
From pyodbc docs the following are the connection strings so it would
seem to me that these could be fairly easy to create based on dsn= or
not in a sqlalchemy connection string. Unless I'm way of topic then
just let me know.
cnxn = pyodbc.connect(DSN=dsnname;UID=user;PWD=password)
or
cnxn = pyodbc.connect('DRIVER={SQL
Server};SERVER=server;DATABASE=database;UID=user;PWD=password)
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 connection string - broken for me!
Here is my take, keeping in mind i havent used a windows machine in
about a year:
On Dec 10, 2008, at 5:13 PM, Lukasz Szybalski wrote:
cnxn = pyodbc.connect(DSN=dsnname;UID=user;PWD=password)
mssql://user:[EMAIL PROTECTED]/
or
cnxn = pyodbc.connect('DRIVER={SQL
Server};SERVER=server;DATABASE=database;UID=user;PWD=password)
mssql://user:[EMAIL PROTECTED]/database?
connect_type=TDS7other=argsthat=areneeded=foo
using connect_type, or some better name, we can map the URL scheme
to an unlimited number of vendor specific connect strings on the back.
--~--~-~--~~~---~--~~
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 connection string - broken for me!
Something like this: As of 0.5 for pyodbc connections: a) If the keyword argument 'odbc_connect' is given, it is assumed to be a full ODBC connection string, which is used for the connection (perhaps we can include a facility for Python sting interpolation into this string from the dburi components). b) otherwise, the host portion of the dburl represents a ODBC DSN. A simple connection string is constructed using the user name and password and DSN (host) from the dburl. Any given database name is ignored. Finally, if present, the contents of the keyword argument 'odbc_options' (assumed to be a string) are concatenated to the connection string generated in either (a) or (b). --~--~-~--~~~---~--~~ 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 connection string - broken for me!
mssql://user:[EMAIL PROTECTED]/database? connect_type=TDS7other=argsthat=areneeded=foo using connect_type, or some better name, we can map the URL scheme to an unlimited number of vendor specific connect strings on the back. Yeah, it's exactly that kind of mapping that has so far been a fussy pain in the neck to use and maintain. I'm for nuking the idea and just going with a straight-up ODBC connection string as in my last last posting. --~--~-~--~~~---~--~~ 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 connection string - broken for me!
On Dec 10, 2008, at 5:21 PM, Rick Morrison wrote:
Something like this:
As of 0.5 for pyodbc connections:
a) If the keyword argument 'odbc_connect' is given, it is assumed to
be a full ODBC connection string, which is used for the connection
(perhaps we can include a facility for Python sting interpolation
into this string from the dburi components).
so you mean create_engine('mssql://?
odbc_connect
=
DRIVER
=
{SQLServer
};SERVER=server;DATABASE=database;UID=user;PWD=password') ?I can't
go with that, there has to be some way to plug in a URL handler.if
you really need to send that raw string straight through you can use
the creator() function. Its really not a big deal to maintain if
we just make a modular URLHandler class that given a sqlalchemy.URL
and a DBAPI, returns a connection. Anytime someone has some new
goofy string they need, they can provide one of these, we add it to
the MSSQL dialect, and we wont have to change it again, since it will
be keyed to a name.
--~--~-~--~~~---~--~~
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 connection string - broken for me!
On Dec 10, 2008, at 5:39 PM, Rick Morrison wrote: Its really not a big deal to maintain if we just make a modular URLHandler class that given a sqlalchemy.URL and a DBAPI, returns a connection. Anytime someone has some new goofy string they need, they can provide one of these, we add it to the MSSQL dialect, and we wont have to change it again, since it will be keyed to a name. OK, that sounds promising and like it has a chance to keep working in 0.6 too. Is that a new idea, or does that already exist somewhere in SA? its just a new idea to solve the problem locally within ms-sql for now, its just a dictionary of callables or objects of some kind. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
