[sqlalchemy] simple: get max id in table
I'm just not getting it: Want: select max(id) from table; attempt (latest): from sqlalchemy import * from sqlalchemy.engine import reflection from sqlalchemy import schema from sqlalchemy import exc from psycopg2 import * import re import time import os targethost = "192.168.181.204" targetdb = "postgresql://user:pword@"+ targethost +"/whatever" eng2 = create_engine(targetdb) con2 = eng2.connect() meta2 = MetaData() meta2.reflect(bind=eng2) insp2 = inspect(eng2) dst_tab = meta2.tables["tab1"] q3 = dst_tab.select([func.max(dst_tab.c.id)]) maxphoneid = con2.execute(q3).scalar() I get: sqlalchemy.exc.ArgumentError: SQL expression object or string expected, got object of type instead without the brackets in the select call, I get: sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) argument of WHERE must be type boolean, not type integer LINE 3: WHERE max(phone.id) There's gotta be a way! Any ideas? murf -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Tips for schema based db traversal and building
See below
On Tuesday, September 8, 2015 at 9:00:12 PM UTC-4, Michael Bayer wrote:
>
>
>
> you can get these like this:
>
> from sqlalchemy import inspect
> insp = inspect(my_engine)
>
> fk_constraints = insp.get_foreign_keys('mytable')
> uq_constraints = insp.get_unique_constraints('mytable')
> indexes = insp.get_indexes('mytable')
>
> docs:
> http://docs.sqlalchemy.org/en/rel_1_0/core/reflection.html#fine-grained-reflection-with-inspector
>
I tried the above, and I could get the foreign keys, but the
insp.get_unique_constraints('mytable')
returns an empty list.
I tried it against a postgresql table (that has unique constraints),
version 8.4.20...
I don't have many other versions available at the moment. I don't know if
that makes
any difference, anyway.
And, I'm running 1.0.8 sqlalchemy.
Is this a bug, or are there some requirements to get the unique constraints?
many thanks
murf
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Tips for schema based db traversal and building
On Wednesday, September 30, 2015 at 1:43:46 PM UTC-6, Michael Bayer wrote:
>
> there's no known bugs in fetching unique constraints. PG 8.4 is a pretty
> old version but should be working.
>
> note that a unique index and a unique constraint aren't listed as the same
> thing, however.you might just have unique indexes.
>
This was quite informative!
Just one last item: I'm having trouble getting at the Check Constraints...
You advised:
there's no listing of CHECK constraints right now.
they are also available on the Table as fully constructed constraint
objects:
table = Table('mytable', somemetadata, autoload=True)
for constraint in table.constraints:
# ...
I am assuming when you said "they are also available" that the "they" was
referring
to Foreign, and primary key constraints, etc, and that
CHECK constraints were not available at all, neither by reflection nor
inspection.
Am I correct?
murf
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Tips for schema based db traversal and building
Oh, Michael!
You have made my day. It just gets better and better!
I don't know how I missed these items, but your examples are very helpful.
Many thanks!
On Tuesday, September 8, 2015 at 7:00:12 PM UTC-6, Michael Bayer wrote:
>
>
>
> On 9/8/15 12:57 PM, Steve Murphy wrote:
>
>
> A bit difficult is grabbing just certain columns in the select, given
> that we have only a list of column names. That would be real nice
> if such a method were available in the core API.
>
> For example, a method for select whereby I could supply a simple
> list of column names to fetch in the select would be very handy:
>
> tablename = "user"
> tab = meta1.tables[tablename]
> collist = [ "id", "name", "address" ] ## just a few of many more
> q = tab.select(colnames=collist)
>
> this is pretty simple:
>
> q = select([tab.c[name] for name in ["id", "name", "address"]])
>
> Also, if I could get a column object from a table by name
> it would really make life easier at times:
>
> q = select([tab]).where(tab.colname(colnamevar) == col_var_val)
>
>
> yeah, tab.c[name]
>
> this is here:
>
>
> http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html#accessing-tables-and-columns
>
>
>
> Another rough spot with the API is setting up a simple set of where
> clauses for a select, given a map of column names vs. values.
> if the map contained {"user": "cat", "city": "Gotham"} it would
> be cool if we could get the desired select:
>
> select * from table where user='cat' and city='Gotham';
>
>
> that is:
>
> select = select.where(and_(*[tab.c[key] == value for key, value in
> mymap.items()]))
>
>
> Another rough spot is getting a list of constraints. I note that
> postgresql has sql to create constraints, and remove them, but
> not to get a list of them.
>
> you can get these like this:
>
> from sqlalchemy import inspect
> insp = inspect(my_engine)
>
> fk_constraints = insp.get_foreign_keys('mytable')
> uq_constraints = insp.get_unique_constraints('mytable')
> indexes = insp.get_indexes('mytable')
>
> docs:
> http://docs.sqlalchemy.org/en/rel_1_0/core/reflection.html#fine-grained-reflection-with-inspector
>
> there's no listing of CHECK constraints right now.
>
> they are also available on the Table as fully constructed constraint
> objects:
>
> table = Table('mytable', somemetadata, autoload=True)
> for constraint in table.constraints:
> # ...
>
>
> docs for the table.constraints accessor are not in good shape right now,
> the API docs aren't generating for it and there's only a few mentions of
> it, but inspector is usually more straightforward if you are just getting
> raw information about them.
>
>
>
> I do see the "\d " command,
> and you get a list of all the foreign keys and constraints that way,
> all in a big block of text I'm sure that, without supporting sql
> syntax,
> any further sqlalchemy constraint support will be very hard, if not
> impossible,
> to implement.
>
> well \d is just part of your psql shell, it is ultimately querying the
> pg_catalog tables which is what the SQLA postgresql dialect queries as well.
>
>
>
> The fact that sqlalchemy does what it does has saved me a ton of time
> writing
> a full-blown SQL parser. Many congrats, thanks, ataboys, and kudu's!!!
>
>
> thanks glad its working out!
>
>
>
> murf
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com .
> To post to this group, send email to sqlal...@googlegroups.com
> .
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Tips for schema based db traversal and building
This message concerns using sqlAlchemy for schema based traversal and
manipulation.
It is the result of a project to transfer data from on database to another,
where objects
refer to each other, and must be copied to new rows in the target db, and
have all the
foreign references updated in the new db.
I'm using the 1.0 release of sqlalchemy on python 2.7.9 (ubuntu 15), btw.
I've gotten thru a very large percentage of the project, but see some places
where a few "shortcuts" could be supplied that would shorten the code
and make programming it a bit simpler... just in case anyone is interested.
dynamic lists vs. the declarative approach:
I needed to form the list of variables and values to insert into the target
db at run time. I had success here:
vals = {}
from_tab = meta1.tables[table_name]
q = select([from_tab]).where(from_tab.c.id == from_id)
result = con1.execute(q)
res_colnames = result.keys()
res_row = result.first()
for colname, val in zip(res_colnames, res_row) :
...
vals[colname] = val;
...
to_tab = meta2.tables[table_name]
ins = to_tab.insert().values(**vals) ## here use a map instead of
declarative code
result = self.con2.execute(ins)
The code above is a bit simplified from real life. the table_name is
supplied,
the meta1, con1, are all gotten from easily imaginable function call
results,
and so are meta2, con2 for the target db.
We can assume the from_id is the id in the "from" db, of the row in
the "from" table_name that we want to copy the data.
We fetch the row from the "from" db, and we (with some filtering, like
removing the id of the originating row) then insert the filtered values
into the "to" db's table of the same name. We will obtain the id of the
new row created, and set up a simple mapping, after the insert is
executed.
So much for the task description!
In the below, if you notice I'm missing something in the way of
knowledge, feel free to enlighten me!
A bit difficult is grabbing just certain columns in the select, given
that we have only a list of column names. That would be real nice
if such a method were available in the core API.
For example, a method for select whereby I could supply a simple
list of column names to fetch in the select would be very handy:
tablename = "user"
tab = meta1.tables[tablename]
collist = [ "id", "name", "address" ] ## just a few of many more
q = tab.select(colnames=collist)
In the meantime,
it is a bit simpler to just grab the contents of the whole row, and
sift thru the results for the columns actually needed.
Also, if I could get a column object from a table by name
it would really make life easier at times:
q = select([tab]).where(tab.colname(colnamevar) == col_var_val)
where a colname method would do the lookup for you, and return with
the equivalent of what tab.c.fixedcolname would give.
Another rough spot with the API is setting up a simple set of where
clauses for a select, given a map of column names vs. values.
if the map contained {"user": "cat", "city": "Gotham"} it would
be cool if we could get the desired select:
select * from table where user='cat' and city='Gotham';
via maybe something like this:
wheremap = {"user": "cat", "city": "Gotham"}
q = select([from_tab]).where_from_map(wheremap)
Another rough spot is getting a list of constraints. I note that
postgresql has sql to create constraints, and remove them, but
not to get a list of them. I do see the "\d " command,
and you get a list of all the foreign keys and constraints that way,
all in a big block of text I'm sure that, without supporting sql syntax,
any further sqlalchemy constraint support will be very hard, if not
impossible,
to implement.
The fact that sqlalchemy does what it does has saved me a ton of time
writing
a full-blown SQL parser. Many congrats, thanks, ataboys, and kudu's!!!
murf
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Why is an association object mark as dirty instead of deleted when removed?
Hmm, I don't think I could listen to the attribute event; it's saying that
the AssociationProxy doesn't have dispatch.
Also, suppose I could detect the orphan-deletes earlier, what's the best
way to suppress the objects marked as dirty?
Here's my version of your code if it helps:
https://github.com/canaryhealth/sqlalchemy_audit/blob/master/sqlalchemy_audit/history_meta.py
Your help is much appreciated! Thanks very much.
On Wednesday, April 29, 2015 at 1:00:14 PM UTC-4, Michael Bayer wrote:
On 4/29/15 12:11 PM, st...@canary.md javascript: wrote:
Since my association object doesn't have extra columns, the row ('bob,
'apple') will be deleted. However, if there are extra columns, then having
it marked as dirty is desired.
Perhaps I would delay my recording of my audit rows until after orphans
are resolved. I am already using after_flush, is there another event I
could use? Otherwise, I would need to detect an object as an association
object without extra columns and perform specific logic to handle this. Is
there a way to identify association objects?
there's not an event in between the time that the orphan thing is
calculated and the SQL is emitted. After flush is a candidate if you can
make that work.
The approach using the event is just to make an event listener for this
purpose and apply it to those classes that need it:
class MyParent(Base):
# ...
associations = relationship(MyAssociation, cascade=all,
delete-orphan)
@event.listens_for(MyParent.associations, remove)
def on_remove(obj, child, initiator):
object_session(obj).delete(child)
class MyAssociation(Base):
# ...
Thanks!
On Tuesday, April 28, 2015 at 7:53:21 PM UTC-4, Michael Bayer wrote:
On 4/28/15 6:57 PM, st...@canary.md wrote:
Hi,
Background information: I am trying to implement functionality similar
to the history_meta.py example (
http://docs.sqlalchemy.org/en/rel_0_9/_modules/examples/versioned_history/history_meta.html
http://docs.sqlalchemy.org/en/rel_0_9/_modules/examples/versioned_history/history_meta.html).
I am listening for after_flush events and create an audit record and am
having problems with association objects. Here is an example:
class User(Auditable, self.Base, ComparableEntity):
__tablename__ = 'usertable'
id = Column(Integer, primary_key=True)
name = Column(String)
keywords = association_proxy('assocs', 'keyword')
class Keyword(Auditable, self.Base, ComparableEntity):
__tablename__ = 'keywordtable'
id = Column(Integer, primary_key=True)
word = Column(String)
class UserKeyword(Auditable, self.Base, ComparableEntity):
__tablename__ = 'userkeywordtable'
user_id = Column(Integer, ForeignKey(usertable.id),
primary_key=True)
keyword_id = Column(Integer, ForeignKey(keywordtable.id),
primary_key=True)
user = relationship(User,
backref=backref(assocs,
cascade=all,
delete-orphan))
keyword = relationship(Keyword)
def __init__(self, keyword=None, user=None):
self.user = user
self.keyword = keyword
apple = Keyword(word='apple')
pear = Keyword(word='pear')
bob = User(name='bob')
bob.keywords = [apple, pear]
sess.add(bob)
sess.commit()
bob.keywords.remove(apple) == this is when my question
is about
sess.commit()
When we remove the keyword, it marks the UserKeyword association object
is dirty instead of deleted. Why is that? Since the row is being
removed, I would expect it to be marked as deleted, so that I could make
an audit record indicating it was deleted.
does the row actually get deleted? the calculation of orphan isn't
done until flush time, because theoretically you could be associating the
UserKeyword to another User.
it doesn't look like the versioned rows recipe has support for this use
case right now. You could force the up-front delete using a remove
attribute event on that collection.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an
email to sqlalchemy+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to
Re: [sqlalchemy] Why is an association object mark as dirty instead of deleted when removed?
Since my association object doesn't have extra columns, the row ('bob,
'apple') will be deleted. However, if there are extra columns, then having
it marked as dirty is desired.
Perhaps I would delay my recording of my audit rows until after orphans are
resolved. I am already using after_flush, is there another event I could
use? Otherwise, I would need to detect an object as an association object
without extra columns and perform specific logic to handle this. Is there a
way to identify association objects?
Thanks!
On Tuesday, April 28, 2015 at 7:53:21 PM UTC-4, Michael Bayer wrote:
On 4/28/15 6:57 PM, st...@canary.md javascript: wrote:
Hi,
Background information: I am trying to implement functionality similar
to the history_meta.py example (
http://docs.sqlalchemy.org/en/rel_0_9/_modules/examples/versioned_history/history_meta.html).
I am listening for after_flush events and create an audit record and am
having problems with association objects. Here is an example:
class User(Auditable, self.Base, ComparableEntity):
__tablename__ = 'usertable'
id = Column(Integer, primary_key=True)
name = Column(String)
keywords = association_proxy('assocs', 'keyword')
class Keyword(Auditable, self.Base, ComparableEntity):
__tablename__ = 'keywordtable'
id = Column(Integer, primary_key=True)
word = Column(String)
class UserKeyword(Auditable, self.Base, ComparableEntity):
__tablename__ = 'userkeywordtable'
user_id = Column(Integer, ForeignKey(usertable.id),
primary_key=True)
keyword_id = Column(Integer, ForeignKey(keywordtable.id),
primary_key=True)
user = relationship(User,
backref=backref(assocs,
cascade=all,
delete-orphan))
keyword = relationship(Keyword)
def __init__(self, keyword=None, user=None):
self.user = user
self.keyword = keyword
apple = Keyword(word='apple')
pear = Keyword(word='pear')
bob = User(name='bob')
bob.keywords = [apple, pear]
sess.add(bob)
sess.commit()
bob.keywords.remove(apple) == this is when my question is
about
sess.commit()
When we remove the keyword, it marks the UserKeyword association object
is dirty instead of deleted. Why is that? Since the row is being
removed, I would expect it to be marked as deleted, so that I could make
an audit record indicating it was deleted.
does the row actually get deleted? the calculation of orphan isn't done
until flush time, because theoretically you could be associating the
UserKeyword to another User.
it doesn't look like the versioned rows recipe has support for this use
case right now. You could force the up-front delete using a remove
attribute event on that collection.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Why is an association object mark as dirty instead of deleted when removed?
Hi,
Background information: I am trying to implement functionality similar to
the history_meta.py example
(http://docs.sqlalchemy.org/en/rel_0_9/_modules/examples/versioned_history/history_meta.html).
I am listening for after_flush events and create an audit record and am
having problems with association objects. Here is an example:
class User(Auditable, self.Base, ComparableEntity):
__tablename__ = 'usertable'
id = Column(Integer, primary_key=True)
name = Column(String)
keywords = association_proxy('assocs', 'keyword')
class Keyword(Auditable, self.Base, ComparableEntity):
__tablename__ = 'keywordtable'
id = Column(Integer, primary_key=True)
word = Column(String)
class UserKeyword(Auditable, self.Base, ComparableEntity):
__tablename__ = 'userkeywordtable'
user_id = Column(Integer, ForeignKey(usertable.id),
primary_key=True)
keyword_id = Column(Integer, ForeignKey(keywordtable.id),
primary_key=True)
user = relationship(User,
backref=backref(assocs,
cascade=all,
delete-orphan))
keyword = relationship(Keyword)
def __init__(self, keyword=None, user=None):
self.user = user
self.keyword = keyword
apple = Keyword(word='apple')
pear = Keyword(word='pear')
bob = User(name='bob')
bob.keywords = [apple, pear]
sess.add(bob)
sess.commit()
bob.keywords.remove(apple) == this is when my question is
about
sess.commit()
When we remove the keyword, it marks the UserKeyword association object is
dirty instead of deleted. Why is that? Since the row is being removed,
I would expect it to be marked as deleted, so that I could make an audit
record indicating it was deleted.
Thanks,
Steve
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Is Firebird supported?
Hi, I am testing out Firebird b/c I want to use Alembic to set up my unit tests. Is Firebird supported by Alembic? I am getting the error message below. I am running Alembic 0.6.0 and SqlAlchemy 0.8.2 File .../.virtualenv/local/lib/python2.7/site-packages/alembic/ddl/impl.py , line 50, in get_by_dialect return _impls[dialect.name] KeyError: 'firebird' Thanks, Steve -- You received this message because you are subscribed to the Google Groups sqlalchemy-alembic group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: How to stop SQLAlchemy from adding an ON UPDATE clause to a TIMESTAMP column by default
I realize this thread is ancient, but I'm resurrecting it for Googleable
posterity since I just ran across the same issue.
The problem is that MySQL helpfully inserts the ON UPDATE cheese unless
you specify a default and/or a NULL/NOT NULL value in the CREATE TABLE
query.
http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html
Unfortunately, I haven't yet been able to get sqlalchemy to actually send
such a query, this being my attempt:
time = Column(
TIMESTAMP(), primary_key=True,
default=datetime.min, nullable=False)
Will reply again if I manage to get a TIMESTAMP column without the ON
UPDATE stuff.
On Monday, November 28, 2011 5:09:35 PM UTC-8, Michael Bayer wrote:
There's some more happening on your end. Rest assured DEFAULT and ON
UPDATE are not generated without very specific and explicit instructions -
the default and onupdate keywords would need to be passed to your
Column - engine arguments have nothing to do with it.If it were me I'd
stick a pdb into Column to intercept it happening.
Here is the output of your program:
2011-11-28 20:00:28,203 INFO sqlalchemy.engine.base.Engine SELECT
DATABASE()
2011-11-28 20:00:28,203 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,205 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'character_set%%'
2011-11-28 20:00:28,205 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,206 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'lower_case_table_names'
2011-11-28 20:00:28,206 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,207 INFO sqlalchemy.engine.base.Engine SHOW COLLATION
2011-11-28 20:00:28,207 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,211 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'sql_mode'
2011-11-28 20:00:28,211 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,212 INFO sqlalchemy.engine.base.Engine DESCRIBE `foo`
2011-11-28 20:00:28,212 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,215 INFO sqlalchemy.engine.base.Engine
DROP TABLE foo
2011-11-28 20:00:28,215 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,215 INFO sqlalchemy.engine.base.Engine COMMIT
2011-11-28 20:00:28,216 INFO sqlalchemy.engine.base.Engine DESCRIBE `foo`
2011-11-28 20:00:28,216 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,217 INFO sqlalchemy.engine.base.Engine ROLLBACK
2011-11-28 20:00:28,217 INFO sqlalchemy.engine.base.Engine
CREATE TABLE foo (
id CHAR(36) NOT NULL,
`dateAdded` TIMESTAMP,
reason TEXT,
PRIMARY KEY (id)
)
2011-11-28 20:00:28,217 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,275 INFO sqlalchemy.engine.base.Engine COMMIT
On Nov 28, 2011, at 5:38 PM, Ben Hayden wrote:
Hmm... well this is a weird problem then. I ran the provided code, and got
the same result you did, with the DEFAULT ON UPDATE missing. However, I
added a couple lines:
*from sqlalchemy.ext.declarative import declarative_base*
*from sqlalchemy import Table, CHAR, TIMESTAMP, TEXT, schema, Column*
*from uuid import uuid4 as uuid*
*Base = declarative_base()*
*class Foo(Base):*
*__tablename__ = 'foo'*
*#column definitions*
*id = Column(u'id', CHAR(length=36), default=uuid, primary_key=True,
nullable=False)*
*date_added = Column(u'dateAdded', TIMESTAMP(), nullable=False)*
*reason = Column(u'reason', TEXT())*
*from sqlalchemy.dialects import mysql*
*print schema.CreateTable(Foo.__table__).compile(dialect=mysql.dialect())*
*Base.metadata.bind = db.generate_engine()*
*Base.metadata.drop_all()*
*Base.metadata.create_all() *
The create table that was actually generated in the db is still:
*CREATE TABLE `foo` (*
* `id` char(36) NOT NULL,*
* `dateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,*
* `reason` text,*
* PRIMARY KEY (`id`)*
*) ENGINE=InnoDB DEFAULT CHARSET=latin1;*
My generate_engine method is a little helper method that returns an engine
with the following params:
*create_engine('mysql://%s:%s@%s/%s' % (*
* config.get('database', 'user'),*
* urllib.quote_plus(config.get('database', 'pass')),*
* config.get('database', 'host'),*
* config.get('database', 'name')),*
* convert_unicode=True, pool_size=20, pool_recycle=60,*
* connect_args={'use_unicode': True, 'charset':
'utf8', 'compress': True})*
Am I unknowingly passing a default I shouldn't to SQLA that is causing the
generation of the table to add those defaults? Or is there an option in
MySQL that I unknowingly have turned on?
My versions:
Python 2.7
SQLA 0.7.*
MySQL version 5.5.11
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To view this discussion on the web visit
Re: [sqlalchemy] Re: How to stop SQLAlchemy from adding an ON UPDATE clause to a TIMESTAMP column by default
My solution, since sqlalchemy seems to be ignoring the nullable and default
kwargs, is this:
time = Column(
TIMESTAMP(), primary_key=True,
server_default=text('-00-00 00:00:00'))
The default is just never used.
On Friday, January 10, 2014 12:20:45 PM UTC-8, Steve Johnson wrote:
I realize this thread is ancient, but I'm resurrecting it for Googleable
posterity since I just ran across the same issue.
The problem is that MySQL helpfully inserts the ON UPDATE cheese unless
you specify a default and/or a NULL/NOT NULL value in the CREATE TABLE
query.
http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html
Unfortunately, I haven't yet been able to get sqlalchemy to actually send
such a query, this being my attempt:
time = Column(
TIMESTAMP(), primary_key=True,
default=datetime.min, nullable=False)
Will reply again if I manage to get a TIMESTAMP column without the ON
UPDATE stuff.
On Monday, November 28, 2011 5:09:35 PM UTC-8, Michael Bayer wrote:
There's some more happening on your end. Rest assured DEFAULT and ON
UPDATE are not generated without very specific and explicit instructions -
the default and onupdate keywords would need to be passed to your
Column - engine arguments have nothing to do with it.If it were me I'd
stick a pdb into Column to intercept it happening.
Here is the output of your program:
2011-11-28 20:00:28,203 INFO sqlalchemy.engine.base.Engine SELECT
DATABASE()
2011-11-28 20:00:28,203 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,205 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'character_set%%'
2011-11-28 20:00:28,205 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,206 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'lower_case_table_names'
2011-11-28 20:00:28,206 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,207 INFO sqlalchemy.engine.base.Engine SHOW COLLATION
2011-11-28 20:00:28,207 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,211 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
LIKE 'sql_mode'
2011-11-28 20:00:28,211 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,212 INFO sqlalchemy.engine.base.Engine DESCRIBE `foo`
2011-11-28 20:00:28,212 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,215 INFO sqlalchemy.engine.base.Engine
DROP TABLE foo
2011-11-28 20:00:28,215 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,215 INFO sqlalchemy.engine.base.Engine COMMIT
2011-11-28 20:00:28,216 INFO sqlalchemy.engine.base.Engine DESCRIBE `foo`
2011-11-28 20:00:28,216 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,217 INFO sqlalchemy.engine.base.Engine ROLLBACK
2011-11-28 20:00:28,217 INFO sqlalchemy.engine.base.Engine
CREATE TABLE foo (
id CHAR(36) NOT NULL,
`dateAdded` TIMESTAMP,
reason TEXT,
PRIMARY KEY (id)
)
2011-11-28 20:00:28,217 INFO sqlalchemy.engine.base.Engine ()
2011-11-28 20:00:28,275 INFO sqlalchemy.engine.base.Engine COMMIT
On Nov 28, 2011, at 5:38 PM, Ben Hayden wrote:
Hmm... well this is a weird problem then. I ran the provided code, and
got the same result you did, with the DEFAULT ON UPDATE missing. However,
I added a couple lines:
*from sqlalchemy.ext.declarative import declarative_base*
*from sqlalchemy import Table, CHAR, TIMESTAMP, TEXT, schema, Column*
*from uuid import uuid4 as uuid*
*Base = declarative_base()*
*class Foo(Base):*
*__tablename__ = 'foo'*
*#column definitions*
*id = Column(u'id', CHAR(length=36), default=uuid, primary_key=True,
nullable=False)*
*date_added = Column(u'dateAdded', TIMESTAMP(), nullable=False)*
*reason = Column(u'reason', TEXT())*
*from sqlalchemy.dialects import mysql*
*print schema.CreateTable(Foo.__table__).compile(dialect=mysql.dialect())*
*Base.metadata.bind = db.generate_engine()*
*Base.metadata.drop_all()*
*Base.metadata.create_all() *
The create table that was actually generated in the db is still:
*CREATE TABLE `foo` (*
* `id` char(36) NOT NULL,*
* `dateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,*
* `reason` text,*
* PRIMARY KEY (`id`)*
*) ENGINE=InnoDB DEFAULT CHARSET=latin1;*
My generate_engine method is a little helper method that returns an
engine with the following params:
*create_engine('mysql://%s:%s@%s/%s' % (*
* config.get('database', 'user'),*
* urllib.quote_plus(config.get('database', 'pass')),*
* config.get('database', 'host'),*
* config.get('database', 'name')),*
* convert_unicode=True, pool_size=20,
pool_recycle=60,*
* connect_args={'use_unicode': True, 'charset':
'utf8', 'compress': True})*
Am I unknowingly passing a default I shouldn't to SQLA that is causing
the generation
Re: [sqlalchemy] Getting comparison TypeError when trying to commit aware datetime when previous value was naive datetime
you should only be dealing with timezone-naive datetimes within a Python application Thanks - make sense although the klugey way around the problem is just to commit None before you change between naive and aware since comparisons with None are fine. -- 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] Getting comparison TypeError when trying to commit aware datetime when previous value was naive datetime
When I try to commit a timezone aware datetime to replace a value that was previously timezone naive, I get a TypeError when I try to do the commit with the message: TypeError: can't compare offset-naive and offset-aware datetimes Now I am not trying to compare anything but just store the new value in an sqlite database. Is there a way to force the commit to take place without it performing a comparison to the previously stored value (if that is in fact what is going on)? -- 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: Joining three tables - Selecting column from two different tables
Hi,
Thanks. Worked like a charm.
Also thanks for SqlAlchemy. A refreshing change for someone from java
background.
I am using this with Jython. Thanks for the Jython support also.
Steve
On Jan 18, 8:54 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Jan 18, 2011, at 9:11 AM, Steve wrote:
Hi all,
Newbie here.
I just want to execute the following sql using SqlAlchemy . But
getting various errors.
select ssf.factor,ssf.displayname,pmw.weight
from probability_models_weights pmw
inner join probability_models pm on pm.id = pmw.model_id
inner join success_factors ssf on ssf.id = pmw.factor_id
where pm.id = 6
I want to execute this using session.
I am using declarative base with the following auto loaded classes.
class SucessFactors(WBase):
__tablename__ = success_factors
__table_args__ = {'autoload':True}
class ProbabilityModels(WBase):
__tablename__ = probability_models
__table_args__ = {'autoload':True}
class ProbabilityModelsWeights(WBase):
__tablename__ = probability_models_weights
__table_args__ = {'autoload':True}
I tried the following but it didn't work.
session.query(SucessFactors.factor,SucessFactors.displayname,ProbabilityModelsWeights.weight).
\
join(ProbabilityModelsWeights,ProbabilityModels,
ProbabilityModelsWeights.model_id == ProbabilityModels.id).\
join(ProbabilityModelsWeights,SucessFactors,
ProbabilityModelsWeights.factor_id == SucessFactors.id).\
filter(ProbabilityModels.id == model_id).\
all()
query.join() is a one-argument form (it will accept two arguments in 0.7, but
thats not released yet), so here you want to be saying
query(...).select_from(ProbabiliyModelsWeights).join((ProbabiltityModels,
ProbabiltiyModelsWeights.model_id==ProbabilityModels.id)).
the select_from() accepting a mapped class is a helper that was introudced in
0.6.5. Also note the tuple form inside of join(), i.e. join((target,
onclause)) (you won't need that in 0.7). Documented
athttp://www.sqlalchemy.org/docs/orm/tutorial.html#querying-with-joins.
Thanks in advance.
Steve.
--
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
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 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] Joining three tables - Selecting column from two different tables
Hi all,
Newbie here.
I just want to execute the following sql using SqlAlchemy . But
getting various errors.
select ssf.factor,ssf.displayname,pmw.weight
from probability_models_weights pmw
inner join probability_models pm on pm.id = pmw.model_id
inner join success_factors ssf on ssf.id = pmw.factor_id
where pm.id = 6
I want to execute this using session.
I am using declarative base with the following auto loaded classes.
class SucessFactors(WBase):
__tablename__ = success_factors
__table_args__ = {'autoload':True}
class ProbabilityModels(WBase):
__tablename__ = probability_models
__table_args__ = {'autoload':True}
class ProbabilityModelsWeights(WBase):
__tablename__ = probability_models_weights
__table_args__ = {'autoload':True}
I tried the following but it didn't work.
session.query(SucessFactors.factor,SucessFactors.displayname,ProbabilityModelsWeights.weight).
\
join(ProbabilityModelsWeights,ProbabilityModels,
ProbabilityModelsWeights.model_id == ProbabilityModels.id).\
join(ProbabilityModelsWeights,SucessFactors,
ProbabilityModelsWeights.factor_id == SucessFactors.id).\
filter(ProbabilityModels.id == model_id).\
all()
Thanks in advance.
Steve.
--
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: Determine what joins are in select statement
Still not there because I am not sure how to compare two join objects. For example let's say I have a join object: obj1 sqlalchemy.orm.util._ORMJoin at 0xac0d30c; Join object on task(172432076) and context(172432940) And I then create a second object: obj2 = sqlalchemy.orm.util.join(Task, Context) obj2 sqlalchemy.orm.util._ORMJoin at 0xae517ac; Join object on task(172432076) and context(172432940) I can't quite figure out how to compare the objects since: obj1.compare(obj2) is False What is the best way to compare those two join objects and conclude they represent the same join? Steve --~--~-~--~~~---~--~~ 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: Determine what joins are in select statement
And what is the approach to the simpler question of determining what joins are in a query. For example, query = session.query(Task).join(Context)... If you're just passed the query, what's the best way to determine that it contains a join (for example, so you don't perform the same join again)? Steve --~--~-~--~~~---~--~~ 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: Determine what joins are in select statement
just change table to join. I tried that but got an Attribute Error that Query' object has no attribute '__visit_name__' Asume I am doing something wrong by using the query object as the first argument but not sure what to use. Also, what is the second argument in visitors.traverse(). [It was missing in the most recent example but apparently an empty dictionary is fine.] Steve --~--~-~--~~~---~--~~ 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: Determine what joins are in select statement
use query.statement to get at the SQL expression Thanks. 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 sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] many-to-one question on delete
I have a simple foreign key relationship between a Task class and a Context class, where many Tasks can have the same Context. The default value for the Task foreign key context_id is 0. When I delete a Context, the Tasks with that context have their context_id automatically set to None and I would like it to be set to 0. (I believe this is the default cascade behavior as I do not have any cascade set on the mapper.) Right now I then explicitly change each affected Tasks context_id to 0. Do I have to do this explicitly, or is there a way on delete of a Context to have the task updated to a context_id = 0. Thanks for any advice. Steve --~--~-~--~~~---~--~~ 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: many-to-one question on delete
if you're using foreign keys correctly, that would imply there's an entity with an id of 0, and you'd attach that Context to each Task, replacing the old Context to be deleted. Michael, thanks for the usual thorough response. Yes, there is a Context entity with a unique (non-primary) id of zero, it is No Context' but it is treated exactly the same as any other Context. The reason for this is that the local sqlite database being managed through SQLA is kept in sync with a remote database that (for whatever reason) explicitly sets the 'No Context context_id to zero and not NULL. As you indicate, it is possible to iterate through the list of Tasks and explicitly set the context_id to zero, I just wanted to confirm what you indicated that the setting of the foreign key to NULL on the parent in a delete is hardwired and so I just need to work around that. Again, thanks for the help. Steve --~--~-~--~~~---~--~~ 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] LIKE filter and psycopg2
Hello all,
I'm having trouble using SQLAlchemy 0.50.rc3 and like query filters with the
psycopg2 adapter:
class Activity(Base):
__tablename__ = 'activities'
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(Unicode(100), index=True)
[...]
filter_name = 'john';
activities = db_session.query(model.Activity)
activities = activities.filter(model.Activity.name.like('%%' + filter_name +
'%%'))
The query run from the above statements does not get expanded by the adapter:
SELECT activities.id AS activities_id, activities.name AS activities_name
FROM activities
WHERE activities.name LIKE %(name_1)s
This syntax:
activities = activities.filter(name ~~ '%%%s%%' % filter_name) )
... will produce a valid SQL:
SELECT activities.id AS activities_id, activities.name AS activities_name
FROM activities
WHERE name ~~ '%john%'
However, it raises this error:
[...]
self.dialect.do_execute(cursor, statement, parameters, context=context)
File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.0rc3-
py2.5.egg/sqlalchemy/engine/default.py, line 122, in do_execute
cursor.execute(statement, parameters)
TypeError: 'dict' object is unindexable
I'm stuck. What should I be doing ? Use another syntax ? Replace psycopg2's
paramstyle to non-escaping mode ?
My environment:
Python 2.5.2
SQLAlchemy 0.5.0.rc3
PostgreSQL 8.30
psycopg 2.0.7
Ubuntu 8.04
--
Best Regards,
Steve Howe
--~--~-~--~~~---~--~~
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: LIKE filter and psycopg2
Hello Michael, its not clear to me what is actually going wrong in that case. does it work if you use a raw psycopg2 script ? Yes it does, however I figured out the print statement from the other block was just printing what would be sent to the adapter and not to the database - that confused me. It's working now, thanks, I needed the ILIKE function. -- Best Regards, Steve Howe --~--~-~--~~~---~--~~ 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] adding a child to 2 parents, 2 ways
I've been surprised by some SA behavior that doesn't seem quite right to me so I thought I'd post about it. I have submitted a trac ticket (#1201) which has example source code against sqllite, and its been closed as not a problem (if I understand). I'm fine with that, I just still don't completely understand or agree that this is desirable behavior. I'm new to SA so this may just be a problem with my viewpoint. Ticket is at http://www.sqlalchemy.org/trac/ticket/1201. The situation is that there is one child table with two parent tables, and we want to add the child to both parents. One way to do this (which works, so this is one workaround) is to set both parents at once on the child and then save it: child = Child(parent = p, parent2 = p2, ...) DBSession.save(child) OK. What surprised me was that I couldn't do this another way - by setting one parent on the child directly and then adding the child to the other parent's collection: p = DBSession.query(Parent).filter(...).one() p2 = DBSession.query(Parent2).filter(...).one() # uncommenting this prevents the error # print p.children c = Child(name=u'the child', parent2=p2) p.children.append(c) As is, this code causes an exception at the final p.children.append(c) line, the error being that the foreign key to Parent has not been set (it's the database complaining since the fk is not nullable). If however the p.children attribute is forced to load before the final line, by either eagerloading children attribute in the query for p, or by just printing p.children, then there is no error. Also no error of course if the classes/tables are modified so that there is no second parent at all. I don't see how the presence of the second parent in the design seems to cause the main parent not to adopt the child. I don't really understand the connection to autoflushing that the trac item mentioned, either (and I don't have an autoflush attribute on my scoped session, it appears). I do want to thank zzzeek who very quickly provided workarounds in the trac entry. Thoughts? Is this the way it should be? -Steve --~--~-~--~~~---~--~~ 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: adding a child to 2 parents, 2 ways
Thanks for the explanation, Michael. The behavior is very sensible now that I see what's happening. --~--~-~--~~~---~--~~ 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: trunk is now on 0.5
Trunk Rev 4726
Note the following:
Python 2.5.2 (r252:60911, May 7 2008, 15:19:09)
[GCC 4.2.3 (Ubuntu 4.2.3-2ubuntu7)] on linux2
Type help, copyright, credits or license for more information.
from sqlalchemy import *
Traceback (most recent call last):
File stdin, line 1, in module
File .../sqlalchemy/__init__.py, line 34, in module
from sqlalchemy.engine import create_engine, engine_from_config
File .../sqlalchemy/engine/__init__.py, line 54, in module
from sqlalchemy.engine.base import Dialect, ExecutionContext, Compiled, \
File .../sqlalchemy/engine/base.py, line 16, in module
from sqlalchemy import exc, schema, util, types, log
File .../sqlalchemy/log.py, line 35, in module
rootlogger = logging.getLogger('sqlalchemy')
AttributeError: 'module' object has no attribute 'getLogger'
--~--~-~--~~~---~--~~
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: trunk is now on 0.5
be sure to clean out the .pyc files. That worked. Thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Mapper issue with r4485
my hat's off to you for coming up with that relation(), it works again in rev 4486. Ah the irony ... check out http://tinyurl.com/6kqv94 And thanks as always for your remarkable responsiveness and for sqlalchemy. It is indispensible. Steve --~--~-~--~~~---~--~~ 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: self-referential table question
Michael, Works perfectly. Thanks much. Steve --~--~-~--~~~---~--~~ 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: self-referential table question
another option is: .query(Node).filter(not_(Node.id.in_(select([Node.parent_id] jason, thanks for the alternative method. Steve --~--~-~--~~~---~--~~ 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] self-referential table question
I realize this is actually an SQL question but I haven't been able to figure out the answer. In a simple self-referential table, the following produces all the Nodes that are parents to some child node(s): node_table_alias = node_table.alias() parents = session.query(Node).filter(Node.id == node_table_alias.c.parent_id) I can't figure out the analogous query that produces all the Nodes that are not parents to another node. It is clear that: non_parents = session.query(Node).filter(Node.id != node_table_alias.c.parent_id) doesn't work but I can't figure out what the right query is. Any help would be appreciated. Steve --~--~-~--~~~---~--~~ 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] create indexes on function
Is it possible to create indexes using a function using sqlalchemy and postgresql? Something like: create index idx on table (lower(table.field)) Thanks, Steve --~--~-~--~~~---~--~~ 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: Issue using rev 2425
Michael, thanks for working through this and for taking the time to explain what's going on and to provide alternative ways to getting this done. Your efforts to support the users of sqlalchemy are really extraordinary. --~--~-~--~~~---~--~~ 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: server_side_cursors
I may be missing something fundamental here, but why doesn't it
already know the metadata since I defined the columns in which I'm
interested?
thing_table = sa.Table(thing, md, sa.Column('id', sa.Integer,
primary_key = True))
On 3/17/07, Michael Bayer [EMAIL PROTECTED] wrote:
the cursor metadata often cannot be read until fetchone() is called
first. the current result set implementation we have doesnt call
fetchone() before it tries to get the metadata, and normally it
shouldnt (since the result set doesnt even know if its the result of a
select/insert/whatever). id like an alternate result set class to go
into effect when PG/server side cursors/select is used to do this, i
think someone was supposed to send a patch. its hard for me to
develop since my version of PG 8.1 doesnt seem to reproduce the issue.
On Mar 17, 8:14 pm, [EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:
I was excited to see the server_side_cursors option that was added
recently.
I saw the reports of it not working with autoload = True, but I've
been having trouble getting it to work at all.
When attempting to select a row using:
t2.select().execute().fetchone()
I get:
INFO sqlalchemy.engine.base.Engine.0x..d0 SELECT thing.id FROM thing
INFO sqlalchemy.engine.base.Engine.0x..d0 {}
Traceback (most recent call last): File stdin, line 1, in ?
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line
811, in __repr__
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line
671, in _get_col
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py, line
659, in _convert_key
sqlalchemy.exceptions.NoSuchColumnError: Could not locate column in
row for column '0'
This query runs fine without server_side_cursors = True
Any suggestions?
--~--~-~--~~~---~--~~
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: server_side_cursors
SQLAlchemy's result wrapper, ResultProxy, then calls:
metadata = cursor.metadata
My question was why doesn't ResultProxy use the sqlalchemy metadata I
defined when I defined the sqlalchemy Table?
to psycopg2 versions, PG setup, or what. if we can determine its a
psycopg2 version issue, then everyone can just upgrade.
which version are you using?
On Mar 17, 2007, at 8:41 PM, Steve Huffman wrote:
I may be missing something fundamental here, but why doesn't it
already know the metadata since I defined the columns in which I'm
interested?
thing_table = sa.Table(thing, md, sa.Column('id', sa.Integer,
primary_key = True))
On 3/17/07, Michael Bayer [EMAIL PROTECTED] wrote:
the cursor metadata often cannot be read until fetchone() is called
first. the current result set implementation we have doesnt call
fetchone() before it tries to get the metadata, and normally it
shouldnt (since the result set doesnt even know if its the result
of a
select/insert/whatever). id like an alternate result set class
to go
into effect when PG/server side cursors/select is used to do this, i
think someone was supposed to send a patch. its hard for me to
develop since my version of PG 8.1 doesnt seem to reproduce the
issue.
On Mar 17, 8:14 pm, [EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:
I was excited to see the server_side_cursors option that was added
recently.
I saw the reports of it not working with autoload = True, but I've
been having trouble getting it to work at all.
When attempting to select a row using:
t2.select().execute().fetchone()
I get:
INFO sqlalchemy.engine.base.Engine.0x..d0 SELECT thing.id FROM thing
INFO sqlalchemy.engine.base.Engine.0x..d0 {}
Traceback (most recent call last): File stdin, line 1, in ?
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py,
line
811, in __repr__
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py,
line
671, in _get_col
File build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py,
line
659, in _convert_key
sqlalchemy.exceptions.NoSuchColumnError: Could not locate column in
row for column '0'
This query runs fine without server_side_cursors = True
Any suggestions?
--~--~-~--~~~---~--~~
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: self-referential table question
Works perfectly. Your responsiveness and the usefulness of SQLAlchemy continue to amaze. Thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: self-referential table question
Works. Thanks. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Problem with count in rev 2089
Prior to 2089, the following worked fine:
session.query(ItemKeyword).count()
where ItemKeyword has a compound primary key that is defined in its
mapper as follows:
mapper(ItemKeyword, itemkeyword_table, primary_key =
[itemkeyword_table.c.item_uuid, itemkeyword_table.c.keyword_uuid],
properties={'keyword': relation(Keyword, lazy=False, backref='itemkeywords')})
With 2089, I get the following error:
session.query(ItemKeyword).count()
Traceback (most recent call last):
File stdin, line 1, in ?
File sqlalchemy/orm/query.py, line 270, in count
s = sql.select([sql.func.count(list(self.table.primary_key)[0])],
whereclause, from_obj=from_obj, **kwargs)
IndexError: list index out of range
--
Platform: error occurs on both Ubuntu Linux and Windows XP; database is sqlite.
--~--~-~--~~~---~--~~
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: Problem with mapper relationship when lazy=False
youre really looking to have an association object pattern here. I thought you might recommend that. My problem last time I tried an association object was that in the following situation: item table keyword table itemkeyword table (and association object) I couldn't get the keywords to eager load when I retrieve items, which is the reason I am trying to kluge this together without an association object. I'll go back and see if I can retrieve items and then do keywords = [ik.keyword for ik in item.itemkeywords] without SQLAlchemy querying the database each time separately from the query that brings back the items. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Problem with mapper relationship when lazy=False
the eager load should be able to go through the association object down to the endpoint Keyword objects Thanks -- that does work. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
