Re: [sqlalchemy] select count(*)
On 11/04/2011 03:11 AM, Mark Erbaugh wrote: On Nov 3, 2011, at 3:31 PM, werner wrote: Mark, On 11/03/2011 07:18 PM, Mark Erbaugh wrote: Using the query object count() method generates a sub-query (as per the docs). The docs say to use func.count to avoid the subquery. func.count seems to require a field object. Is there a way to generate a query that essentially becomes 'select count(*) from table' using the ORM, not the SQL generator? Just the other day I thought I needed the same, initially I just used the id column which all my tables had, but as count(anything) is pretty expensive (using Firebird SQL - so might be different for other dbs) I wanted to find a way without using count(). In my case I needed at some point to get all the id values of that table (to build a virtual listctrl in wxPython), so instead of doing the count and starting feeling the list I got the id and did a len(onresult) to get my count. Point I am trying to make with a lot of words, maybe there is a solution which doesn't need count() at all:-) . I never considered that a count(*) was that expensive especially if there is no where clause. I think it depends a lot on the SQL one uses. I know it is recommended not to use count on Firebird SQL unless there is no other solution. Just did a google search on count() sql slow and there seem to be problems with other db engines too. I would think that it would be less expensive than actually retrieving all the rows and counting them. Just did a little test with one of my larger tables (over 16,000 rows) and doing: qTable = db.Cepagesyn idCol = qTable.id resultT = session.query(qTable).all() # about 1 sec resultC = session.query(idCol).all() # about .25 sec count = session.query(db.sa.func.count(idCol)).all() # about .01 sec So, you are absolutely right count() is faster. In my case I need resultC any way, so doing count and then sometimes later resultC could return a different number and I really don't need it. What if there are millions of rows? The result set could fill up memory. In my case, I just need to know how many rows. Don't deal in millions (Euros or rows) :-) . I don't care about any other details. In one case, I'm checking to see if there are zero rows, in which case, I populate the table with initial rows. In another case, I'm just unittesting some code and I want to make sure that there are the proper number of rows in the table as one of the test conditions. I believe the count test line is what you were looking for (btw db.Cepagesyn is the class of my table cepagesyn). Werner -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] select count(*)
Il 04/11/11 03.08, Mark Erbaugh ha scritto:
On Nov 3, 2011, at 2:54 PM, Stefano Fontanelli wrote:
Il 03/11/11 19.18, Mark Erbaugh ha scritto:
Using the query object count() method generates a sub-query (as per the docs).
The docs say to use func.count to avoid the subquery. func.count seems to
require a field object. Is there a way to generate a query that essentially
becomes 'select count(*) from table' using the ORM, not the SQL generator?
Thanks,
Mark
Did you try func.count('*')?
How would you specify the table you want counted? I trued
func.count('table.*') and that didn't work.
Check the manual: http://www.sqlalchemy.org/docs/orm/tutorial.html#counting
To achieve our simpleSELECTcount(*)FROMtable, we can apply it as:
SQL http://www.sqlalchemy.org/docs/orm/tutorial.html#
session.query(func.count('*')).select_from(User).scalar()
Is that right for you?
--
Ing. Stefano Fontanelli
Asidev S.r.l.
Via Osteria Bianca, 108/A 50053 Empoli (Firenze)
Tel. (+39) 333 36 53 294 Fax. (+39) 0571 1 979 978
E-mail: s.fontane...@asidev.com Web: www.asidev.com
Skype: stefanofontanelli
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Thu, Nov 3, 2011 at 11:31 PM, Michael Bayer mike...@zzzcomputing.com wrote: ... I should look at this more closely, took a brief glance. One thought I had was why not do the switch the schema thing within Engine.connect(), at least there you know which engine you're dealing with. Ok I'll try this. I was not sure it was the proper place to do the initialization of the schema Though I don't really understand how this is organized anyway, the query() function for example seems a little weird, wouldn't you want this to be transparent at the Engine level ? Yeah sure -- that's the optimal goal. I will try to refactor everything as a custom Engine I guess, that handles/initialize its own set of pools, There should be a simple way to make two engines talk to one pool and switch the schema based on each engine. Maybe some context to be provided to the checkout event- possibly a small API change. Will try and come back ;) 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- Tarek Ziadé | http://ziade.org -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Fri, Nov 4, 2011 at 10:33 AM, Tarek Ziadé ziade.ta...@gmail.com wrote: ... Will try and come back ;) Hello, I am back \o/ Here's my v2: http://tarek.pastebin.mozilla.org/1373520 This time I have created a custom Strategy and Engine classes, and the get_engine() function takes care of instanciating a pool for the server if needed, and an engine instance per sqlurl, using that pool/ I have used Engine.contextual_connect for the schema switching, and Engine.execute has our custom execution things. I guess this is much cleaner since anyone can use create_engine() with my new strategy. The only assumption I am making is that there's a shared pool on the same host. Does this look better ? Thanks for your help so far Cheers Tarek -- Tarek Ziadé | http://ziade.org -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] select count(*)
On Nov 4, 2011, at 4:54 AM, Stefano Fontanelli wrote:
Il 04/11/11 03.08, Mark Erbaugh ha scritto:
On Nov 3, 2011, at 2:54 PM, Stefano Fontanelli wrote:
Il 03/11/11 19.18, Mark Erbaugh ha scritto:
Using the query object count() method generates a sub-query (as per the
docs). The docs say to use func.count to avoid the subquery. func.count
seems to require a field object. Is there a way to generate a query that
essentially becomes 'select count(*) from table' using the ORM, not the
SQL generator?
Thanks,
Mark
Did you try func.count('*')?
How would you specify the table you want counted? I trued
func.count('table.*') and that didn't work.
Check the manual: http://www.sqlalchemy.org/docs/orm/tutorial.html#counting
To achieve our simple SELECT count(*) FROM table, we can apply it as:
SQL session.query(func.count('*')).select_from(User).scal
ar()
Is that right for you?
Stefano,
Thanks. I missed that in the documentation and it does indeed generate the
expected SQL (at least with SQLite).
Mark
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Issue with compound foreign key
I am having some trouble getting the code below to work... running the
code below will give this error message when trying to create a Session
object (last line):
sqlalchemy.exc.ArgumentError: Could not determine relationship direction
for primaryjoin condition 'ChildArea.session_id==ParentArea.session_id
AND ChildArea.parent_area_id==ParentArea.id', on relationship
ChildArea.parent_area. Ensure that the referencing Column objects have a
ForeignKey present, or are otherwise part of a ForeignKeyConstraint on
their parent Table, or specify the foreign_keys parameter to this
relationship.
In other words, no error messages after the create_all statement.
Bit of background, the database is an existing database, hence the
explicit naming of database columns used every now and then. I am using
SQLA 0.7.3 on a Oracle XE 11.2 database (using the most recent version
of cx_Oracle).
Data is divided in sessions, each session (date) contains a topology
which consists of parent areas which themselves consist of child areas.
The set of parent areas differs over the sessions. The primary key for a
parent area consists of both the session id and the parent area id
column. Hence the foreignkey relation from child to parent id consists
of these two columns.
This is what is giving me issues at the moment, and a few questions.
First of course is how to make the code below work, but I am also
wondering what the best practice is with regards to using column names
and/or object and property names when defining foreign keys and
relationships. As the two namings are not in line for my example.
The ForeignKeyConstraint requires column names, but the primaryjoin
seems to be able to handle various options. Have been skimming through
the documentation to find a declarative example with a compound
foreignkey but have not been able to find one. What would be a best
practice for the primaryjoin?
Of course any other comments/recommendations with regards to the code
below is appreciated :)
Thijs
from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy import ForeignKeyConstraint
from sqlalchemy import and_
from sqlalchemy import Integer
from sqlalchemy import Date
from sqlalchemy import String
from sqlalchemy.orm import relationship
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from datetime import date
# Initiate connection to Oracle database
engine=create_engine('oracle://scott:tiger@localhost:1521/XE',
echo=True)
Base = declarative_base(engine)
class Session(Base):
__tablename__ = 'sessions'
id = Column(Integer, primary_key=True)
date = Column(Date, nullable=False)
def __init__(self, id, date):
self.id = id
self.date = date
def __repr__(self):
return 'Session(date={1:%Y-%m-%d})'.format(self.id,
self.date)
class ParentArea(Base):
__tablename__ = 'session_parentareas'
session_id = Column(Integer, ForeignKey('sessions.id'),
primary_key=True)
session = relationship(
Session,
primaryjoin=ParentArea.session_id==Session.id,
backref=parent_areas
)
id = Column(Integer, primary_key=True)
name = Column(String(50))
def __init__(self, session, id, name):
self.session = session
self.id = id
self.name = name
def __repr__(self):
return 'ParentArea(date={0:%Y-%m-%d}, name={1})'.format(
self.session.delivery_date,
self.name
)
class ChildArea(Base):
__tablename__ = 'session_childareas'
__table_args__ = (
ForeignKeyConstraint(
['session_id', 'parentarea_id'],
['session_parentareas.session_id', 'session_parentareas.id']
),
)
session_id = Column(Integer, ForeignKey('sessions.id'),
primary_key=True)
session = relationship(
Session,
primaryjoin=ChildArea.session_id==Session.id
)
id = Column(Integer, primary_key=True)
parent_area_id = Column('parentarea_id', Integer, nullable=False)
parent_area = relationship(
ParentArea,
primaryjoin=and_(
ChildArea.session_id==ParentArea.session_id,
ChildArea.parent_area_id==ParentArea.id
),
backref=child_areas
)
name = Column(String(50))
def __init__(self, session, id, parent_area, name):
self.session = session
self.id = id
self.parent_area = parent_area
self.name = name
def __repr__(self):
return 'ChildArea(date={0:%Y-%m-%d}, name={1})'.format(
self.session.delivery_date,
self.name
)
Base.metadata.drop_all()
Base.metadata.create_all()
# Add a session
c = Session(1, date.today())
For completeness sake the full traceback:
Traceback (most recent call last):
File example.py, line 107, in module
c = Session(1, date.today())
File string, line 2, in __init__
File
Re: [sqlalchemy] per-host pooling vs per-engine pooling
On Nov 4, 2011, at 3:41 AM, Tarek Ziadé wrote: On Fri, Nov 4, 2011 at 10:33 AM, Tarek Ziadé ziade.ta...@gmail.com wrote: ... Will try and come back ;) Hello, I am back \o/ Here's my v2: http://tarek.pastebin.mozilla.org/1373520 This time I have created a custom Strategy and Engine classes, and the get_engine() function takes care of instanciating a pool for the server if needed, and an engine instance per sqlurl, using that pool/ I have used Engine.contextual_connect for the schema switching, and Engine.execute has our custom execution things. I guess this is much cleaner since anyone can use create_engine() with my new strategy. The only assumption I am making is that there's a shared pool on the same host. Does this look better ? Thanks for your help so far OK this is clearer to me. So you're really trying to get it so that any number of create_engine() calls all use the same pool based on hostname, OK. If i have time today I might want to try paring this down a lot more. Also not sure why you need the disconnect check stuff in execute() , SQLA has that stuff built in.But it does raise an important point that the mechanism by which we dispose the pool when a disconnect is detected probably should be different here, since the disposal is per-engine, just gives itself a new pool. The other engines will still point to the old, disposed pool, probably causing some kind of pileup. Cheers Tarek -- Tarek Ziadé | http://ziade.org -- 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 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 sqlalchemy@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] Issue with compound foreign key
On Nov 4, 2011, at 3:25 AM, Thijs Engels wrote: I am having some trouble getting the code below to work... running the code below will give this error message when trying to create a Session object (last line): sqlalchemy.exc.ArgumentError: Could not determine relationship direction for primaryjoin condition 'ChildArea.session_id==ParentArea.session_id AND ChildArea.parent_area_id==ParentArea.id', on relationship ChildArea.parent_area. Ensure that the referencing Column objects have a ForeignKey present, or are otherwise part of a ForeignKeyConstraint on their parent Table, or specify the foreign_keys parameter to this relationship. the issue is this: primaryjoin=and_( ChildArea.session_id==ParentArea.session_id, ChildArea.parent_area_id==ParentArea.id ), it makes the mistake described here (note the FAQ overall is being redone, don't worry that this isn't easy to find at the moment): http://www.sqlalchemy.org/trac/wiki/FAQ#ImusingDeclarativeandsettingprimaryjoinsecondaryjoinusinganand_oror_andIamgettinganerrormessageaboutforeignkeys. This is what is giving me issues at the moment, and a few questions. First of course is how to make the code below work, but I am also wondering what the best practice is with regards to using column names and/or object and property names when defining foreign keys and relationships. As the two namings are not in line for my example. The ForeignKeyConstraint requires column names, but the primaryjoin seems to be able to handle various options. Have been skimming through the documentation to find a declarative example with a compound foreignkey but have not been able to find one. What would be a best practice for the primaryjoin? ForeignKeyConstraint is a Core construct. It knows nothing about the ORM or primaryjoin or anything like that.It allows strings as it is associated with a Table inline, where it can then pull the parent columns from the parent Table object's .c collection. primaryjoin OTOH receives a SQL Expression Language structure as an argument, a Python data structure that represents SQL string, not unlike how a DOM structure represents an XML document. For convenience, when using declarative, this structure can be passed as Python code inside of a string, where eval() is then called upon the string to produce the Python construct. In this case your foreign key constraints match exactly to your relationships so I would think primaryjoin is not needed at all here. (just tried it - you can remove all primaryjoin directives here no problem). -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] query returns a non-result?
On Nov 3, 2011, at 9:33 AM, Chris Withers wrote:
Hi All,
Any idea what this traceback is about?
This query normally works fine and is run a few hundred times a day ;-)
if you use the DBAPI and you say:
cursor = conn.cursor()
cursor.execute(UPDATE XYZ set foo=bar)
if you were to then try to look at the list of columns returned by this
query, that is, cursor.description, you'd get None. Why is that ? Because
we just did an UPDATE statement, no columnar results are returned. description
is None.
If our UPDATE happened to use a RETURNING type of clause, assuming this is
supported by both database and DBAPI we *would* get a result set back, and
cursor.description should be present.
Anyway, SQLAlchemy raises the error you see if you call fetchone()/all() on a
result set where cursor.description is not present.
Why query(X).with_lockmode('update').all() is doing this appears to be some bug
in your DBAPI, because all() emits a SELECT unconditionally.
cheers,
Chris
Original Message
session.query(PasswordRequest).with_lockmode('update').all():
File SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/query.py, line 1579, in all
return list(self)
File SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/orm/query.py, line 1791, in
instances
fetch = cursor.fetchall()
File SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py, line 2498, in
fetchall
l = self.process_rows(self._fetchall_impl())
File SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py, line 2467, in
_fetchall_impl
self._non_result()
File SQLAlchemy-0.6.6-py2.6.egg/sqlalchemy/engine/base.py, line 2472, in
_non_result
This result object does not return rows.
sqlalchemy.exc.ResourceClosedError: This result object does not return rows.
It has been closed automatically.
--
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
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 sqlalchemy@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] per-host pooling vs per-engine pooling
On Fri, Nov 4, 2011 at 4:45 PM, Michael Bayer mike...@zzzcomputing.com wrote: ... OK this is clearer to me. So you're really trying to get it so that any number of create_engine() calls all use the same pool based on hostname, OK. If i have time today I might want to try paring this down a lot more. Also not sure why you need the disconnect check stuff in execute() , SQLA has that stuff built in. I did not know. Do you have any pointer ? But it does raise an important point that the mechanism by which we dispose the pool when a disconnect is detected probably should be different here, since the disposal is per-engine, just gives itself a new pool. The other engines will still point to the old, disposed pool, probably causing some kind of pileup. Ah I see.. maybe some kind of event the engine can register to, to refresh its pool variable ? Cheers Tarek -- Tarek Ziadé | http://ziade.org -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Foreign key reflection error?
Hello, Thanks Mike for the comments. Before I answer the questions you asked, I want to note I found a workaround without making any changes to the database-- I just reversed the tables in the definition. At first I was using: Survey.bossSpectrumHeaders = relationship(BOSSSpectrumHeader, backref=survey) Changing this to the following worked: BOSSSpectrumHeader.survey = relationship(Survey, backref=bossSpectrumHeaders) I'm not really sure how to interpret that. On Nov 3, 2011, at 6:25 PM, Michael Bayer wrote: two things I notice, first why using extend_existing - suggests theres more going on here. I'm not wholly sure why that is in place; my colleague wrote that part. Also are you certain the foreign key from boss.spectrum_header points to the platedb.schema table and not another schema table elsewhere ? Yes, that table name is unique across all schemas. Yet another thing, when you reflect the foreign key from spectrum_header, it may not be coming back with platedb as the schema since you appear to be referring to the remote table using the implicit search path. SQLAlchemy may not be matching that up like you expect. There was an issue regarding this which was fixed in 0.7.3, another user relying upon a long search path. I do have a long search path. Do you get different results using 0.7.2 ? No, the first thing I did when I got this error was upgrade to 0.7.3. can you try defining your foreign key constraints in PG consistently with regards to how you're using schemas in your model ? (i.e. either the FK is to platedb.schema in PG, or remove the platedb schema from Survey). I was reading the descriptions from PGAdmin3 - apparently they drop the schema in the display when the table is on the search path. There's nothing that I can do to further put the table in the schema, as it were. I hope that knowing that reversing the order works helps to point to the problem...? Cheers, Demitri -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Foreign key reflection error?
I think the important thing here is that the table definition on the Python side needs to represent the table in the same way that the foreign key def will represent it from PG. It's based on this fact: I was reading the descriptions from PGAdmin3 - apparently they drop the schema in the display when the table is on the search path. There's nothing that I can do to further put the table in the schema, as it were. So SQLA does this: 1. reflect boss.spectrum_header 2. see that boss.spectrum_header has a foreign key - to a table called survey. No schema is given for this FK def. So SQLAlchemy creates a new table called survey in the metadata collection. The schema is None. 3. SQLAlchemy then reflects survey, all its columns. PG's liberal search path allows this to work without issue. 4. The application then goes to reflect what is, from SQLAlchemy's perspective, an entirely different table called platedb.survey. Populates that table with things too. 5. The MetaData now has three tables: boss.spectrum_header, platedb.survey, survey. Errors ensue since boss.spectrum_header points to survey and not platedb.survey. Solutions: 1. set the search path to be only public for the application's connection. Use explicit schema names for all constructs outside of public. A connection event that emits SET search_path TO public on each new connection will achieve this without affecting the database outside of the app. 2. Leave the liberal search path in place. Remove the usage of schema within the SQLAlchemy application and let PG's liberal search path find things. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] How to get literal default column values?
Hi,
I'm trying to use the sqlalchemy compiler to generate insert statements
that I can feed myself to MySQLdb. With a sqlalchemy table definition, I'm
able to get a nice SQL statement using the following.
query =
str(table.insert().values(**kwargs).compile(dialect=MySQLDialect(paramstyle='pyformat')))
However, if the table has literal default values for any of the columns,
those column names end up in the generated SQL statement as variables even
if kwargs does not contain that key . How can I get a dict of the literal
keys to merge with ones passed in when I execute the query?
Example:
Table('fubar', metadata,
Column('id', Integer, primary_key=True),
Column('status_id', Integer, nullable=False, default=1),
Column('modified', DateTime, default=func.current_timestamp(),
onupdate=func.current_timestamp()),
Column('created', DateTime, default=func.current_timestamp()),
mysql_engine='InnoDB'
)
produces
INSERT INTO fubar (status_id, modified, created) VALUES (%(status_id)s,
CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
I need to get a dict of literal defaults from somewhere so that I can pass
in {'status_id': 1} to cursor.execute()
Thanks,
Roger
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: How to get literal default column values?
I found that the following works. I'm wondering if there's a better way.
def _add_insert_default_values(self, kwargs):
for col in self._table.c.keys():
default = self._table.c[col].default
if default is not None and default.is_scalar:
if col not in kwargs:
kwargs[col] = default.arg
On Fri, Nov 4, 2011 at 3:19 PM, Roger Hoover roger.hoo...@gmail.com wrote:
Hi,
I'm trying to use the sqlalchemy compiler to generate insert statements
that I can feed myself to MySQLdb. With a sqlalchemy table definition, I'm
able to get a nice SQL statement using the following.
query =
str(table.insert().values(**kwargs).compile(dialect=MySQLDialect(paramstyle='pyformat')))
However, if the table has literal default values for any of the columns,
those column names end up in the generated SQL statement as variables even
if kwargs does not contain that key . How can I get a dict of the literal
keys to merge with ones passed in when I execute the query?
Example:
Table('fubar', metadata,
Column('id', Integer, primary_key=True),
Column('status_id', Integer, nullable=False, default=1),
Column('modified', DateTime, default=func.current_timestamp(),
onupdate=func.current_timestamp()),
Column('created', DateTime, default=func.current_timestamp()),
mysql_engine='InnoDB'
)
produces
INSERT INTO fubar (status_id, modified, created) VALUES (%(status_id)s,
CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
I need to get a dict of literal defaults from somewhere so that I can pass
in {'status_id': 1} to cursor.execute()
Thanks,
Roger
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] How to get literal default column values?
On Nov 4, 2011, at 3:19 PM, Roger Hoover wrote: Hi, I'm trying to use the sqlalchemy compiler to generate insert statements that I can feed myself to MySQLdb. With a sqlalchemy table definition, I'm able to get a nice SQL statement using the following. query = str(table.insert().values(**kwargs).compile(dialect=MySQLDialect(paramstyle='pyformat'))) However, if the table has literal default values for any of the columns, those column names end up in the generated SQL statement as variables even if kwargs does not contain that key . that's not true, what you describe is the opposite of SQLAlchemy's policy on database-side defaults. If a column is defined with a server side default, and the key is not in kwargs, it is not rendered in the SQL statement - otherwise there'd be no way for SQLalchemy to support such defaults ! the error in your code is that you're using default which is for a Python side default rather than server_default which is for a server side default. http://www.sqlalchemy.org/docs/core/schema.html#server-side-defaults -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] How to get literal default column values?
On Fri, Nov 4, 2011 at 3:57 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Nov 4, 2011, at 3:19 PM, Roger Hoover wrote: Hi, I'm trying to use the sqlalchemy compiler to generate insert statements that I can feed myself to MySQLdb. With a sqlalchemy table definition, I'm able to get a nice SQL statement using the following. query = str(table.insert().values(**kwargs).compile(dialect=MySQLDialect(paramstyle='pyformat'))) However, if the table has literal default values for any of the columns, those column names end up in the generated SQL statement as variables even if kwargs does not contain that key . that's not true, what you describe is the opposite of SQLAlchemy's policy on database-side defaults. If a column is defined with a server side default, and the key is not in kwargs, it is not rendered in the SQL statement - otherwise there'd be no way for SQLalchemy to support such defaults ! the error in your code is that you're using default which is for a Python side default rather than server_default which is for a server side default. http://www.sqlalchemy.org/docs/core/schema.html#server-side-defaults Thank you for the pointer to server side defaults. I actually do want client side defaults in this case. i just want a convenient way to get them. Looks like I can get them from the compiler.prefetch list. Thanks, Roger -- 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 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 sqlalchemy@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.
