[sqlalchemy] Re: group_by and column labels
Michael Bayer wrote: On Jun 7, 2007, at 7:17 AM, Huy Do wrote: Michael Bayer wrote: put strings into group_by: group_by = [client_code, client_name, ...] Hi Michael, Sorry I'm not sure what you are suggesting. I don't really want to retype my column names again in the group_by clause. i was suggesting a workaround. just thought i might have missed something. thanks. My intention was to be able to reuse the actual column list in the group by clause. would be easier if you can wait for 0.4 on that. Not a problem. Just though it was a nice thing to have, because I have always hated retyping all those columns names when i need group by in my sql. Thanks Huy --~--~-~--~~~---~--~~ 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: group_by and column labels
Hi Rick, If this works, that is fine for me. I just don't want to retype my columns to avoid errors. I'll test and then send an update. Thanks Huy I think the .name property of a labled column holds the label, so how about db.job_table, db.client_table, db.service_type_table], group_by = [col.name http://col.name for col in group_by], On 6/7/07, *Michael Bayer* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: On Jun 7, 2007, at 7:17 AM, Huy Do wrote: Michael Bayer wrote: put strings into group_by: group_by = [client_code, client_name, ...] Hi Michael, Sorry I'm not sure what you are suggesting. I don't really want to retype my column names again in the group_by clause. i was suggesting a workaround. My intention was to be able to reuse the actual column list in the group by clause. would be easier if you can wait for 0.4 on that. --~--~-~--~~~---~--~~ 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: group_by and column labels
I think the .name property of a labled column holds the label, so how about db.job_table, db.client_table, db.service_type_table], group_by = [col.name http://col.name for col in group_by], This almost worked, but not quite. Using the name column, does give me the label, but it also only gives the column name, which is a problem when you have tables with the same column in the from clause (Ambiguous column error from the db server). The select column statement has table.column_name whilst the group by (using the method above) only has column_name or label_name. If i use use_labels option for the select statement, it works, because then the label names are unique across all columns. thanks Huy On 6/7/07, *Michael Bayer* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: On Jun 7, 2007, at 7:17 AM, Huy Do wrote: Michael Bayer wrote: put strings into group_by: group_by = [client_code, client_name, ...] Hi Michael, Sorry I'm not sure what you are suggesting. I don't really want to retype my column names again in the group_by clause. i was suggesting a workaround. My intention was to be able to reuse the actual column list in the group by clause. would be easier if you can wait for 0.4 on that. --~--~-~--~~~---~--~~ 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: autocloning vs returning self+explicit clone()
On Jun 8, 2007, at 9:54 AM, svilen wrote: i know this change is old, and the above SelectResults has been made to match Query() (cloning) behaviour, and whole SelectResults is abandoned, etc, but in this case the explicit is better than implicit would help to avoid such mess - if u wanna clone, please say it... i think the fact that theres a return value does imply its newits true that methods like list.append() in python dont return the list --~--~-~--~~~---~--~~ 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: sqlalchemy.exceptions.SQLError: (ProgrammingError) can't adapt
On Jun 8, 11:27 am, [EMAIL PROTECTED] [EMAIL PROTECTED]
wrote:
Hello. I am receiving the error:
sqlalchemy.exceptions.SQLError: (ProgrammingError) can't adapt 'INSERT
INTO workorderlines (workorderlines_rowid) VALUES (%
(workorderlines_rowid)s)' {'workorderlines_rowid':
Sequence('workorderlines_rowid_seq',start=None,increment=None,optional=False)}
running the following simplified version of what I am working with:
from sqlalchemy import *
db = create_engine('postgres://[EMAIL PROTECTED]:5432/fleettest')
db.echo = True
metadata = BoundMetaData(db)
workorderlines_table = Table('workorderlines', metadata,
Column('workorderlines_rowid', Numeric(10,0),
default=Sequence('workorderlines_rowid_seq')),
PrimaryKeyConstraint('workorderlines_rowid'),
)
the default keyword argument is for literal values or python
functions. to use Sequence:
t = Table('foo', metadata, Column('id', Integer,
Sequence('my_sequence'), primary_key=True))
the postgres dialect currently has the restriction that PK values must
go in as explicitly inserted values, as opposed to a default firing
off implicitly on the PG side. this is documented here:
http://www.sqlalchemy.org/docs/metadata.html#metadata_defaults_passive_postgres
--~--~-~--~~~---~--~~
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] remember that polymorphic_union function ?
I know ive put many folks through the wringer dealing with this
function, in order to get their inherited mappers to load instances
polymorphically. It seems like we probably dont need it for the most
common case, which is that you are using only joined-table
inheritance from a single common base table (i.e. no concrete-
inheritance), and have a specific column in your base table that
indicates the polymorphic type. if thats the case, you can forego
the usage of polymorphic_union and just string together your tables
using joins and outerjoins. Theres a slight tweak in the post 0.3.8
trunk with eager loading to get eager loading to a polymorphic
mapper to work with this case.
meaning, you can make mappers like this:
page_join = page_table.outerjoin(magazine_page_table).outerjoin
(classified_page_table)
magazine_join = page_table.join(magazine_page_table).outerjoin
(classified_page_table)
page_mapper = mapper(Page, page_table, select_table=page_join,
polymorphic_on=page_table.c.type, polymorphic_identity='p')
magazine_page_mapper = mapper(MagazinePage, magazine_page_table,
select_table=magazine_join, inherits=page_mapper,
polymorphic_identity='m')
classified_page_mapper = mapper(ClassifiedPage,
classified_page_table, inherits=magazine_page_mapper,
polymorphic_identity='c')
full example attached.
conclusions we can draw from this ? I am really, really dumb for not
seeing this for like, the past year. the queries are a lot easier to
create and read, plus we avoid all the other issues UNIONS bring
forth (like same column name but different types colliding, etc).
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
from sqlalchemy import *
metadata = BoundMetaData('sqlite://', echo=True)
page_table = Table('page', metadata,
Column('id', Integer, primary_key=True, default=None),
Column('page_no', Integer),
Column('type', CHAR(1)),
)
magazine_page_table = Table('magazine_page', metadata,
Column('page_id', Integer, ForeignKey('page.id'), primary_key=True),
Column('orders', String(45)),
)
classified_page_table = Table('classified_page', metadata,
Column('magazine_page_id', Integer, ForeignKey('magazine_page.page_id'), primary_key=True),
Column('titles', String(45)),
)
metadata.create_all()
page_join = page_table.outerjoin(magazine_page_table).outerjoin(classified_page_table)
magazine_join = page_table.join(magazine_page_table).outerjoin(classified_page_table)
class Page(object):
def __init__(self, **kwargs):
for key, value in kwargs.iteritems():
setattr(self, key, value)
def __repr__(self):
return %s(%s) % (self.__class__.__name__, ','.join([%s=%s % (k, repr(v)) for k, v in self.__dict__.iteritems() if k[0] != '_']))
class MagazinePage(Page):pass
class ClassifiedPage(MagazinePage):pass
page_mapper = mapper(Page, page_table, select_table=page_join, polymorphic_on=page_table.c.type, polymorphic_identity='p')
magazine_page_mapper = mapper(MagazinePage, magazine_page_table, select_table=magazine_join, inherits=page_mapper, polymorphic_identity='m')
classified_page_mapper = mapper(ClassifiedPage, classified_page_table, inherits=magazine_page_mapper, polymorphic_identity='c')
sess = create_session()
objs = [Page(page_no=5), MagazinePage(page_no=6, orders='some text'), ClassifiedPage(page_no=7, orders='some other text', titles='classified titles')]
for o in objs:
sess.save(o)
sess.flush()
sess.clear()
print sess.query(Page).list()
print sess.query(MagazinePage).list()
[sqlalchemy] sqlalchemy.exceptions.SQLError: (ProgrammingError) can't adapt
Hello. I am receiving the error:
sqlalchemy.exceptions.SQLError: (ProgrammingError) can't adapt 'INSERT
INTO workorderlines (workorderlines_rowid) VALUES (%
(workorderlines_rowid)s)' {'workorderlines_rowid':
Sequence('workorderlines_rowid_seq',start=None,increment=None,optional=False)}
running the following simplified version of what I am working with:
from sqlalchemy import *
db = create_engine('postgres://[EMAIL PROTECTED]:5432/fleettest')
db.echo = True
metadata = BoundMetaData(db)
workorderlines_table = Table('workorderlines', metadata,
Column('workorderlines_rowid', Numeric(10,0),
default=Sequence('workorderlines_rowid_seq')),
PrimaryKeyConstraint('workorderlines_rowid'),
)
class Workorder_Line(object):
def __repr__(self):
return Workorder_Line: %d %d %d %d%s % (
self.company, self.store, self.workorder, self.line,
self.suffix)
mapper(Workorder_Line, workorderlines_table)
def main():
session = create_session()
obj = Workorder_Line()
session.save(obj)
session.flush()
if __name__ == '__main__': main()
Primarily, I have a postgres database with a sequence setup as a
default on the workorderlines_rowid column within the database. If I
try to write out a record without setting the workorderlines_rowid
value or without specifying a default, the SQL tries to insert it with
a NULL value. Since I couldn't figure out how to disable that, I have
tried linking a sqlalchemy default by either explicity specifying the
database sequence as above, or by using PassiveDefault to specify
DEFAULT, but in either case, I get the above error. Is there a way
to stop sqlalchemy from trying to insert a value for a column I
haven't specified a value for? Is something wrong with my sequence
specification?
--~--~-~--~~~---~--~~
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: sqlalchemy.exceptions.SQLError: (ProgrammingError) can't adapt
Hmmm. Perhaps I oversimplified my code for the example. The
workorderlines_rowid isn't actually a primary key; given the following
code where it is not one:
from sqlalchemy import *
db = create_engine('postgres://[EMAIL PROTECTED]:5432/fleettest')
db.echo = True
metadata = BoundMetaData(db)
workorderlines_table = Table('workorderlines', metadata,
Column('company', Integer, nullable=False, autoincrement=False),
Column('store', Integer, nullable=False, autoincrement=False),
Column('barcode', Numeric(10,0)),
Column('workorder', Integer, nullable=False, autoincrement=False),
Column('line', Integer, nullable=False, autoincrement=False),
Column('suffix', Unicode(1), nullable=False, default=''),
Column('workorderlines_rowid', Numeric(10,0),
default=Sequence('workorderlines_rowid_seq')),
PrimaryKeyConstraint('company', 'store', 'workorder', 'line',
'suffix'),
)
class Workorder_Line(object):
def __repr__(self):
return Workorder_Line: %d %d %d %d%s % (
self.company, self.store, self.workorder, self.line,
self.suffix)
mapper(Workorder_Line, workorderlines_table)
def main():
session = create_session()
obj = Workorder_Line()
session.save(obj)
session.flush()
if __name__ == '__main__': main()
sqlalchemy.exceptions.SQLError: (ProgrammingError) can't adapt 'INSERT
INTO workorderlines (company, store, barcode, workorder, line, suffix,
workorderlines_rowid) VALUES (%(company)s, %(store)s, %(barcode)s, %
(workorder)s, %(line)s, %(suffix)s, %
(workorderlines_rowid)s)' {'suffix': '', 'workorderlines_rowid':
Sequence('workorderlines_rowid_seq',start=None,increment=None,optional=False),
'company': None, 'barcode': None, 'line': None, 'workorder': None,
'store': None}
is the error given. I'd expect to get an error on not having
specified values for the primary key fields, but it appears instead to
be confused with the sequence object it's getting for the
workorderlines_rowid field.
On Jun 8, 11:49 am, Michael Bayer [EMAIL PROTECTED] wrote:
On Jun 8, 11:27 am, [EMAIL PROTECTED] [EMAIL PROTECTED]
wrote:
Hello. I am receiving the error:
sqlalchemy.exceptions.SQLError: (ProgrammingError) can't adapt 'INSERT
INTO workorderlines (workorderlines_rowid) VALUES (%
(workorderlines_rowid)s)' {'workorderlines_rowid':
Sequence('workorderlines_rowid_seq',start=None,increment=None,optional=False)}
running the following simplified version of what I am working with:
from sqlalchemy import *
db = create_engine('postgres://[EMAIL PROTECTED]:5432/fleettest')
db.echo = True
metadata = BoundMetaData(db)
workorderlines_table = Table('workorderlines', metadata,
Column('workorderlines_rowid', Numeric(10,0),
default=Sequence('workorderlines_rowid_seq')),
PrimaryKeyConstraint('workorderlines_rowid'),
)
the default keyword argument is for literal values or python
functions. to use Sequence:
t = Table('foo', metadata, Column('id', Integer,
Sequence('my_sequence'), primary_key=True))
the postgres dialect currently has the restriction that PK values must
go in as explicitly inserted values, as opposed to a default firing
off implicitly on the PG side. this is documented
here:http://www.sqlalchemy.org/docs/metadata.html#metadata_defaults_passiv...
--~--~-~--~~~---~--~~
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: sqlalchemy.exceptions.SQLError: (ProgrammingError) can't adapt
this syntax, from your example:
Column('workorderlines_rowid', Numeric(10,0),
default=Sequence('workorderlines_rowid_seq')),
is wrong. that is why you are getting this:
VALUES (%(company)s, %(store)s, %(barcode)s, %
(workorder)s, %(line)s, %(suffix)s, %
(workorderlines_rowid)s)' {'suffix': '', 'workorderlines_rowid':
Sequence
('workorderlines_rowid_seq',start=None,increment=None,optional=False),
notice the Sequence schema object shoved into your bind parameters.
the correct syntax is:
Column('workorderlines_rowid', Numeric(10,0),
Sequence('workorderlines_rowid_seq')),
however, since its not a primary key column, you can forego putting
the explicit Sequence there; technically you dont need to put any
kind of default anything on it.
if you'd like the object-relational-mapper specifically to post-
fetch the row after the default has been applied, you probably want
to specify it at least as:
Column('workorderlines_rowid', PassiveDefault(), Numeric(10,0)),
where the PassiveDefault indicates to the ORM that a default fired
off on the DB side and should be fetched. the contents of the
PassiveDefault shouldnt matter since you arent issuing CREATE TABLE
statements.
On Jun 8, 2007, at 12:17 PM, A. Grossman wrote:
Hmmm. Perhaps I oversimplified my code for the example. The
workorderlines_rowid isn't actually a primary key; given the following
code where it is not one:
from sqlalchemy import *
db = create_engine('postgres://[EMAIL PROTECTED]:5432/fleettest')
db.echo = True
metadata = BoundMetaData(db)
workorderlines_table = Table('workorderlines', metadata,
Column('company', Integer, nullable=False, autoincrement=False),
Column('store', Integer, nullable=False, autoincrement=False),
Column('barcode', Numeric(10,0)),
Column('workorder', Integer, nullable=False, autoincrement=False),
Column('line', Integer, nullable=False, autoincrement=False),
Column('suffix', Unicode(1), nullable=False, default=''),
Column('workorderlines_rowid', Numeric(10,0),
default=Sequence('workorderlines_rowid_seq')),
PrimaryKeyConstraint('company', 'store', 'workorder', 'line',
'suffix'),
)
class Workorder_Line(object):
def __repr__(self):
return Workorder_Line: %d %d %d %d%s % (
self.company, self.store, self.workorder, self.line,
self.suffix)
mapper(Workorder_Line, workorderlines_table)
def main():
session = create_session()
obj = Workorder_Line()
session.save(obj)
session.flush()
if __name__ == '__main__': main()
sqlalchemy.exceptions.SQLError: (ProgrammingError) can't adapt 'INSERT
INTO workorderlines (company, store, barcode, workorder, line, suffix,
workorderlines_rowid) VALUES (%(company)s, %(store)s, %(barcode)s, %
(workorder)s, %(line)s, %(suffix)s, %
(workorderlines_rowid)s)' {'suffix': '', 'workorderlines_rowid':
Sequence
('workorderlines_rowid_seq',start=None,increment=None,optional=False),
'company': None, 'barcode': None, 'line': None, 'workorder': None,
'store': None}
is the error given. I'd expect to get an error on not having
specified values for the primary key fields, but it appears instead to
be confused with the sequence object it's getting for the
workorderlines_rowid field.
On Jun 8, 11:49 am, Michael Bayer [EMAIL PROTECTED] wrote:
On Jun 8, 11:27 am, [EMAIL PROTECTED] [EMAIL PROTECTED]
wrote:
Hello. I am receiving the error:
sqlalchemy.exceptions.SQLError: (ProgrammingError) can't adapt
'INSERT
INTO workorderlines (workorderlines_rowid) VALUES (%
(workorderlines_rowid)s)' {'workorderlines_rowid':
Sequence
('workorderlines_rowid_seq',start=None,increment=None,optional=False
)}
running the following simplified version of what I am working with:
from sqlalchemy import *
db = create_engine('postgres://[EMAIL PROTECTED]:5432/fleettest')
db.echo = True
metadata = BoundMetaData(db)
workorderlines_table = Table('workorderlines', metadata,
Column('workorderlines_rowid', Numeric(10,0),
default=Sequence('workorderlines_rowid_seq')),
PrimaryKeyConstraint('workorderlines_rowid'),
)
the default keyword argument is for literal values or python
functions. to use Sequence:
t = Table('foo', metadata, Column('id', Integer,
Sequence('my_sequence'), primary_key=True))
the postgres dialect currently has the restriction that PK values
must
go in as explicitly inserted values, as opposed to a default firing
off implicitly on the PG side. this is documented here:http://
www.sqlalchemy.org/docs/metadata.html#metadata_defaults_passiv...
--~--~-~--~~~---~--~~
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] [MySQL] Checking if commit() is available
is there a way to determine if the underlying MySQL DB is able to perform a commit() operation? The following code fails (likely because the underlying MySQL db is pretty old, V3ish). Andreas -- dsn = 'mysql://' e = create_engine(dsn) c=e.connect() t = c.begin() t.commit() Traceback (most recent call last): File stdin, line 1, in ? File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line 675, in commit File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line 488, in _commit_impl sqlalchemy.exceptions.SQLError: (ProgrammingError) (1064, You have an error in your SQL syntax near 'commit' at line 1) None None -- ZOPYX Ltd. Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376 Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535 Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK E-Publishing, Python, Zope Plone development, Consulting pgpVDItFEqcMO.pgp Description: PGP signature
[sqlalchemy] Re: [MySQL] Checking if commit() is available
try: t.commit() except: print 'Holy cow, this database is lame' On 6/8/07, Andreas Jung [EMAIL PROTECTED] wrote: is there a way to determine if the underlying MySQL DB is able to perform a commit() operation? The following code fails (likely because the underlying MySQL db is pretty old, V3ish). Andreas -- dsn = 'mysql://' e = create_engine(dsn) c=e.connect() t = c.begin() t.commit() Traceback (most recent call last): File stdin, line 1, in ? File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line 675, in commit File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line 488, in _commit_impl sqlalchemy.exceptions.SQLError: (ProgrammingError) (1064, You have an error in your SQL syntax near 'commit' at line 1) None None -- ZOPYX Ltd. Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany Web: www.zopyx.com - Email: [EMAIL PROTECTED] - Phone +49 - 7071 - 793376 Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535 Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK E-Publishing, Python, Zope Plone development, Consulting --~--~-~--~~~---~--~~ 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: sqlalchemy.exceptions.SQLError: (ProgrammingError) can't adapt
Ahhh, PassiveDefault in that way worked. Excellent. Thank you.
On Jun 8, 1:43 pm, Michael Bayer [EMAIL PROTECTED] wrote:
this syntax, from your example:
Column('workorderlines_rowid', Numeric(10,0),
default=Sequence('workorderlines_rowid_seq')),
is wrong. that is why you are getting this:
VALUES (%(company)s, %(store)s, %(barcode)s, %
(workorder)s, %(line)s, %(suffix)s, %
(workorderlines_rowid)s)' {'suffix': '', 'workorderlines_rowid':
Sequence
('workorderlines_rowid_seq',start=None,increment=None,optional=False),
notice the Sequence schema object shoved into your bind parameters.
the correct syntax is:
Column('workorderlines_rowid', Numeric(10,0),
Sequence('workorderlines_rowid_seq')),
however, since its not a primary key column, you can forego putting
the explicit Sequence there; technically you dont need to put any
kind of default anything on it.
if you'd like the object-relational-mapper specifically to post-
fetch the row after the default has been applied, you probably want
to specify it at least as:
Column('workorderlines_rowid', PassiveDefault(), Numeric(10,0)),
where the PassiveDefault indicates to the ORM that a default fired
off on the DB side and should be fetched. the contents of the
PassiveDefault shouldnt matter since you arent issuing CREATE TABLE
statements.
On Jun 8, 2007, at 12:17 PM, A. Grossman wrote:
Hmmm. Perhaps I oversimplified my code for the example. The
workorderlines_rowid isn't actually a primary key; given the following
code where it is not one:
from sqlalchemy import *
db = create_engine('postgres://[EMAIL PROTECTED]:5432/fleettest')
db.echo = True
metadata = BoundMetaData(db)
workorderlines_table = Table('workorderlines', metadata,
Column('company', Integer, nullable=False, autoincrement=False),
Column('store', Integer, nullable=False, autoincrement=False),
Column('barcode', Numeric(10,0)),
Column('workorder', Integer, nullable=False, autoincrement=False),
Column('line', Integer, nullable=False, autoincrement=False),
Column('suffix', Unicode(1), nullable=False, default=''),
Column('workorderlines_rowid', Numeric(10,0),
default=Sequence('workorderlines_rowid_seq')),
PrimaryKeyConstraint('company', 'store', 'workorder', 'line',
'suffix'),
)
class Workorder_Line(object):
def __repr__(self):
return Workorder_Line: %d %d %d %d%s % (
self.company, self.store, self.workorder, self.line,
self.suffix)
mapper(Workorder_Line, workorderlines_table)
def main():
session = create_session()
obj = Workorder_Line()
session.save(obj)
session.flush()
if __name__ == '__main__': main()
sqlalchemy.exceptions.SQLError: (ProgrammingError) can't adapt 'INSERT
INTO workorderlines (company, store, barcode, workorder, line, suffix,
workorderlines_rowid) VALUES (%(company)s, %(store)s, %(barcode)s, %
(workorder)s, %(line)s, %(suffix)s, %
(workorderlines_rowid)s)' {'suffix': '', 'workorderlines_rowid':
Sequence
('workorderlines_rowid_seq',start=None,increment=None,optional=False),
'company': None, 'barcode': None, 'line': None, 'workorder': None,
'store': None}
is the error given. I'd expect to get an error on not having
specified values for the primary key fields, but it appears instead to
be confused with the sequence object it's getting for the
workorderlines_rowid field.
On Jun 8, 11:49 am, Michael Bayer [EMAIL PROTECTED] wrote:
On Jun 8, 11:27 am, [EMAIL PROTECTED] [EMAIL PROTECTED]
wrote:
Hello. I am receiving the error:
sqlalchemy.exceptions.SQLError: (ProgrammingError) can't adapt
'INSERT
INTO workorderlines (workorderlines_rowid) VALUES (%
(workorderlines_rowid)s)' {'workorderlines_rowid':
Sequence
('workorderlines_rowid_seq',start=None,increment=None,optional=False
)}
running the following simplified version of what I am working with:
from sqlalchemy import *
db = create_engine('postgres://[EMAIL PROTECTED]:5432/fleettest')
db.echo = True
metadata = BoundMetaData(db)
workorderlines_table = Table('workorderlines', metadata,
Column('workorderlines_rowid', Numeric(10,0),
default=Sequence('workorderlines_rowid_seq')),
PrimaryKeyConstraint('workorderlines_rowid'),
)
the default keyword argument is for literal values or python
functions. to use Sequence:
t = Table('foo', metadata, Column('id', Integer,
Sequence('my_sequence'), primary_key=True))
the postgres dialect currently has the restriction that PK values
must
go in as explicitly inserted values, as opposed to a default firing
off implicitly on the PG side. this is documented here:http://
www.sqlalchemy.org/docs/metadata.html#metadata_defaults_passiv...
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to
[sqlalchemy] Re: [MySQL] Checking if commit() is available
--On 8. Juni 2007 14:05:39 -0400 Rick Morrison [EMAIL PROTECTED] wrote: try: t.commit() except: print 'Holy cow, this database is lame' This code is also lame :-) The code should work for arbitrary DSNs and swallowing an exception while committing is evil, evil, evil. -aj pgpZ2vlCZO15d.pgp Description: PGP signature
[sqlalchemy] Re: [MySQL] Checking if commit() is available
Well it's not so evil if all you're doing is testing to see if commit() is available -- I wasn't trying to suggest that it was a great pattern for your whole application. On 6/8/07, Andreas Jung [EMAIL PROTECTED] wrote: --On 8. Juni 2007 14:05:39 -0400 Rick Morrison [EMAIL PROTECTED] wrote: try: t.commit() except: print 'Holy cow, this database is lame' This code is also lame :-) The code should work for arbitrary DSNs and swallowing an exception while committing is evil, evil, evil. -aj --~--~-~--~~~---~--~~ 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: sqlalchemy.exceptions.SQLError: (ProgrammingError) can't adapt
On Jun 8, 2007, at 2:10 PM, A. Grossman wrote: Ahhh, PassiveDefault in that way worked. Excellent. Thank you. glad that worked. --~--~-~--~~~---~--~~ 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: [MySQL] Checking if commit() is available
On Jun 8, 2007, at 2:17 PM, Andreas Jung wrote: --On 8. Juni 2007 14:05:39 -0400 Rick Morrison [EMAIL PROTECTED] wrote: try: t.commit() except: print 'Holy cow, this database is lame' This code is also lame :-) The code should work for arbitrary DSNs and swallowing an exception while committing is evil, evil, evil. with a pre-5 version of mysql, it is the lesser evil --~--~-~--~~~---~--~~ 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: SA and pyodbc connections
Hi, For me, at least, solid and passes all tests are not necessarily the same thing This depends somewhat on your definition of solid. In terms of volumns, I think you're absolutely right, if your basic operation works, you can process millions of rows and it doesn't matter that other unit tests fail. In terms of all the features working I've found the unit tests much more important. Over the last six months I've built quite a complicated app that using SQLAlchemy on MSSQL. What I found early on was that I'd often hit SA bugs as I coded up features. The unpredictability was quite a nightmare, and this is what motivated me to get all the unit tests passing. I've found that most of the failing tests are fixable problems. MSSQL has a few quirks, the IDENTITY columns (they're just like AUTONUMBER) being the main culprit. Just about the only feature it doesn't support is LIMIT with an offset; everything else works. And I've recently realised that this behaviour could be emulated by seeking the cursor. And with pyODBC and server-side cursors, this would be reasonably efficient. Looking forward, my work on the MSSQL module is probably getting towards the end. With Michael Jahn's work on fixing the scope_identity() issue looking very promising, that only leaves two bugs I know about. Once those are fixed, I'm hoping we can have a 0.3.9 release and finally drop the experimental status. And one last thing, a little nuge for Rick, can you commit my patches in ticket #581 please. :-) Take care, Paul The issue I think likes more with the test suite than with MSSQL or any of its connectors, and I just haven't really found the time to dig into the underlying reasons, as the basics all work for me -- I use pymssql with SA in a commercial application that processes millions of rows per day with some really complex SQL, mixing ORM with SQL layer operations, mixing transactions between ORM and the SQL layer, calling database functions and stored procedures and working with lots of different datatypes, and the thing works fine. It runs under Pylons with Paste, and stays up for weeks at a time. It's stable. It's solid. And yes, it fails unit tests. Writing a comprehensive test suite for like six different database engines is really, really difficult, because of all the various feature sets involved. Trying to run that test suite on a database that was more or less a late entry to the game, with three (count em!) DB-API access modules, each with their own sets of annoying problems, and then add the issue of crossing the Unix - WIndow DMZ, and I'm not surprised we get test failures. I'll be working on getting pyodbc stable and supported on Unix over the next weeks and months, but it's going to be a slow process that's going to involve more adjusting of the tests than fixes to the MSSQL module. In a fast-changing library like SA, that's difficult because test changes are likely to break other databases and annoy the author ;-) What will help, at least a little is a buildbot that will run the tests on Unix across a range of databases. I'm setting that up to run from the Pylons buildbot master, which will watch SQLalchemy trunk checkins. This way, when you make changes for Windows, we can see their effects for Unix within a few minutes on all databases. The other thing that's going to have to happen is a pretty big effort to straighten out dependancies on database feature sets in the unit tests, and factor those out so we don't see spurious failures. So, if your acceptance criteria for SA + MSSQL is going to be passes all unit tests, then it's going to be a while before that happens. But we will get there eventually. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
