[sqlalchemy] Re: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
OK, I checked to make sure the updates were being fired (and from the looks of the log, they are). But I think I see that the lack of update executions hasn't been the problem all along, but rather that those updates are not finding their row... never checked that part. I'm offsite right now and can't look at the code, but I suspect that the milliseconds are the problem -- MSSQL rounds milliseconds to some multiple, so what you put in is not always what you get back. Since the program saves the initial date PK as the result of a datetime.now() call, I'll bet that it doesn't match the DB stored value. Here's a couple of things you can do to work around that: a) Truncate the milliseconds from the datetime.now() call before you write the initial job object b) Fetch the job object back after the first flush() to get the DB stored value. See if one of those fixes your 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
On 19 Dic, 01:37, "Rick Morrison" <[EMAIL PROTECTED]> wrote: > Same here on pymssql. > > I tried it with 'start' as the only PK, and with both 'identifier' and > 'start' as PK. Both work fine. > > Are you sure your in-database tabledef matches your declared schema? > > I've attached a script that works here. This one has both 'identifier' and > 'start' set as PK. > > ***---WARNING ---***: > I've added a table.drop() to the script to simplify testing and make > sure the schemas match I understand it could seem impossible Rick, but if i run your script it doesn't update the row!!! ( I swear!! ). I'm really confused on what's going on... maybe py_odbc? Here's the log: 2007-12-19 10:18:29,421 INFO sqlalchemy.engine.base.Engine.0x..d0 DROP TABLE jobs 2007-12-19 10:18:29,421 INFO sqlalchemy.engine.base.Engine.0x..d0 {} 2007-12-19 10:18:29,421 INFO sqlalchemy.engine.base.Engine.0x..d0 COMMIT 2007-12-19 10:18:29,421 INFO sqlalchemy.engine.base.Engine.0x..d0 CREATE TABLE jobs ( identifier NUMERIC(18, 2) NOT NULL, section VARCHAR(20), start DATETIME NOT NULL, stop DATETIME, station VARCHAR(20), PRIMARY KEY (identifier, start) ) 2007-12-19 10:18:29,421 INFO sqlalchemy.engine.base.Engine.0x..d0 {} 2007-12-19 10:18:29,437 INFO sqlalchemy.engine.base.Engine.0x..d0 COMMIT 2007-12-19 10:18:29,437 INFO sqlalchemy.engine.base.Engine.0x..d0 BEGIN 2007-12-19 10:18:29,437 INFO sqlalchemy.engine.base.Engine.0x..d0 SET nocount ON 2007-12-19 10:18:29,437 INFO sqlalchemy.engine.base.Engine.0x..d0 {} 2007-12-19 10:18:29,437 INFO sqlalchemy.engine.base.Engine.0x..d0 INSERT INTO jo bs (identifier, section, start, stop, station) VALUES (?, ?, ?, ?, ?) 2007-12-19 10:18:29,437 INFO sqlalchemy.engine.base.Engine.0x..d0 ['22', None, d atetime.datetime(2007, 12, 19, 10, 18, 29, 437000), None, 'TCHUKI'] 2007-12-19 10:18:29,437 INFO sqlalchemy.engine.base.Engine.0x..d0 COMMIT 2007-12-19 10:18:30,437 INFO sqlalchemy.engine.base.Engine.0x..d0 BEGIN 2007-12-19 10:18:30,437 INFO sqlalchemy.engine.base.Engine.0x..d0 SET nocount ON 2007-12-19 10:18:30,437 INFO sqlalchemy.engine.base.Engine.0x..d0 {} 2007-12-19 10:18:30,437 INFO sqlalchemy.engine.base.Engine.0x..d0 SELECT jobs.id entifier AS jobs_identifier, jobs.section AS jobs_section, jobs.start AS jobs_st art, jobs.stop AS jobs_stop, jobs.station AS jobs_station FROM jobs ORDER BY jobs.identifier 2007-12-19 10:18:30,437 INFO sqlalchemy.engine.base.Engine.0x..d0 [] 2007-12-19 10:18:30,453 INFO sqlalchemy.engine.base.Engine.0x..d0 SET nocount ON 2007-12-19 10:18:30,453 INFO sqlalchemy.engine.base.Engine.0x..d0 {} 2007-12-19 10:18:30,453 INFO sqlalchemy.engine.base.Engine.0x..d0 UPDATE jobs SE T stop=? WHERE jobs.identifier = ? AND jobs.start = ? 2007-12-19 10:18:30,453 INFO sqlalchemy.engine.base.Engine.0x..d0 [datetime.date time(2007, 12, 19, 10, 18, 30, 453000), '22.00', datetime.datetime(2007, 12, 19, 10, 18, 29)] 2007-12-19 10:18:30,467 INFO sqlalchemy.engine.base.Engine.0x..d0 COMMIT --~--~-~--~~~---~--~~ 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
Same here on pymssql. I tried it with 'start' as the only PK, and with both 'identifier' and 'start' as PK. Both work fine. Are you sure your in-database tabledef matches your declared schema? I've attached a script that works here. This one has both 'identifier' and 'start' set as PK. ***---WARNING ---***: I've added a table.drop() to the script to simplify testing and make sure the schemas match --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- import sqlalchemy as sa import datetime, time from sqlalchemy.orm import sessionmaker #dburi = 'mssql://driveulator:[EMAIL PROTECTED]/d2test' sa_engine=sa.create_engine("mssql://:[EMAIL PROTECTED]/xxx", echo=True) metadata = sa.MetaData(sa_engine) Session = sessionmaker(bind=sa_engine, autoflush=True, transactional=True) sa_session = Session() jobs = sa.Table('jobs', metadata, sa.Column('identifier', sa.Numeric(18), primary_key=True), sa.Column('section', sa.VARCHAR(20)), sa.Column("start",sa.DateTime, primary_key=True), sa.Column("stop",sa.DateTime), sa.Column("station", sa.VARCHAR(20)), autoload=False)#ok class Job(object): def __init__(self, identifier, start, station="TCHUKI"): self.identifier, self.start, self.station=identifier, start, station sa.orm.mapper(Job, jobs) try: jobs.drop() except: pass jobs.create() j = Job(22, datetime.datetime.now()) sa_session.save(j) sa_session.commit() sa_session.clear() time.sleep(1) j1=sa_session.query(Job).all()[0] while True: j1.stop=datetime.datetime.now() sa_session.save_or_update(j1) sa_session.commit() time.sleep(2)
[sqlalchemy] Re: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
On Dec 18, 2007, at 6:44 PM, Smoke wrote: > > Sorry because i'm a bit late ( work deadlines are struggling my > time! :) ). > I've made some different configurations and schema definitions... and > i've noticed that it never updates a row if i set the datetime field > as PK ( never! even if i set it as the only PK .. ). If i set > composite PKs excluding any datetime column everything works fine ( it > also works if i set a datetime as PK at the database side and > excluding it at the schema on sqlalchemy side.. ). Sorry about the > mess with the PKs between the former examples but i was only trying to > say that changing the schema results that everything works fine... if i set "start" and "identifier" as primary key columns, script works fine for both sqlite and postgres, using both 0.4.1 and trunk. mssql guys will have to try it on mssql. --~--~-~--~~~---~--~~ 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
Sorry because i'm a bit late ( work deadlines are struggling my time! :) ). I've made some different configurations and schema definitions... and i've noticed that it never updates a row if i set the datetime field as PK ( never! even if i set it as the only PK .. ). If i set composite PKs excluding any datetime column everything works fine ( it also works if i set a datetime as PK at the database side and excluding it at the schema on sqlalchemy side.. ). Sorry about the mess with the PKs between the former examples but i was only trying to say that changing the schema results that everything works fine... Here's a little piece of code just as an example to start playing... ( it works for me.., but if i include start as PK it "crashes") import sqlalchemy as sa import datetime, time from sqlalchemy.orm import sessionmaker sa_engine=sa.create_engine("mssql://sa:[EMAIL PROTECTED]/siaDB_BR", echo=True) metadata = sa.MetaData(sa_engine) Session = sessionmaker(bind=sa_engine, autoflush=True, transactional=True) sa_session = Session() jobs = sa.Table('jobs', metadata, sa.Column('identifier', sa.Numeric(18), primary_key=True), sa.Column('section', sa.VARCHAR(20)), sa.Column("start",sa.DateTime), sa.Column("stop",sa.DateTime), sa.Column("station", sa.VARCHAR(20), primary_key=True), autoload=False)#ok class Job(object): def __init__(self, identifier, start, station="TCHUKI"): self.identifier, self.start, self.station=identifier, start, station sa.orm.mapper(Job, jobs) j = Job(22, datetime.datetime.now()) sa_session.save(j) sa_session.commit() sa_session.clear() time.sleep(1) j1=sa_session.query(Job).all()[0] j1.stop=datetime.datetime.now() sa_session.save_or_update(j1) sa_session.commit() On 12 Dic, 17:54, "Rick Morrison" <[EMAIL PROTECTED]> wrote: > Hey Fabio, would you please post a full non-working copy with the new schema > and all the PKs that you want set up? There are a few too many variants in > this thread to see what's going on now. Your earlier versions didn't include > 'station' as a PK, but did include 'start', while this one's the opposite. --~--~-~--~~~---~--~~ 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
Sorry because i'm a bit late ( work deadlines are struggling my time! :) ). I've made some different configurations and schema definitions... and i've noticed that it never updates a row if i set the datetime field as PK ( never! even if i set it as the only PK .. ). If i set composite PKs excluding any datetime column everything works fine ( it also works if i set a datetime as PK at the database side and excluding it at the schema on sqlalchemy side.. ). Sorry about the mess with the PKs between the former examples but i was only trying to say that changing the schema results that everything works fine... Here's a little piece of code just as an example to start playing... ( it works for me.., but if i include start as PK it "crashes") import sqlalchemy as sa import datetime, time from sqlalchemy.orm import sessionmaker sa_engine=sa.create_engine("mssql://sa:[EMAIL PROTECTED]/siaDB_BR", echo=True) metadata = sa.MetaData(sa_engine) Session = sessionmaker(bind=sa_engine, autoflush=True, transactional=True) sa_session = Session() jobs = sa.Table('jobs', metadata, sa.Column('identifier', sa.Numeric(18), primary_key=True), sa.Column('section', sa.VARCHAR(20)), sa.Column("start",sa.DateTime), sa.Column("stop",sa.DateTime), sa.Column("station", sa.VARCHAR(20), primary_key=True), autoload=False)#ok class Job(object): def __init__(self, identifier, start, station="TCHUKI"): self.identifier, self.start, self.station=identifier, start, station sa.orm.mapper(Job, jobs) j = Job(22, datetime.datetime.now()) sa_session.save(j) sa_session.commit() sa_session.clear() time.sleep(1) j1=sa_session.query(Job).all()[0] j1.stop=datetime.datetime.now() sa_session.save_or_update(j1) sa_session.commit() On 12 Dic, 17:54, "Rick Morrison" <[EMAIL PROTECTED]> wrote: > Hey Fabio, would you please post a full non-working copy with the new schema > and all the PKs that you want set up? There are a few too many variants in > this thread to see what's going on now. Your earlier versions didn't include > 'station' as a PK, but did include 'start', while this one's the opposite. --~--~-~--~~~---~--~~ 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
I'm not on my pc right now so I can send you the non working copy only tomorrow I've tried several schemas changes to try and see if the problem always occurs or if there cases that it works, not necessary because i need all those schemas In the former table schema, as i said, i've included only identifier and station as PKs, but the table in the database is set with PKs on identifier, START and station... and save_or_update works fine... I'll post some code tomorrow... On 12 Dic, 17:54, "Rick Morrison" <[EMAIL PROTECTED]> wrote: > Hey Fabio, would you please post a full non-working copy with the new schema > and all the PKs that you want set up? There are a few too many variants in > this thread to see what's going on now. Your earlier versions didn't include > 'station' as a PK, but did include 'start', while this one's the opposite. --~--~-~--~~~---~--~~ 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
Hey Fabio, would you please post a full non-working copy with the new schema and all the PKs that you want set up? There are a few too many variants in this thread to see what's going on now. Your earlier versions didn't include 'station' as a PK, but did include 'start', while this one's the opposite. --~--~-~--~~~---~--~~ 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
Yeah... it's really strange i've made some other tryies and i've noticed the following: if i use: jobs = sa.Table('jobs', metadata, sa.Column('identifier', sa.VARCHAR(20), primary_key=True), sa.Column('section', sa.VARCHAR(20)), sa.Column("start",sa.DateTime), sa.Column("stop",sa.DateTime), sa.Column("station", sa.VARCHAR(20), primary_key=True), autoload=False)#ok and set identifier, start and station as PKs at the database side ( wich is incongruent with the sqlalchemy's table definition ), it works... If i define start as PKS at sqlalchemy's side it doesn't work.. and it still don't throw any exception ( still using pyodbc ). It seems that the datetime PK is blocking something... Even if I set identifier as Numeric and start and station as Datetime and VARCHAR it still doesn't work if i keep defining start as PK at sqlalchemy side... On 11 Dic, 00:43, Paul Johnston <[EMAIL PROTECTED]> wrote: > Hi, > > > > > > > Ok, you got my attention :-) Not at my best right now after being out > drinking, but hey... P.S. - Paul... am I wrong or i've seen you out drinking also at PyConUK nights? ;) Fabio --~--~-~--~~~---~--~~ 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
Hi, > > Ok, you got my attention :-) Not at my best right now after being out drinking, but hey... After a little tweak to the code (removing autoload=True, adding metadata.create_all() ) I get this: sqlalchemy.exceptions.ProgrammingError: (ProgrammingError) ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting from a character string to uniqueidentifier. (8169)') u'INSERT INTO jobs (identifier, section, start, stop, station) VALUES (?, ?, ?, ?, ?)' ['TEST1', None, datetime.datetime(2007, 12, 10, 23, 40, 30,593000), None, None] So, follow Rick's advice on fixing it. This does work with SQLite, but that's an accident of SQLite's funky type system more than anything. 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
> I did not get any exception... doh! :) What kind of exception did > you get? The traceback I get is below. If you're not getting one, it may be a pyodbc issue, which I don't have installed right now. Traceback (most recent call last): File "test.py", line 31, in ? sa_session.commit() File "/usr/lib/python2.4/site-packages/SQLAlchemy- 0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/session.py", line 484, in commit self.transaction = self.transaction.commit() File "/usr/lib/python2.4/site-packages/SQLAlchemy- 0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/session.py", line 211, in commit self.session.flush() File "/usr/lib/python2.4/site-packages/SQLAlchemy- 0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/session.py", line 684, in flush self.uow.flush(self, objects) File "/usr/lib/python2.4/site-packages/SQLAlchemy- 0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/unitofwork.py", line 207, in flush flush_context.execute() File "/usr/lib/python2.4/site-packages/SQLAlchemy- 0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/unitofwork.py", line 434, in execute UOWExecutor().execute(self, head) File "/usr/lib/python2.4/site-packages/SQLAlchemy- 0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/unitofwork.py", line 1053, in execute self.execute_save_steps(trans, task) File "/usr/lib/python2.4/site-packages/SQLAlchemy- 0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/unitofwork.py", line 1067, in execute_save_steps self.save_objects(trans, task) File "/usr/lib/python2.4/site-packages/SQLAlchemy- 0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/unitofwork.py", line 1058, in save_objects task.mapper.save_obj(task.polymorphic_tosave_objects, trans) File "/usr/lib/python2.4/site-packages/SQLAlchemy- 0.4.2dev_r3844-py2.4.egg/sqlalchemy/orm/mapper.py", line 1129, in save_obj c = connection.execute(statement.values(value_params), params) File "/usr/lib/python2.4/site-packages/SQLAlchemy- 0.4.2dev_r3844-py2.4.egg/sqlalchemy/engine/base.py", line 796, in execute return Connection.executors[c](self, object, multiparams, params) File "/usr/lib/python2.4/site-packages/SQLAlchemy- 0.4.2dev_r3844-py2.4.egg/sqlalchemy/engine/base.py", line 847, in execute_clauseelement return self._execute_compiled(elem.compile(dialect=self.dialect, column_keys=keys, inline=len(params) > 1), distilled_params=params) File "/usr/lib/python2.4/site-packages/SQLAlchemy- 0.4.2dev_r3844-py2.4.egg/sqlalchemy/engine/base.py", line 859, in _execute_compiled self.__execute_raw(context) File "/usr/lib/python2.4/site-packages/SQLAlchemy- 0.4.2dev_r3844-py2.4.egg/sqlalchemy/engine/base.py", line 871, in __execute_raw self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File "/usr/lib/python2.4/site-packages/SQLAlchemy- 0.4.2dev_r3844-py2.4.egg/sqlalchemy/engine/base.py", line 887, in _cursor_execute raise exceptions.DBAPIError.instance(statement, parameters, e) sqlalchemy.exceptions.DatabaseError: (DatabaseError) internal error: SQL Server message 245, severity 16, state 1, line 1: Conversion failed when converting the varchar value 'TEST1' to data type int. DB-Lib error message 20018, severity 5: General SQL Server error: Check messages from the SQL Server. 'INSERT INTO jobs (identifier, section, start, stop, station) VALUES (%(identifier)s, %(section)s, %(start)s, %(stop)s, %(station)s)' {'start': datetime.datetime(2007, 12, 10, 18, 15, 23, 170889), 'section': None, 'station': None, 'stop': None, 'identifier': 'TEST1'} --~--~-~--~~~---~--~~ 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
Thanks... i'll try changing to numeric... On 10 Dic, 19:57, "Rick Morrison" <[EMAIL PROTECTED]> wrote: > I did get an exception, that's how I knew to change the type! > I did not get any exception... doh! :) What kind of exception did you get? --~--~-~--~~~---~--~~ 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
I did get an exception, that's how I knew to change the type! On 12/10/07, Michael Bayer <[EMAIL PROTECTED]> wrote: > > > > On Dec 10, 2007, at 12:47 PM, Rick Morrison wrote: > > > This works here on MSSQL/pymssql with a small change: > > > > -- j = Job("TEST1", datetime.datetime.now()) > > > > ++ j = Job(1, datetime.datetime.now()) > > > > MSSQL (and most other db engines) are going to enforce type on the > > 'identifier' column. In the new code, it's an int, so...no strings > > allowed. The original example user "uniqueidentifier", which is a > > rather odd duck, and I'm not sure would support an arbitrary string > > as a key. Unless you need real GUID keys for some reason, I would > > suggest using a normal string or int surrogate key like the new > > example does. > > > how come no exception is thrown ? silent failure is the party pooper. > > > > > > > > --~--~-~--~~~---~--~~ 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
On Dec 10, 2007, at 12:47 PM, Rick Morrison wrote: > This works here on MSSQL/pymssql with a small change: > > -- j = Job("TEST1", datetime.datetime.now()) > > ++ j = Job(1, datetime.datetime.now()) > > MSSQL (and most other db engines) are going to enforce type on the > 'identifier' column. In the new code, it's an int, so...no strings > allowed. The original example user "uniqueidentifier", which is a > rather odd duck, and I'm not sure would support an arbitrary string > as a key. Unless you need real GUID keys for some reason, I would > suggest using a normal string or int surrogate key like the new > example does. > how come no exception is thrown ? silent failure is the party pooper. --~--~-~--~~~---~--~~ 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
This works here on MSSQL/pymssql with a small change: -- j = Job("TEST1", datetime.datetime.now()) ++ j = Job(1, datetime.datetime.now()) MSSQL (and most other db engines) are going to enforce type on the 'identifier' column. In the new code, it's an int, so...no strings allowed. The original example user "uniqueidentifier", which is a rather odd duck, and I'm not sure would support an arbitrary string as a key. Unless you need real GUID keys for some reason, I would suggest using a normal string or int surrogate key like the new example does. --~--~-~--~~~---~--~~ 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
On 10 Dic, 03:11, Michael Bayer <[EMAIL PROTECTED]> wrote: > I cant reproduce your problem, although i dont have access to MSSQL > here and there may be some issue on that end. Attached is your script > using an in-memory sqlite database, with the update inside of a while > loop, and it updates regularly.A few things to try on the MSSQL > side, if the issue is due to some typing issue, try not using > autoload=True, try using generic types instead of the MSSQL specific > ones, etc., in an effort to narrow down what might be the problem. I've redefined the table using only generic types: jobs = sa.Table('jobs', metadata, sa.Column('identifier', sa.VARCHAR(20), primary_key=True), sa.Column('section', sa.VARCHAR(20)), sa.Column("start",sa.DATETIME, primary_key=True), sa.Column("stop",sa.DATETIME), sa.Column("station", sa.VARCHAR(20)), autoload=False) and also autoload=False made no difference. I'll trying changing something else... > > also ive added "MSSQL/pyodbc" to the subject line here in case any of > the MSSQL crew wants to try out your script with pyodbc. Thanks. --~--~-~--~~~---~--~~ 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: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?
I cant reproduce your problem, although i dont have access to MSSQL here and there may be some issue on that end. Attached is your script using an in-memory sqlite database, with the update inside of a while loop, and it updates regularly.A few things to try on the MSSQL side, if the issue is due to some typing issue, try not using autoload=True, try using generic types instead of the MSSQL specific ones, etc., in an effort to narrow down what might be the problem. also ive added "MSSQL/pyodbc" to the subject line here in case any of the MSSQL crew wants to try out your script with pyodbc. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- import sqlalchemy as sa import datetime, time from sqlalchemy.orm import sessionmaker from sqlalchemy import * sa_engine=sa.create_engine("sqlite://",echo=True) metadata = sa.MetaData(sa_engine) Session = sessionmaker(bind=sa_engine, autoflush=True,transactional=True) sa_session = Session() jobs = sa.Table('jobs', metadata, sa.Column('identifier', Integer, primary_key=True), sa.Column('section', Integer), sa.Column("start",DateTime, primary_key=True), sa.Column("stop",DateTime), sa.Column("station", sa.VARCHAR(20))) metadata.create_all() class Job(object): def __init__(self, identifier, start): self.identifier, self.start=identifier, start sa.orm.mapper(Job, jobs) j = Job("TEST1", datetime.datetime.now()) sa_session.save(j) sa_session.commit() # The following part is here just to simluate my problem... if I keep using j instead of getting j1 from query # the record is updated as well while True: sa_session.clear() time.sleep(1) j1=sa_session.query(Job).all()[0] j1.stop=datetime.datetime.now() sa_session.save_or_update(j1) sa_session.commit() On Dec 9, 2007, at 5:26 PM, Smoke wrote: > > On 9 Dic, 21:37, Michael Bayer <[EMAIL PROTECTED]> wrote: >> theyre entirely supported. try to provide a fully working example >> illustrating the problem youre having. > > > > Here's a small example just to simulate the problem.. The last part of > this code is there just to simulate the problem... normally i would > just keep using j and update it... and this updates the record into > the db. But if I get an instance of the Job class from a query on the > db and try to update ( or save_or_update)it the record is not updated > into the db as well.. > > Here the sample code: > > > import sqlalchemy as sa > import datetime, time > from sqlalchemy.orm import sessionmaker > > sa_engine=sa.create_engine("mssql://user:[EMAIL PROTECTED]/myDB", > echo=True) > metadata = sa.MetaData(sa_engine) > Session = sessionmaker(bind=sa_engine, autoflush=True, > transactional=True) > sa_session = Session() > > > jobs = sa.Table('jobs', metadata, > sa.Column('identifier', > sa.databases.mssql.MSUniqueIdentifier, > primary_key=True), > sa.Column('section', > sa.databases.mssql.MSUniqueIdentifier), > > sa.Column("start",sa.databases.mssql.MSDateTime_pyodbc, > primary_key=True), > > sa.Column("stop",sa.databases.mssql.MSDateTime_pyodbc), > sa.Column("station", > sa.VARCHAR(20)), > autoload=True) > > class Job(object): > def __init__(self, identifier, start): > self.identifier, self.start=identifier, start > > sa.orm.mapper(Job, jobs) > > j = Job("TEST1", datetime.datetime.now()) > sa_session.save(j) > sa_session.commit() > # The following part is here just to simluate my problem... if I keep > using j instead of getting j1 from query > # the record is updated as well > sa_session.clear() > time.sleep(1) > j1=sa_session.query(Job).all()[0] > j1.stop=datetime.datetime.now() > sa_session.save_or_update(j1) > sa_session.commit() > --~--~-~--~~~---~--~~ > 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 > -~--~~~~--~~--~--~--- >