[sqlalchemy] Re: mapper for ms sql server
I'd be curious to see if it's really a lack of ALTER SCHEMA that's the culprit. Are you sure that the only thing you are varying to make it work is ALTER SCHEMA? This looks like a lack of access to the 'Sources' table, which can happen if the user that creates the table is missing certain properties. I think that if the account that created the table has the SA (Server Administrator?) role on the server then the table should be accessible to everyone. dbo may also be required. If an unprivileged user creates a table, I believe that, by default, it is inacessible to other unprivilieged users. So: verify that the table was created by SA (possibly dbo). Disclaimer - this is all off the top of my head. I got this from the docs, but I have a tough time getting my head around msdn navigation so I'm not sure I could point you to where I found this information. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] MSSQL pyodbc connection string - broken for me!
mssql through pyodbc (and unixODBC and FreeTDS on debian) connections are broken for me when I pass a url to create engine. I can see that databases/mssql.py:make_connect_string has been changed to express host and port as ''server=host,port from server=host;port=port when the driver attribute is 'SQL Server'. It seems that this change was made to address ticket 1192[0]. It seems that the way that the port should be expressed in the connection string is more related to the underlying driver implementation and not its name. As I mentioned above, we're using FreeTDS with unixODBC. The FreeTDS documentation on connection strings [1] indicates that the port specification is not a special case and that it should be specified int the 'key=value format. If, on my dev box, I change my /etc/odbcinst.ini entry from [SQL Server] to [FreeTDS] and I specify driver=FreeTDS (per the MSSQL section of the SA Database Notes[2]), then my connection seems to work. So clearly, for my configuration, the driver name is configurable and it doesn't actually say anything about the driver it names. Is the driver name on windows configurable? I'm guessing that it is not. Regardless, I think that making decisions based on the driver name is probably not a good idea. Maybe the way to handle this is to introduce another connect argument for pyodbc, but I'm not sure that this should look like. Maybe 'port_style', with possible values of 'windows', 'freetds', and whatever else people are using? Or maybe the values are 'comma' and 'attribute', or something. I'm terrible at naming things, so somebody please step in here. If we can get the names resolved, I'll be glad to put together a patch. [0] http://www.sqlalchemy.org/trac/ticket/1192 [1] http://www.freetds.org/userguide/odbcconnattr.htm [2] http://www.sqlalchemy.org/trac/wiki/DatabaseNotes --~--~-~--~~~---~--~~ 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, 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] MSSQL pyodbc - unicode problems since 0.5rc2
I'm using MSSQL + pyodbc + unixODBC + FreeTDS ... I have been tracking the 0.5 line for a while now, but I only recently noticed that I am unable to insert unicode into the database since 0.5rc2. Starting with 0.5rc3, when I do try to insert unicode into a column defined as Unicode or UnicodeText, I get an error like: [HY000] [FreeTDS][SQL Server]Could not perform COMMIT or ROLLBACK (0) (SQLEndTran) I should probably also add that I autoload my tables. So before I start tearing code apart ... does this sound familiar to anyone out there? Can anyone think of changes since 0.5rc2 that might not be local to mssql and that might result in such errors? Thanks, Matthew --~--~-~--~~~---~--~~ 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 null column definitions
There's now a ticket that addresses this, along with a patch: http://www.sqlalchemy.org/trac/ticket/1243 --~--~-~--~~~---~--~~ 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] MSSQL null column definitions
On MSSQL the default nullableness of a column, when neither NULL or NOT NULL are specified in the column definition, is configurable. The MSSQL dialect produces ANSI standard SQL when creating tables. So when a Column is specified with nullable=True, the DDL emitted contains neither NULL or NOT NULL. If the database is not set to use the ANSI null default, this produces unexpected results. I'm not sure what the policy is about things like this. Would you welcome a patch that always explicitly applied either NULL or NOT NULL to column definition DDL produces by the MSSQL dialect? It might lead to some people being surprised by the behavior of their tables, but it seems (to me) to be the best way to assure that there are no future surprises. --~--~-~--~~~---~--~~ 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] mssql unicode weirdness: empty nvarchar string is selected as u
I'm setting up my first project that will use SA exclusively for database access and I've run into some behavior that seems odd to me. If I insert u into a column that is defined as nvarchar then when I select that column, I receive u . So I'm receiving a unicode string containing one space where I would expect to find an empty unicode string. Does this ring any bells for anyone? I'm using: SQLAlchemy 0.5.0beta3 pyodbc 2.0.58 FreeTDS 0.82 Here's a test case: import unittest import pyodbc import sqlalchemy as sa class TestMSSQLConnections(unittest.TestCase): def setUp(self): self.host = self.port = self.host_and_port = %s:%s % (self.host, self.port) self.database = self.user = self.password = engine_url = mssql://%s:[EMAIL PROTECTED]/%s % \ (self.user, self.password, self.host_and_port, self.database) self.sa_engine = sa.create_engine(engine_url) self.unicode_test_table = sa.Table(unicode_test_table, sa.MetaData(), sa.Column(unicode_test_column, sa.Unicode(60))) self.unicode_test_table.drop(bind=self.sa_engine) self.unicode_test_table.create(bind=self.sa_engine) def test_sqlalchemy_over_pyodbc(self): assert isinstance(self.sa_engine.dialect, sa.databases.mssql.MSSQLDialect_pyodbc), \ self.sa_engine.dialect self.sa_engine.execute(self.unicode_test_table.insert(), unicode_test_column=u) rows = list(self.sa_engine.execute(self.unicode_test_table.select())) assert u == rows[0].unicode_test_column, \ repr(rows[0].unicode_test_column) unittest.main() --~--~-~--~~~---~--~~ 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 unicode weirdness: empty nvarchar string is selected as u
Almost forgot: I'm working against SQL Server 2000. --~--~-~--~~~---~--~~ 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] Accessing tables in multiple databases with one connection (MSSQL)
Hi Folks, In my current environment, I get one database connection per web application. That connection has to access tables in a data warehouse as well as an application specific database. Both databases live on the same SQL Server instance. It seems as though I can't use the sql generation component to access tables from both databases at the same time. I am able to do this in my hand-written SQL by fully qualifying the tables names as database.owner.table. I have tried using this fully qualified name as the table name and also using database.owner as the schema argument to the constructor, but neither works. I also noticed that there is an owner argument to the constructor, but that doesn't seem to do the trick. Is this at all possible right now? Thanks, Matthew --~--~-~--~~~---~--~~ 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: Accessing tables in multiple databases with one connection (MSSQL)
Hi Rick, On Jun 5, 4:05 pm, Rick Morrison [EMAIL PROTECTED] wrote: I don't think so, not directly. I was afraid of this. Short-term, here's a couple of things to try: [snip suggestions] I appreciate the suggestions. I may see about adding a view for this purpose. We decided against it when we started work on our environment, but it may be time to reconsider. Longer term, this is going to need support on both the DB-API and the SA level. AFAIK, only MSSQL has this kind of feature, and adding it will probably risk breaking a lot of other things. So, if it is going to need a lot of changes to SA to make it happen, it probably won't. I poked around in SA to see if I could figure out where this would go and I must admit that I had a tough time getting my mind around it. I'm also ignorant when it comes to the DB-API aspect. What kind of support would be required for the DB-API? Thanks, Matthew --~--~-~--~~~---~--~~ 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: Null Foreign key issues
Hi wfpearson, On Apr 18, 1:21 pm, wfpearson [EMAIL PROTECTED] wrote: [snip full description of the problem] I've tried the following method: surgery = session.query(Surgery).select_by(dictation=None)[0] I'm pretty new to all of this myself, but maybe try using clause elements in yout select_by. See: http://www.sqlalchemy.org/docs/datamapping.html#datamapping_query So maybe something like: surgery = session.query(Surgery).select_by(surgery_table.c.dictation_id==null()) [0] would work. This is _totally_ untested (and it might be gibberish), but it might be worth a try. Good Luck, Matthew --~--~-~--~~~---~--~~ 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: simple DateTime select
Hi Ryan,
On Apr 7, 6:10 pm, rkennedy [EMAIL PROTECTED] wrote:
I'm new to SQLAlchemy and am trying to select objects from the
following table that occurred before a specified date.
I'm pretty new myself, but I've been reading the docs a bunch today
and I may be able to help.
event_table = Table('event', meta,
Column('event_id', Integer, primary_key=True),
Column('title', String()),
Column('start_time', DateTime,default=func.now()),
Column('stop_time', DateTime,default=func.now()),
)
[snip]
event = self.sess.query(model.Event).get_by(start_time '2007-10-19
10:23:54')
I think that when you use get_by this way you are supplying a keyword
argument and not referencing a column name. Try something like
(untested):
event = self.sess.query(model.Event).get_by(event_table.c.start_time
'2007-10-19 10:23:54')
That builds a ClauseElement that should do what you want (if I
understand correctly).
I hope I'm correct and this is helpful.
Regards,
Matthew
--~--~-~--~~~---~--~~
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: simple DateTime select
Hi Ryan, On Apr 7, 8:44 pm, rkennedy [EMAIL PROTECTED] wrote: Thanks, Matthew. Looks like SA is still complaining about the global name not being defined. event = self.sess.query(model.Event).get_by(event_table.c.start_time '2007-10-19 10:23:54') The above code produces a similar error... NameError: global name 'event_table' is not defined Does the above code have access to your event table definition? That looks to me like Python can't find event table. I don't think SA has even gotten to it yet. Make sure that you've made the necessary imports to get event_table into the local namspace. When I try retrieving objects that occurred on a specific date, e.g event = self.sess.query(model.Event).get_by(start_time='2006-10-19 10:23:54') ..everything checks out ok. Perhaps it has something to do with the way the operator is being used? When you use the start_time=... form above, you are passing a keyword argument to the get_by method. The start_time name is used inside get_by and so it doesn't need to exist outside. When you use the start_time==... form, I think that you'll find that you will get the same NameError that you got when you tried start_time Regards, Matthew --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
