[sqlalchemy] Re: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?

[Smoke]

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 ?

[Rick Morrison]

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 ?

[Smoke]

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 ?

[Smoke]

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 ?

[Michael Bayer]

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 ?

[Rick Morrison]

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 ?

[Rick Morrison]

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 ?

[Smoke]

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 ?

[Smoke]

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 ?

[Rick Morrison]

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 ?

[Rick Morrison]

 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.

/me faces toward UK, where it's about midnight right now...
/me yells HEY PAUL!! YOU WATCHING THIS THREAD??

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 ?

[Paul Johnston]

Hi,

 /me faces toward UK, where it's about midnight right now...
 /me yells HEY PAUL!! YOU WATCHING THIS THREAD??

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...

[Michael Bayer]

On Dec 9, 2007, at 2:54 PM, Smoke wrote:

 When i create and save a j = Job(identifier, start), I have no
 problems and it saves the new record on the table, but when i want to
 update ( update or save_or_update ) the record with the stop time i
 just don't update the record... It does not throw any new
 exception  I've also tryied to change the table definition putting
 the primary_key on both columns definition instead of using
 PrimaryKeyConstraint ( as you can see by the comments.. ) but the
 result is the same...
 Am I missing something? Or maybe composite primary keys tables are not
 supported for updating using the session ?


theyre entirely supported.  try to provide a fully working example  
illustrating the problem youre having.

--~--~-~--~~~---~--~~
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...

[Smoke]

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
-~--~~~~--~~--~--~---

[sqlalchemy] Re: save_or_update and composit Primary Keys... MSSQL / pyodbc issue ?

[Michael Bayer]

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
 -~--~~~~--~~--~--~---