[sqlalchemy] Re: Creating a custom Visitable
I figured that I need to extend a dialect. Would having something
similar to (still a rough version) added to the postgresql dialect be
an option?:
def post_create_table(self, table):
Build table-level CREATE options like TABLESPACE.
table_opts = []
inherits = table.kwargs.get('postgresql_inherits')
if inherits is not None:
if not isinstance(inherits, (list, tuple)):
inherits = (inherits,)
table_opts.append(
'\nINHERITS ( ' +
', '.join(isinstance(i, basestring) and i
or self.process(i)
for i
in inherits) +
' )')
on_commit = table.kwargs.get('postgresql_on_commit')
if on_commit:
table_opts.append(
'\nON COMMIT ' +
on_commit.upper().replace('_', ' '))
with_oids = table.kwargs.get('postgresql_with_oids')
if with_oids is not None:
if with_oids:
w = 'WITH'
else:
w = 'WITHOUT'
table_opts.append('\n%s OIDS' % w)
tablespace = table.kwargs.get('postgresql_tablespace')
if tablespace:
table_opts.append('\nTABLESPACE ' + tablespace)
return ''.join(table_opts)
Thanks,
Mike
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
I'm clear on that. I'm only using session.add to do an insert when I
know i definitely want to. But you'll see I used session.merge on the
composite object, yet it still attempts to do an insert for rows that
already exist in its constituent tables...
On Mon, Jul 19, 2010 at 5:19 PM, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 19, 2010, at 12:04 PM, Harry Percival wrote:
Michael, thanks, as ever, for your help.
So, I think I've managed to specify the relationships:
j = join(movies_table,md_table).join(directors_table).join(genres_table)
js = j.select(use_labels=True).alias('mdg')
r0 = relationship(Movies,
primaryjoin=(js.c.movies_movie_id==movies_table.c.movie_id),
foreign_keys=([js.c.movies_movie_id])
)
r1 = relationship(Directors,
primaryjoin=(js.c.directors_director_id==directors_table.c.director_id),
foreign_keys=([js.c.directors_director_id])
)
r2 = relationship(Genres,
primaryjoin=(js.c.genres_genre_id==genres_table.c.genre_id),
foreign_keys=([js.c.genres_genre_id])
)
mapper(MoviesAndDirectorsAndGenres,
js,
properties={'movie':r0,
'director':r1,
'genre':r2
},
passive_updates=False)
To test it, I'm trying to create a new composite object, based on a
new movie but existing director genre:
session=Session()
m_new = Movies()
m_new.id=8
m_new.title = 'new movie'
session.add(m_new)
d2 = session.query(Directors).get(2)
print '***director=',d2
g6 = session.query(Genres).get(6)
print '***genre=',g6
oo_new = MoviesAndDirectorsAndGenres()
oo_new.movie = m_new
oo_new.director = d2
oo_new.genre = g6
try:
# another_new = session.merge(new)
session.merge(oo_new)
but sqla is doing some really weird stuff:
2010-07-19 16:56:01,877 INFO sqlalchemy.engine.base.Engine.0x...37f0 BEGIN
2010-07-19 16:56:01,878 INFO sqlalchemy.engine.base.Engine.0x...37f0 INSERT
INTO
movies (movie_id, title, description, genre_id, release_date) VALUES
(?, ?, ?,?, ?)
2010-07-19 16:56:01,880 INFO sqlalchemy.engine.base.Engine.0x...37f0
(8, 'new movie', None, None, None)
2010-07-19 16:56:01,882 INFO sqlalchemy.engine.base.Engine.0x...37f0
SELECT directors.director_id AS directors_director_id, directors.name
AS directors_name
FROM directors
WHERE directors.director_id = ?
2010-07-19 16:56:01,885 INFO sqlalchemy.engine.base.Engine.0x...37f0 (2,)
***director= test_multitable.Directors object at 0x021E2030
2010-07-19 16:56:01,888 INFO sqlalchemy.engine.base.Engine.0x...37f0
SELECT genres.genre_id AS genres_genre_id, genres.name AS genres_name,
genres.description AS genres_description
FROM genres
WHERE genres.genre_id = ?
2010-07-19 16:56:01,891 INFO sqlalchemy.engine.base.Engine.0x...37f0 (6,)
***genre= test_multitable.Genres object at 0x021E21B0
2010-07-19 16:56:01,894 INFO sqlalchemy.engine.base.Engine.0x...37f0
INSERT INTO genres (genre_id, name, description) VALUES (?, ?, ?)
2010-07-19 16:56:01,895 INFO sqlalchemy.engine.base.Engine.0x...37f0
(6, None, None)
2010-07-19 16:56:01,898 INFO sqlalchemy.engine.base.Engine.0x...37f0 ROLLBACK
attempted to add new composite object, failed with (IntegrityError)
PRIMARY KEY must be unique u'INSERT INTO genres (genre_id, name,
description) VALUES (?, ?, ?)' (6, None, None)
Traceback (most recent call last):
File test_multitable.py, line 105, in module
session.commit()
File sqlalchemy\orm\session.py, line 653, in commit
etc etc
IntegrityError: (IntegrityError) PRIMARY KEY must be unique u'INSERT INTO
genres
(genre_id, name, description) VALUES (?, ?, ?)' (6, None, None)
it seems to trying to insert a new genre, even though I've told it to
use an existing one?
I suppose, once I've done the insert into the movies table, i could
retrieve the new composite object via a session.query, instead of
trying to do a session.merge. but this wouldn't work for creating a
new Director object, since I also need to create an entry into the
bridging table, something i'm hoping sqla could manage for me...
if you say x = new Foo() x.id = 7; session.add(x); that is an INSERT, no
matter what the ID is, and will fail as above if that primary key already
exists. If you OTOH say x = session.merge(x), it will be an INSERT or an
UPDATE depending on whether or not primary key id #7 exists in the database
already or not.
You can get an overview of what the various methods do at:
http://www.sqlalchemy.org/docs/session.html#id1
rgds,
hp
On Mon, Jul 19, 2010 at 3:31 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Jul 19, 2010, at 10:24 AM, Harry Percival wrote:
OK, so I will treat any classes mapped to a join of multiple tables as
being a read-only API, and manually manage the write-API using
relationship().
Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
On Jul 20, 2010, at 6:05 AM, Harry Percival wrote:
I'm clear on that. I'm only using session.add to do an insert when I
know i definitely want to. But you'll see I used session.merge on the
composite object, yet it still attempts to do an insert for rows that
already exist in its constituent tables...
you are setting a primary key, and adding right here:
m_new = Movies()
m_new.id=8
m_new.title = 'new movie'
session.add(m_new)
here is your SQL statement, attempting to INSERT id #8:
INSERT INTO
movies (movie_id, title, description, genre_id, release_date) VALUES
(?, ?, ?,?, ?)
2010-07-19 16:56:01,880 INFO sqlalchemy.engine.base.Engine.0x...37f0
(8, 'new movie', None, None, None)
On Mon, Jul 19, 2010 at 5:19 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Jul 19, 2010, at 12:04 PM, Harry Percival wrote:
Michael, thanks, as ever, for your help.
So, I think I've managed to specify the relationships:
j = join(movies_table,md_table).join(directors_table).join(genres_table)
js = j.select(use_labels=True).alias('mdg')
r0 = relationship(Movies,
primaryjoin=(js.c.movies_movie_id==movies_table.c.movie_id),
foreign_keys=([js.c.movies_movie_id])
)
r1 = relationship(Directors,
primaryjoin=(js.c.directors_director_id==directors_table.c.director_id),
foreign_keys=([js.c.directors_director_id])
)
r2 = relationship(Genres,
primaryjoin=(js.c.genres_genre_id==genres_table.c.genre_id),
foreign_keys=([js.c.genres_genre_id])
)
mapper(MoviesAndDirectorsAndGenres,
js,
properties={'movie':r0,
'director':r1,
'genre':r2
},
passive_updates=False)
To test it, I'm trying to create a new composite object, based on a
new movie but existing director genre:
session=Session()
m_new = Movies()
m_new.id=8
m_new.title = 'new movie'
session.add(m_new)
d2 = session.query(Directors).get(2)
print '***director=',d2
g6 = session.query(Genres).get(6)
print '***genre=',g6
oo_new = MoviesAndDirectorsAndGenres()
oo_new.movie = m_new
oo_new.director = d2
oo_new.genre = g6
try:
#another_new = session.merge(new)
session.merge(oo_new)
but sqla is doing some really weird stuff:
2010-07-19 16:56:01,877 INFO sqlalchemy.engine.base.Engine.0x...37f0 BEGIN
2010-07-19 16:56:01,878 INFO sqlalchemy.engine.base.Engine.0x...37f0 INSERT
INTO
movies (movie_id, title, description, genre_id, release_date) VALUES
(?, ?, ?,?, ?)
2010-07-19 16:56:01,880 INFO sqlalchemy.engine.base.Engine.0x...37f0
(8, 'new movie', None, None, None)
2010-07-19 16:56:01,882 INFO sqlalchemy.engine.base.Engine.0x...37f0
SELECT directors.director_id AS directors_director_id, directors.name
AS directors_name
FROM directors
WHERE directors.director_id = ?
2010-07-19 16:56:01,885 INFO sqlalchemy.engine.base.Engine.0x...37f0 (2,)
***director= test_multitable.Directors object at 0x021E2030
2010-07-19 16:56:01,888 INFO sqlalchemy.engine.base.Engine.0x...37f0
SELECT genres.genre_id AS genres_genre_id, genres.name AS genres_name,
genres.description AS genres_description
FROM genres
WHERE genres.genre_id = ?
2010-07-19 16:56:01,891 INFO sqlalchemy.engine.base.Engine.0x...37f0 (6,)
***genre= test_multitable.Genres object at 0x021E21B0
2010-07-19 16:56:01,894 INFO sqlalchemy.engine.base.Engine.0x...37f0
INSERT INTO genres (genre_id, name, description) VALUES (?, ?, ?)
2010-07-19 16:56:01,895 INFO sqlalchemy.engine.base.Engine.0x...37f0
(6, None, None)
2010-07-19 16:56:01,898 INFO sqlalchemy.engine.base.Engine.0x...37f0
ROLLBACK
attempted to add new composite object, failed with (IntegrityError)
PRIMARY KEY must be unique u'INSERT INTO genres (genre_id, name,
description) VALUES (?, ?, ?)' (6, None, None)
Traceback (most recent call last):
File test_multitable.py, line 105, in module
session.commit()
File sqlalchemy\orm\session.py, line 653, in commit
etc etc
IntegrityError: (IntegrityError) PRIMARY KEY must be unique u'INSERT INTO
genres
(genre_id, name, description) VALUES (?, ?, ?)' (6, None, None)
it seems to trying to insert a new genre, even though I've told it to
use an existing one?
I suppose, once I've done the insert into the movies table, i could
retrieve the new composite object via a session.query, instead of
trying to do a session.merge. but this wouldn't work for creating a
new Director object, since I also need to create an entry into the
bridging table, something i'm hoping sqla could manage for me...
if you say x = new Foo() x.id = 7; session.add(x); that is an INSERT, no
matter what the ID is, and will fail as above if that primary key already
exists. If you OTOH say x = session.merge(x), it will be an INSERT or an
UPDATE depending
Re: [sqlalchemy] Re: Creating a custom Visitable
why not use the compiler extension ?
On Jul 20, 2010, at 3:07 AM, Mike Lewis wrote:
I figured that I need to extend a dialect. Would having something
similar to (still a rough version) added to the postgresql dialect be
an option?:
def post_create_table(self, table):
Build table-level CREATE options like TABLESPACE.
table_opts = []
inherits = table.kwargs.get('postgresql_inherits')
if inherits is not None:
if not isinstance(inherits, (list, tuple)):
inherits = (inherits,)
table_opts.append(
'\nINHERITS ( ' +
', '.join(isinstance(i, basestring) and i
or self.process(i)
for i
in inherits) +
' )')
on_commit = table.kwargs.get('postgresql_on_commit')
if on_commit:
table_opts.append(
'\nON COMMIT ' +
on_commit.upper().replace('_', ' '))
with_oids = table.kwargs.get('postgresql_with_oids')
if with_oids is not None:
if with_oids:
w = 'WITH'
else:
w = 'WITHOUT'
table_opts.append('\n%s OIDS' % w)
tablespace = table.kwargs.get('postgresql_tablespace')
if tablespace:
table_opts.append('\nTABLESPACE ' + tablespace)
return ''.join(table_opts)
Thanks,
Mike
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Using the declarative base across projects
Hi,
Just wanted to say thanks to those who helped me with this. Simon's solution
was exactly what I was looking for (though I have to admit I don't exactly
understand *how* it works!). But that's no longer an SQLAlchemy question...
Cheers,
Demitri
On Jul 8, 2010, at 5:49 AM, King Simon-NFHD78 wrote:
In general, you don't need a database connection just to define your
tables and mappers. The 'bind' parameter to DeclarativeBase is optional,
and only necessary if you are using autoloading. So one solution to your
problem would be not to use autoloading, and bind to a database at the
Session level rather than the Mapper level. That would be the usual way
to use the same set of classes against multiple databases.
If you really need to use autoloading, you could move all your class
definitions into a function that accepts a database engine as a
parameter. For example:
#
# ModelClasses.py
class Namespace(object):
def __init__(self, **kwargs):
self.__dict__.update(kwargs)
def initdb(connection_string):
engine = create_engine(connection_string)
Base = declarative_base(bind=engine)
class Table1(Base):
__tablename__ = 'table1'
__table_args__ = {'autoload': True}
return Namespace(Base=Base,
Table1=Table1)
# or, you could be lazy:
# return Namespace(**locals())
# MainScript1.py
import ModelClasses
db = ModelClasses.initdb(my_connection_string)
# access db.Table1, db.Base etc.
Hope that helps,
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
On Jul 20, 2010, at 11:51 AM, Harry Percival wrote:
attached. feel free to ignore the 'sqlite' folder, which is only
needed for ironpython. the error definitely occurs in cpython 2.6.3.
1. Merging MoviesAndDirectorsAndGenres, with no primary key identifiers set,
sets it as pending in the Session. This now indicates that an INSERT into
all three tables will take place during flush, since the object had no primary
key when being merged.
2. Existing Genres, Directors, and Movies objects are attached to the new
MoviesAndDirectorsAndGenres object. These attachments specify a primary join
condition that requests that the primary key of each of the Genres, Directors,
and Movies be populated into each of the director_id, genre_id, and movie_id
attributes of the MoviesAndDirectorsAndGenres during a flush.
3. The flush takes place, the director_id, genre_id, and movie_id attributes of
the pending MoviesAndDirectorsAndGenres are populated with integer values that
happen to already exist in those tables, and the INSERT fails.
Since you don't really want an INSERT to take place here, I would recommend
using only Genres, Directors, and Movies objects.
It would be very helpful if you could not send any more zipfiles, only a single
.py script is needed for test case illustration. This makes my life easier and
also for the many people that will be reading your post in the future.
rgds,
hp
On Tue, Jul 20, 2010 at 3:54 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Jul 20, 2010, at 10:46 AM, Michael Bayer wrote:
On Jul 20, 2010, at 6:05 AM, Harry Percival wrote:
I'm clear on that. I'm only using session.add to do an insert when I
know i definitely want to. But you'll see I used session.merge on the
composite object, yet it still attempts to do an insert for rows that
already exist in its constituent tables...
you are setting a primary key, and adding right here:
my apologies, thats not the INSERT that fails.
please attach a full reproducing test script.
On Mon, Jul 19, 2010 at 5:19 PM, Michael Bayer mike...@zzzcomputing.com
wrote:
On Jul 19, 2010, at 12:04 PM, Harry Percival wrote:
Michael, thanks, as ever, for your help.
So, I think I've managed to specify the relationships:
j = join(movies_table,md_table).join(directors_table).join(genres_table)
js = j.select(use_labels=True).alias('mdg')
r0 = relationship(Movies,
primaryjoin=(js.c.movies_movie_id==movies_table.c.movie_id),
foreign_keys=([js.c.movies_movie_id])
)
r1 = relationship(Directors,
primaryjoin=(js.c.directors_director_id==directors_table.c.director_id),
foreign_keys=([js.c.directors_director_id])
)
r2 = relationship(Genres,
primaryjoin=(js.c.genres_genre_id==genres_table.c.genre_id),
foreign_keys=([js.c.genres_genre_id])
)
mapper(MoviesAndDirectorsAndGenres,
js,
properties={'movie':r0,
'director':r1,
'genre':r2
},
passive_updates=False)
To test it, I'm trying to create a new composite object, based on a
new movie but existing director genre:
session=Session()
m_new = Movies()
m_new.id=8
m_new.title = 'new movie'
session.add(m_new)
d2 = session.query(Directors).get(2)
print '***director=',d2
g6 = session.query(Genres).get(6)
print '***genre=',g6
oo_new = MoviesAndDirectorsAndGenres()
oo_new.movie = m_new
oo_new.director = d2
oo_new.genre = g6
try:
#another_new = session.merge(new)
session.merge(oo_new)
but sqla is doing some really weird stuff:
2010-07-19 16:56:01,877 INFO sqlalchemy.engine.base.Engine.0x...37f0 BEGIN
2010-07-19 16:56:01,878 INFO sqlalchemy.engine.base.Engine.0x...37f0 INSERT
INTO
movies (movie_id, title, description, genre_id, release_date) VALUES
(?, ?, ?,?, ?)
2010-07-19 16:56:01,880 INFO sqlalchemy.engine.base.Engine.0x...37f0
(8, 'new movie', None, None, None)
2010-07-19 16:56:01,882 INFO sqlalchemy.engine.base.Engine.0x...37f0
SELECT directors.director_id AS directors_director_id, directors.name
AS directors_name
FROM directors
WHERE directors.director_id = ?
2010-07-19 16:56:01,885 INFO sqlalchemy.engine.base.Engine.0x...37f0 (2,)
***director= test_multitable.Directors object at 0x021E2030
2010-07-19 16:56:01,888 INFO sqlalchemy.engine.base.Engine.0x...37f0
SELECT genres.genre_id AS genres_genre_id, genres.name AS genres_name,
genres.description AS genres_description
FROM genres
WHERE genres.genre_id = ?
2010-07-19 16:56:01,891 INFO sqlalchemy.engine.base.Engine.0x...37f0 (6,)
***genre= test_multitable.Genres object at 0x021E21B0
2010-07-19 16:56:01,894 INFO sqlalchemy.engine.base.Engine.0x...37f0
INSERT
[sqlalchemy] Lazy load on a column basis?
Hi, Is lazy loading supported on a column by column basis, or only through relationships? Cheers, Demitri -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Lazy load on a column basis?
deferred(), defer() On Jul 20, 2010, at 1:58 PM, thatsanicehatyouh...@mac.com wrote: Hi, Is lazy loading supported on a column by column basis, or only through relationships? Cheers, Demitri -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Creating a custom Visitable
I'm not familiar with the compiler extension. Could you elaborate?
(my temporary solution is monkey patching that function into
PGDDLCompiler (it feels dirty, like ruby programming))
Thanks,
Mike
On Jul 20, 7:47 am, Michael Bayer mike...@zzzcomputing.com wrote:
why not use the compiler extension ?
On Jul 20, 2010, at 3:07 AM, Mike Lewis wrote:
I figured that I need to extend a dialect. Would having something
similar to (still a rough version) added to the postgresql dialect be
an option?:
def post_create_table(self, table):
Build table-level CREATE options like TABLESPACE.
table_opts = []
inherits = table.kwargs.get('postgresql_inherits')
if inherits is not None:
if not isinstance(inherits, (list, tuple)):
inherits = (inherits,)
table_opts.append(
'\nINHERITS ( ' +
', '.join(isinstance(i, basestring) and i
or self.process(i)
for i
in inherits) +
' )')
on_commit = table.kwargs.get('postgresql_on_commit')
if on_commit:
table_opts.append(
'\nON COMMIT ' +
on_commit.upper().replace('_', ' '))
with_oids = table.kwargs.get('postgresql_with_oids')
if with_oids is not None:
if with_oids:
w = 'WITH'
else:
w = 'WITHOUT'
table_opts.append('\n%s OIDS' % w)
tablespace = table.kwargs.get('postgresql_tablespace')
if tablespace:
table_opts.append('\nTABLESPACE ' + tablespace)
return ''.join(table_opts)
Thanks,
Mike
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] mismatched parameter markers when running on Mac OS X
I have the following stack, which works fine on two different Linux
systems, but produces the error below on a Mac OS X 10.6.4 machine:
- Ubuntu 10.04 / Fedora 8 (works) - Mac OS X 10.6.4 (does not
work)
- freeTDS 0.82
- unixODBC 2.3.0
- pyodbc 2.1.7
- collective.lead 1.0
- SQLAlchemy 0.4.8
- Zope 2.10.11
- Plone 3.3.5
From the debugger (bin/instance debug) I can make simple connections
and execute simple queries, both using pyodbc and sqlalchemy.
Basically, it looks like it's breaking at this line in _setup_tables
(which overrides collective.lead.Database's):
tables['WebRegions'] = Table('lut_WebRegions', metadata,
autoload=True)
Any assistance or leads on where to go next would be greatly
appreciated!
Thanks!
2010-07-14 20:40:33 ERROR Zope.SiteErrorLog
1279165233.140.347153571577
a href=http://localhost:8080/mysite/@@database-controlpanel;http://
localhost:8080/mysite/@@database-controlpanel/a
Traceback (innermost last):
Module ZPublisher.Publish, line 119, in publish
Module ZPublisher.mapply, line 88, in mapply
Module Products.PDBDebugMode.runcall, line 70, in pdb_runcall
Module ZPublisher.Publish, line 42, in call_object
Module zope.formlib.form, line 769, in __call__
Module Products.Five.formlib.formbase, line 55, in update
Module zope.formlib.form, line 750, in update
Module zope.formlib.form, line 594, in success
Module plone.app.controlpanel.form, line 41, in handle_edit_action
Module mysite.content.browser.dbsettings, line 40, in _on_save
Module collective.lead.database, line 49, in invalidate
Module collective.lead.database, line 95, in _initialize_engine
Module mysite.content.db, line 61, in _setup_tables
Module sqlalchemy.schema, line 110, in __call__
Module sqlalchemy.schema, line 226, in __init__
Module sqlalchemy.engine.base, line 1275, in reflecttable
Module sqlalchemy.databases.mssql, line 570, in reflecttable
Module sqlalchemy.engine.base, line 844, in execute
Module sqlalchemy.engine.base, line 895, in execute_clauseelement
Module sqlalchemy.engine.base, line 907, in _execute_compiled
Module sqlalchemy.engine.base, line 916, in __execute_raw
Module sqlalchemy.engine.base, line 960, in _cursor_execute
Module sqlalchemy.engine.base, line 942, in _handle_dbapi_exception
ProgrammingError: (ProgrammingError) ('The SQL contains 0 parameter
markers, but 2 parameters were supplied', 'HY000') u'SELECT
[COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].
[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].
[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT] \nFROM [INFORMATION_SCHEMA].[COLUMNS] AS
[COLUMNS_1] \nWHERE [COLUMNS_1].[TABLE_NAME] = ? AND
[COLUMNS_1].[TABLE_SCHEMA] = ? ORDER BY [COLUMNS_1].
[ORDINAL_POSITION]' ['lut_WebRegions', 'dbo']
/Users/fulvio/plone/mysite/buildout/eggs/SQLAlchemy-0.4.8-py2.4.egg/sqlalchemy/engine/base.py(944)_handle_dbapi_exception()
- del self._reentrant_error
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] mismatched parameter markers when running on Mac OS X
On Jul 20, 2010, at 3:34 PM, fulv wrote:
I have the following stack, which works fine on two different Linux
systems, but produces the error below on a Mac OS X 10.6.4 machine:
- Ubuntu 10.04 / Fedora 8 (works) - Mac OS X 10.6.4 (does not
work)
- freeTDS 0.82
- unixODBC 2.3.0
- pyodbc 2.1.7
- collective.lead 1.0
- SQLAlchemy 0.4.8
- Zope 2.10.11
- Plone 3.3.5
From the debugger (bin/instance debug) I can make simple connections
and execute simple queries, both using pyodbc and sqlalchemy.
Basically, it looks like it's breaking at this line in _setup_tables
(which overrides collective.lead.Database's):
tables['WebRegions'] = Table('lut_WebRegions', metadata,
autoload=True)
MS-SQL (I am guessing you're using MS-SQL based on the error message and the
quoting style) support is extremely poor in SQLAlchemy 0.4.8, though this
appears to be at least partially a FreeTDS issue. Also OSX does not use
unixODBC as far as I know, it uses iODBC.You should at once be using
SQLAlchemy 0.6.3 where our reflection tests pass just fine using FreeTDS + OSX
+ default iODBC install + MS-SQL.
Any assistance or leads on where to go next would be greatly
appreciated!
Thanks!
2010-07-14 20:40:33 ERROR Zope.SiteErrorLog
1279165233.140.347153571577
a href=http://localhost:8080/mysite/@@database-controlpanel;http://
localhost:8080/mysite/@@database-controlpanel/a
Traceback (innermost last):
Module ZPublisher.Publish, line 119, in publish
Module ZPublisher.mapply, line 88, in mapply
Module Products.PDBDebugMode.runcall, line 70, in pdb_runcall
Module ZPublisher.Publish, line 42, in call_object
Module zope.formlib.form, line 769, in __call__
Module Products.Five.formlib.formbase, line 55, in update
Module zope.formlib.form, line 750, in update
Module zope.formlib.form, line 594, in success
Module plone.app.controlpanel.form, line 41, in handle_edit_action
Module mysite.content.browser.dbsettings, line 40, in _on_save
Module collective.lead.database, line 49, in invalidate
Module collective.lead.database, line 95, in _initialize_engine
Module mysite.content.db, line 61, in _setup_tables
Module sqlalchemy.schema, line 110, in __call__
Module sqlalchemy.schema, line 226, in __init__
Module sqlalchemy.engine.base, line 1275, in reflecttable
Module sqlalchemy.databases.mssql, line 570, in reflecttable
Module sqlalchemy.engine.base, line 844, in execute
Module sqlalchemy.engine.base, line 895, in execute_clauseelement
Module sqlalchemy.engine.base, line 907, in _execute_compiled
Module sqlalchemy.engine.base, line 916, in __execute_raw
Module sqlalchemy.engine.base, line 960, in _cursor_execute
Module sqlalchemy.engine.base, line 942, in _handle_dbapi_exception
ProgrammingError: (ProgrammingError) ('The SQL contains 0 parameter
markers, but 2 parameters were supplied', 'HY000') u'SELECT
[COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].
[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE],
[COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].
[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION],
[COLUMNS_1].[NUMERIC_SCALE],
[COLUMNS_1].[COLUMN_DEFAULT] \nFROM [INFORMATION_SCHEMA].[COLUMNS] AS
[COLUMNS_1] \nWHERE [COLUMNS_1].[TABLE_NAME] = ? AND
[COLUMNS_1].[TABLE_SCHEMA] = ? ORDER BY [COLUMNS_1].
[ORDINAL_POSITION]' ['lut_WebRegions', 'dbo']
/Users/fulvio/plone/mysite/buildout/eggs/SQLAlchemy-0.4.8-py2.4.egg/sqlalchemy/engine/base.py(944)_handle_dbapi_exception()
- del self._reentrant_error
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how to run a stored procedure?
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
I need to pass in the objects a[5] or do
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
new_record=a[5]
print new_record.customername
#call a function by passing the object
process_client(new_record)
#the process_client function can access records like
new_record.clientname,new_record.transaction_date
right now I get:
a[5]
Traceback (most recent call last):
File stdin, line 1, in module
TypeError: 'ResultProxy' object is unindexable
Do I convert the return or I use a different command?
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: how to run a stored procedure?
On Jul 20, 2010, at 4:44 PM, Lukasz Szybalski wrote:
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
sure, query.from_statement(), its in the ORM tutorial
I need to pass in the objects a[5] or do
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
new_record=a[5]
print new_record.customername
#call a function by passing the object
process_client(new_record)
#the process_client function can access records like
new_record.clientname,new_record.transaction_date
right now I get:
a[5]
Traceback (most recent call last):
File stdin, line 1, in module
TypeError: 'ResultProxy' object is unindexable
Do I convert the return or I use a different command?
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how to run a stored procedure?
On Jul 20, 3:46 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 4:44 PM, Lukasz Szybalski wrote:
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
sure, query.from_statement(), its in the ORM tutorial
Sorry, I think I'm reading it wrong.
a=session.query().from_statement(assp_ReportDailyTransactions
@start_date=:start,
@end_date=:end).params(start=20100701,end=20100719).all()
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 1453, in all
return list(self)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 1565, in
__iter__
return self._execute_and_instances(context)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 1570, in
_execute_and_instances
mapper=self._mapper_zero_or_none())
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 276, in
_mapper_zero_or_none
if not getattr(self._entities[0], 'primary_entity', False):
IndexError: list index out of range
Thanks,
Lucas
I need to pass in the objects a[5] or do
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
new_record=a[5]
print new_record.customername
#call a function by passing the object
process_client(new_record)
#the process_client function can access records like
new_record.clientname,new_record.transaction_date
right now I get:
a[5]
Traceback (most recent call last):
File stdin, line 1, in module
TypeError: 'ResultProxy' object is unindexable
Do I convert the return or I use a different command?
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: how to run a stored procedure?
On Jul 20, 2010, at 5:10 PM, Lukasz Szybalski wrote:
On Jul 20, 3:46 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 4:44 PM, Lukasz Szybalski wrote:
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
sure, query.from_statement(), its in the ORM tutorial
Sorry, I think I'm reading it wrong.
a=session.query().from_statement(assp_ReportDailyTransactions
@start_date=:start,
@end_date=:end).params(start=20100701,end=20100719).all()
query() needs to have entities. Here's an example:
http://www.sqlalchemy.org/docs/ormtutorial.html#using-literal-sql
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 1453, in all
return list(self)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 1565, in
__iter__
return self._execute_and_instances(context)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 1570, in
_execute_and_instances
mapper=self._mapper_zero_or_none())
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 276, in
_mapper_zero_or_none
if not getattr(self._entities[0], 'primary_entity', False):
IndexError: list index out of range
Thanks,
Lucas
I need to pass in the objects a[5] or do
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
new_record=a[5]
print new_record.customername
#call a function by passing the object
process_client(new_record)
#the process_client function can access records like
new_record.clientname,new_record.transaction_date
right now I get:
a[5]
Traceback (most recent call last):
File stdin, line 1, in module
TypeError: 'ResultProxy' object is unindexable
Do I convert the return or I use a different command?
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how to run a stored procedure?
On Jul 20, 4:55 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 5:10 PM, Lukasz Szybalski wrote:
On Jul 20, 3:46 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 4:44 PM, Lukasz Szybalski wrote:
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
sure, query.from_statement(), its in the ORM tutorial
Sorry, I think I'm reading it wrong.
a=session.query().from_statement(assp_ReportDailyTransactions
@start_date=:start,
@end_date=:end).params(start=20100701,end=20100719).all()
query() needs to have entities. Here's an example:
http://www.sqlalchemy.org/docs/ormtutorial.html#using-literal-sql
Can I pass in a list or some other type, I have over 30 columns?
a=session.execute(assp_ReportDailyTransactions @start_date=:start,
@end_date=:end,params={'start':20100701,'end':20100719})
b=session.query(a.keys()).from_statement(exec
assp_ReportDailyTransactions @start_date=:start,
@end_date=:end).params({'start':20100701,'end':20100719}).all()
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/session.py, line 873, in
query
return self._query_cls(entities, self, **kwargs)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 92, in
__init__
self._set_entities(entities)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 99, in
_set_entities
entity_wrapper(self, ent)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 2584, in
__init__
expected - got '%r' % column
sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
entity expected - got '[u'customer', u'customer_id', u'customer_num',
u'TransactionDate'..]
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: how to run a stored procedure?
On Jul 20, 2010, at 6:19 PM, Lukasz Szybalski wrote:
On Jul 20, 4:55 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 5:10 PM, Lukasz Szybalski wrote:
On Jul 20, 3:46 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 4:44 PM, Lukasz Szybalski wrote:
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
sure, query.from_statement(), its in the ORM tutorial
Sorry, I think I'm reading it wrong.
a=session.query().from_statement(assp_ReportDailyTransactions
@start_date=:start,
@end_date=:end).params(start=20100701,end=20100719).all()
query() needs to have entities. Here's an example:
http://www.sqlalchemy.org/docs/ormtutorial.html#using-literal-sql
Can I pass in a list or some other type, I have over 30 columns?
Its not clear what you are asking for.execute().fetchall() already returns
a list of rows, where each row has attributes, so that you can say
row.attrname, so it is already like an object.If you use a query(), you
have the choice of specifying an ORM mapped class or individual columns as
well, though if you are querying for all individual columns there's not much
difference between query(x, y, z).all() versus using execute().fetchall().
When you say change it, if that means, I'd like to set attributes on the
resulting objects and they go back to the database, that's clearly not
possible unless you can relate your stored procedure rows to an ORM mapped
class, since SQLAlchemy knows nothing about how your stored procedure gets
data or how that data would be modified.
If you can illustrate fully what kind of interface to the data you'd like to
see that be helpful.
a=session.execute(assp_ReportDailyTransactions @start_date=:start,
@end_date=:end,params={'start':20100701,'end':20100719})
b=session.query(a.keys()).from_statement(exec
assp_ReportDailyTransactions @start_date=:start,
@end_date=:end).params({'start':20100701,'end':20100719}).all()
Traceback (most recent call last):
File stdin, line 1, in module
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/session.py, line 873, in
query
return self._query_cls(entities, self, **kwargs)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 92, in
__init__
self._set_entities(entities)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 99, in
_set_entities
entity_wrapper(self, ent)
File /home/lucas/tmp/sql2008/env/lib/python2.5/site-packages/
SQLAlchemy-0.6.3-py2.5.egg/sqlalchemy/orm/query.py, line 2584, in
__init__
expected - got '%r' % column
sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
entity expected - got '[u'customer', u'customer_id', u'customer_num',
u'TransactionDate'..]
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Problem with alias in SQL
Hi there,
I have these two tables (with very long column names and most of them
with the same names in both tables) I want to join. Since I will be
processing the results afterwards, I would like to have shorten names
to type less and clearly differentiated names to avoid conflicts.
So I pass the use_labels param to the select statement and apply
labels to both tables.
E.g.:
select([service.alias('a'), history.alias('b')], use_labels=True)
which generates the following sql:
select a.id as a_id, a.valid_flag as a_valid_flag, a.code as
a_code, ...more columns with 'a' alias, b.id as b_id, ... more column
with 'b' alias from service as a, history as b...
The problem is when I add a where clause (or I think it's the
problem):
select([service.alias('a'), history.alias('b')],
and_(service.c.id==history.c.id, service.c.valid_flag==True),
use_labels=True)
turns out it generates the following sql:
select a.id as a_id, a.valid_flag as a_valid_flag, a.code as
a_code, ...more columns with 'a' alias, b.id as b_id, ... more column
with 'b' alias from service as a, history as b, service, history where
service.id=history.id and service.valid_flag='f'
As you can see it ignores the existence of the aliased tables and use
a new copy of them to make the join, generating a cartesian join.
How can I fix this situation?
TIA,
Mariano
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: how to run a stored procedure?
On Jul 20, 6:02 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 6:19 PM, Lukasz Szybalski wrote:
On Jul 20, 4:55 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 5:10 PM, Lukasz Szybalski wrote:
On Jul 20, 3:46 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jul 20, 2010, at 4:44 PM, Lukasz Szybalski wrote:
Session.execute() accepts strings that are converted to text():
a=session.execute(assp_ReportDailyTransactions
@start_date=:start,@end_date=:end,
params={'start':20100701,'end':20100719})
Thanks,
That does work.
Is it possible to get each record to be returned as object instead of
dictionary or change it so that it is similar object
as ..session.query().all()?
sure, query.from_statement(), its in the ORM tutorial
Sorry, I think I'm reading it wrong.
a=session.query().from_statement(assp_ReportDailyTransactions
@start_date=:start,
@end_date=:end).params(start=20100701,end=20100719).all()
query() needs to have entities. Here's an example:
http://www.sqlalchemy.org/docs/ormtutorial.html#using-literal-sql
Can I pass in a list or some other type, I have over 30 columns?
Its not clear what you are asking for. execute().fetchall() already
returns a list of rows, where each row has attributes, so that you can say
row.attrname, so it is already like an object. If you use a query(),
you have the choice of specifying an ORM mapped class or individual columns
as well, though if you are querying for all individual columns there's not
much difference between query(x, y, z).all() versus using
execute().fetchall().
When you say change it, if that means, I'd like to set attributes on the
resulting objects and they go back to the database, that's clearly not
possible unless you can relate your stored procedure rows to an ORM mapped
class, since SQLAlchemy knows nothing about how your stored procedure gets
data or how that data would be modified.
If you can illustrate fully what kind of interface to the data you'd like to
see that be helpful.
Sorry for not being clear.
Instead of typing manually column names
(column1,column2,...column38 inside the query() I would like to
use previous query .keys() to list them there
Instead doing:
myresults=session.query('column1','column2','column3').from_statement
I would like to do a=session.execute(...) and then
myresults=session.query(a.keys()).from_statement() where a.keys()
returns a list of all the column names from the stored procedure, but
unfortunately passing a list like a.keys() gives me an error. If I
type it in it works fine. How can I pass in these column names ?
Should I convert a.keys() to dictionary, or some other type?
__init__
expected - got '%r' % column
sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
entity expected - got '[u'customer', u'customer_id', u'customer_num',
u'TransactionDate'..]
I tried fetchall but when I loop over the rows, I was getting a
dictionary and instead of doing row.column1, I had to use row[0].
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: how to run a stored procedure?
On Jul 20, 2010, at 9:39 PM, Lukasz Szybalski wrote:
Instead of typing manually column names
(column1,column2,...column38 inside the query() I would like to
use previous query .keys() to list them there
Instead doing:
myresults=session.query('column1','column2','column3').from_statement
I would like to do a=session.execute(...) and then
myresults=session.query(a.keys()).from_statement() where a.keys()
returns a list of all the column names from the stored procedure, but
unfortunately passing a list like a.keys() gives me an error. If I
type it in it works fine. How can I pass in these column names ?
Should I convert a.keys() to dictionary, or some other type?
how is calling query(colmames)...all() significantly different from simply
saying execute(..).fetchall() ? you get a list of named-tuple like objects in
both cases.
Anyway, the column names are not available until you execute your string
statement and cursor.description is accessed. So if you really were in the
mood for this , you could say:
result = Session.execute(stmt)
query = Session.query(*[column(name) for name in
result.keys()]).instances(result)
__init__
expected - got '%r' % column
sqlalchemy.exc.InvalidRequestError: SQL expression, column, or mapped
entity expected - got '[u'customer', u'customer_id', u'customer_num',
u'TransactionDate'..]
I tried fetchall but when I loop over the rows, I was getting a
dictionary and instead of doing row.column1, I had to use row[0].
Thanks,
Lucas
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Creating a custom Visitable
have you found it yet ?
http://www.sqlalchemy.org/docs/reference/ext/compiler.html?highlight=compiler#module-sqlalchemy.ext.compiler
On Jul 20, 2010, at 2:12 PM, Mike Lewis wrote:
I'm not familiar with the compiler extension. Could you elaborate?
(my temporary solution is monkey patching that function into
PGDDLCompiler (it feels dirty, like ruby programming))
Thanks,
Mike
On Jul 20, 7:47 am, Michael Bayer mike...@zzzcomputing.com wrote:
why not use the compiler extension ?
On Jul 20, 2010, at 3:07 AM, Mike Lewis wrote:
I figured that I need to extend a dialect. Would having something
similar to (still a rough version) added to the postgresql dialect be
an option?:
def post_create_table(self, table):
Build table-level CREATE options like TABLESPACE.
table_opts = []
inherits = table.kwargs.get('postgresql_inherits')
if inherits is not None:
if not isinstance(inherits, (list, tuple)):
inherits = (inherits,)
table_opts.append(
'\nINHERITS ( ' +
', '.join(isinstance(i, basestring) and i
or self.process(i)
for i
in inherits) +
' )')
on_commit = table.kwargs.get('postgresql_on_commit')
if on_commit:
table_opts.append(
'\nON COMMIT ' +
on_commit.upper().replace('_', ' '))
with_oids = table.kwargs.get('postgresql_with_oids')
if with_oids is not None:
if with_oids:
w = 'WITH'
else:
w = 'WITHOUT'
table_opts.append('\n%s OIDS' % w)
tablespace = table.kwargs.get('postgresql_tablespace')
if tablespace:
table_opts.append('\nTABLESPACE ' + tablespace)
return ''.join(table_opts)
Thanks,
Mike
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group
athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Problem with alias in SQL
On Jul 20, 2010, at 8:24 PM, Mariano Mara wrote:
Hi there,
I have these two tables (with very long column names and most of them
with the same names in both tables) I want to join. Since I will be
processing the results afterwards, I would like to have shorten names
to type less and clearly differentiated names to avoid conflicts.
So I pass the use_labels param to the select statement and apply
labels to both tables.
E.g.:
select([service.alias('a'), history.alias('b')], use_labels=True)
which generates the following sql:
select a.id as a_id, a.valid_flag as a_valid_flag, a.code as
a_code, ...more columns with 'a' alias, b.id as b_id, ... more column
with 'b' alias from service as a, history as b...
The problem is when I add a where clause (or I think it's the
problem):
select([service.alias('a'), history.alias('b')],
and_(service.c.id==history.c.id, service.c.valid_flag==True),
use_labels=True)
once you make an alias(), that's your selectable. Using service refers to
the original table. You want to be using the alias() object of service in
your and_().
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
