[sqlalchemy] calling LENGTH() in SQLite?
Perhaps I have been up too many hours, but my syntax foo is fizzling. Given the following class, I want to compute the string length of "position" instead of storing it as another attribute which can get out of sync. eg. class Position(Base): __tablename__ = 'position' id = Column(INTEGER, primary_key=True) timestamp = Column(TIMESTAMP, nullable=False) position = Column(TEXT, unique=True, nullable=False) So to get all positions of string length 2, the following is not working: from sqlalchemy.sql.expression import func # ... for position, in session.query(Position.position).filter(func.length(Position.position == 2): print(position) Any insight offered would certainly be appreciated. Thanks! -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAKeNXXtURxf-eXNaXn1woYE_q9i%3DggeopN%2B_%3D9hQb3FWfqqyLQ%40mail.gmail.com.
[sqlalchemy] confirming SQLite pragma settings?
I have successfully installed SQLAlchemy 1.0.9, & can enable foreign key constraint support on each connection by hooking the event as specified in the following: http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html SQLite also allows pragma settings to be queried in the command-line shell as: sqlite> pragma foreign_keys; It would be good if I could log the setting too. Is it possible to query the connection for such information? Thanks! -- 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] translating many-to-many relationship from CSV to database?
I have a CSV file with lots of redundant data which models many-to-many relationships. I'm needing to scrub the data as it is inserted into the database littered with unique constraints. Is there a way to insert the data once without querying for each object before inserting? I'm sure this is a common CSV translation nit, but it is new to me. Thanks for any insight provided! -- 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] views declarative?
On Wed, Apr 17, 2013 at 2:59 PM, Michael Bayer mike...@zzzcomputing.comwrote:
James Hartley jjhart...@gmail.com writes:
Is it possible to map Table instances back to classes defined through
declarative_base()?
the typical form is:
Base = declarative_base()
some_table = Table('some_table', Base.metadata, Column('id', Integer,
primary_key=True))
class SomeClass(Base):
__table__ = some_table
Thanks all for the responses.
The Wiki recipe for views:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views
...creates drops the defined view on-the-fly. This raises two
questions. Is there a way to allow the view created in Python code to
persist? Likewise, is there a way to take advantage of an existing view
defined at the database level? An obvious workaround is to create a
duplicate view with a different name, but I'm curious as to whether the two
can be merged.
Thanks again for the insight shared.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] views declarative?
Starting with the Wiki article on implementing views: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views Is it possible to map Table instances back to classes defined through declarative_base()? I'm using SQLAlchemy 0.7.1. Thanks. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Re: views declarative?
On Wed, Apr 17, 2013 at 6:20 AM, Lele Gaifax l...@metapensiero.it wrote: James Hartley jjhart...@gmail.com writes: Is it possible to map Table instances back to classes defined through declarative_base()? ...I assume you are asking whether you can map a view onto a Python class using the declarative layer. If so, yes, SQLAlchemy does not care about how the underlying table is implemented, it will issue the same SQL in either cases. Thanks for your reply. Any hints on how to tie a class inheriting from Base to a Table? I have been unsuccessful in bridging the two. -- 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?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] implementing one-to-many relationship?
On Thu, Apr 4, 2013 at 1:39 PM, Michael Bayer mike...@zzzcomputing.comwrote:
the requery is due to the default expire_on_commit of session.commit():
http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#committing. Feel
free to disable this feature if you don't need it.
as far as one-to-many, I don't see the use of relationship() here, you'd
likely find it easier to use rather than assigning primary key identities
to foreign key attributes directly:
http://docs.sqlalchemy.org/en/rel_0_8/orm/tutorial.html#building-a-relationship
On Apr 3, 2013, at 2:49 PM, James Hartley jjhart...@gmail.com wrote:
I am finding it curious in the following output that once the subordinate
tuples are committed, SQLAlchemy is querying the database once again to
retrieve the primary keys of the second table. Am I performing too much
work in client code?
Thanks, Michael! expire_on_commit=False was exactly what I needed to stop
redundant queries.
I now need to go back one step, ask about sqlalchemy.orm.relationship.
Below is the table/class definitions I am using:
=8---
class Heartbeat(Base):
__tablename__ = 'heartbeat'
id = Column(Integer, primary_key=True)
timestamp = Column(DateTime, unique=True, nullable=False)
elapsed_time = Column(Float, CheckConstraint('elapsed_time 0'),
nullable=False)
def __init__(self, elapsed_time):
Constructor.
self.timestamp = datetime.datetime.now()
self.elapsed_time = elapsed_time
def __repr__(self):
Overload.
return 'Heartbeat({},{},{})'.format(self.id,
self.timestamp, self.elapsed_time)
class Platform(Base):
__tablename__ = 'platforms'
id = Column(Integer, primary_key=True)
name = Column(String, nullable=False)
#first_heartbeat_id = Column(Integer, ForeignKey('heartbeat.id'),
nullable=False)
first_heartbeat_id = Column(Integer,
CheckConstraint('first_heartbeat_id != last_heartbeat_id'), ForeignKey('
heartbeat.id'), nullable=False)
last_heartbeat_id = Column(Integer, CheckConstraint('last_heartbeat_id
!= first_heartbeat_id'), ForeignKey('heartbeat.id'), nullable=True)
UniqueConstraint('name', 'first_heartbeat_id')
first_heartbeat = relationship('Heartbeat')
#last_heartbeat =
relationship('Heartbeat')
def __init__(self, name):
self.name = name
def __repr__(self):
return 'Platform({},{},{},{})'.format(self.id, self.name,
self.first_heartbeat_id, self.last_heartbeat_id)
=8---
Numerous foreign keys in various tables refer back to the timestamp
maintained in the heartbeat table, however in the case of the platforms
table, timestamps are used to designate when the platform was originally
detected deactivated. If I don't specify a relationship() work
directly with the primary key ID values, my code works without specifying a
relationship(). If I use the code above which abstracts away the direct
use of assigning to first_heartbeat_id last_heartbeat_id in favor of
dealing with class instances, I get the following error upon creating any
Heartbeat instance:
ArgumentError: Could not determine join condition between parent/child
tables on relationship Platform.first_heartbeat. Specify a 'primaryjoin'
expression. If 'secondary' is present, 'secondaryjoin' is needed as well.
I am guessing that this arises from relationship()'s focus on types. Since
I have identical foreign keys when it comes to where the foreign keys
point, I am surmising that the underlying code cannot determine that
first_heartbeat actually maps to first_heartbeat_id. Is this correct?
If this is correct, I am not lobbying for SQLAlchemy to address this
strange edge case. I'm only trying to understand how to leverage the
mapping of Python objects to the underlying SQL tables better.
Thanks for you insight.
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] implementing one-to-many relationship?
I have implemented a (simplified) one-to-many relationship which works, but
I suspect I am reimplementing functionality in a suboptimal fashion which
is already done by SQLAlchemy. The following short example:
8---
#!/usr/bin/env python
import datetime
from sqlalchemy import create_engine, Column, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
from sqlalchemy.schema import UniqueConstraint
from sqlalchemy.dialects.sqlite import INTEGER, TEXT, DATETIME
Base = declarative_base()
Session = sessionmaker()
engine = create_engine('sqlite:///test.db', echo=True)
class Subordinate(Base):
__tablename__ = 'subordinate'
id = Column(INTEGER, primary_key=True)
name = Column(TEXT, unique=True, nullable=False)
discovered = Column(DATETIME, nullable=False)
discontinued = Column(DATETIME, nullable=True)
def __init__(self, name):
constructor
self.name = name
self.discovered = datetime.datetime.now()
def __repr__(self):
string representation overload
return 'subordinate(%d,%s,%s)' % (self.id, self.discovered,
self.discontinued)
class Record(Base):
__tablename__ = 'record'
id = Column(INTEGER, primary_key=True)
subordinate_id = Column(INTEGER, ForeignKey('subordinate.id'),
nullable=False)
timestamp = Column(DATETIME, nullable=False)
UniqueConstraint('subordinate_id', 'timestamp', name='occurrence')
def __init__(self, subordinate):
constructor
self.subordinate_id = subordinate.id
self.timestamp = datetime.datetime.now()
def __repr__(self):
string representation overload
return 'Snapshot(%s,%s,%s)' % (self.id,
self.subordinate_id, self.timestamp)
if __name__ == '__main__':
Session.configure(bind=engine)
session = Session()
Base.metadata.create_all(engine)
d = {'subordinates':{}, 'records':{}}
lst = []
for p in ('abc', 'ijk', 'xyz'):
d['subordinates'][p] = Subordinate(p)
lst.append(d['subordinates'][p])
session.add_all(lst)
session.commit()
lst = []
for p in ('abc', 'ijk', 'xyz'):
d['records'][p] = Record(d['subordinates'][p])
lst.append(d['records'][p])
session.add_all(lst)
session.commit()
8---
I am finding it curious in the following output that once the subordinate
tuples are committed, SQLAlchemy is querying the database once again to
retrieve the primary keys of the second table. Am I performing too much
work in client code?
Any insight shared would be appreciated.
8---
2013-04-03 13:35:38,291 INFO sqlalchemy.engine.base.Engine ()
2013-04-03 13:35:38,293 INFO sqlalchemy.engine.base.Engine COMMIT
2013-04-03 13:35:38,297 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-04-03 13:35:38,299 INFO sqlalchemy.engine.base.Engine INSERT INTO
subordinate (name, discovered, discontinued) VALUES (?, ?, ?)
2013-04-03 13:35:38,300 INFO sqlalchemy.engine.base.Engine ('abc',
'2013-04-03 13:35:38.296111', None)
2013-04-03 13:35:38,301 INFO sqlalchemy.engine.base.Engine INSERT INTO
subordinate (name, discovered, discontinued) VALUES (?, ?, ?)
2013-04-03 13:35:38,301 INFO sqlalchemy.engine.base.Engine ('ijk',
'2013-04-03 13:35:38.296223', None)
2013-04-03 13:35:38,302 INFO sqlalchemy.engine.base.Engine INSERT INTO
subordinate (name, discovered, discontinued) VALUES (?, ?, ?)
2013-04-03 13:35:38,302 INFO sqlalchemy.engine.base.Engine ('xyz',
'2013-04-03 13:35:38.296309', None)
2013-04-03 13:35:38,303 INFO sqlalchemy.engine.base.Engine COMMIT
2013-04-03 13:35:38,305 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2013-04-03 13:35:38,306 INFO sqlalchemy.engine.base.Engine SELECT
subordinate.id AS subordinate_id, subordinate.name AS subordinate_name,
subordinate.discovered AS subordinate_discovered, subordinate.discontinued
AS subordinate_discontinued
FROM subordinate
WHERE subordinate.id = ?
2013-04-03 13:35:38,306 INFO sqlalchemy.engine.base.Engine (1,)
2013-04-03 13:35:38,309 INFO sqlalchemy.engine.base.Engine SELECT
subordinate.id AS subordinate_id, subordinate.name AS subordinate_name,
subordinate.discovered AS subordinate_discovered, subordinate.discontinued
AS subordinate_discontinued
FROM subordinate
WHERE subordinate.id = ?
2013-04-03 13:35:38,309 INFO sqlalchemy.engine.base.Engine (2,)
2013-04-03 13:35:38,311 INFO sqlalchemy.engine.base.Engine SELECT
subordinate.id AS subordinate_id, subordinate.name AS subordinate_name,
subordinate.discovered AS subordinate_discovered, subordinate.discontinued
AS subordinate_discontinued
FROM subordinate
WHERE subordinate.id = ?
2013-04-03 13:35:38,311 INFO sqlalchemy.engine.base.Engine (3,)
2013-04-03 13:35:38,313 INFO sqlalchemy.engine.base.Engine INSERT INTO
record (subordinate_id, timestamp) VALUES (?, ?)
2013-04-03 13:35:38,313 INFO sqlalchemy.engine.base.Engine (1, '2013-04-03
13:35:38.308225')
[sqlalchemy] max() min() string lengths?
Embarrassingly, I'm gotten lost in calling SQL functions in SQLAlchemy
0.7.1.
I can boil the problem down to the following table structure:
CREATE TABLE words (
id INTEGER NOT NULL,
timestamp DATETIME NOT NULL,
word TEXT NOT NULL,
PRIMARY KEY (id),
UNIQUE (word)
);
...where I would like to find the maximum minimum stored string lengths.
eg.
SELECT MAX(LENGTH(word), MAX(LENGTH(word)) FROM words;
The code below constructs populates the table correctly, but translating
the above SQL into something more Pythonic is eluding me. Any suggestions
would be welcomed, as I'm in a rut.
Thanks.
#8
#!/usr/bin/env python
from datetime import datetime
from sqlalchemy import create_engine, Column, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.dialects.sqlite import INTEGER, DATETIME, TEXT
Base = declarative_base()
def get_dbname():
return 'test.db'
class Word(Base):
__tablename__ = 'words'
id = Column(INTEGER, primary_key=True)
timestamp = Column(DATETIME, nullable=False, default=datetime.now())
word = Column(TEXT, nullable=False, unique=True)
def __init__(self, word):
self.word = word
def __repr__(self):
return 'Word(%d, %s, %s)' % (self.id, self.timestamp, self.word)
if __name__ == '__main__':
engine = create_engine('sqlite:///' + get_dbname(), echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
words = THE OF AND TO A IN THAT IS WAS HE FOR IT WITH AS HIS ON BE AT
SAME ANOTHER KNOW WHILE LAST.split()
for w in words:
session.add(Word(w))
session.commit()
print 'total words = %d' % session.query(Word).count()
# minimum length = ?
# maximum length = ?
--
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] defining foreign keys?
On Wed, Oct 26, 2011 at 10:15 AM, Michael Bayer mike...@zzzcomputing.comwrote:
On Oct 26, 2011, at 1:04 PM, James Hartley wrote:
On Wed, Oct 26, 2011 at 2:22 AM, Stefano Fontanelli
s.fontane...@asidev.com wrote:
Hi James,
you cannot define two mapper properties that use the same name.
If you wish to locate classes based on their string name as you are doing
in relationship('User') here, the calling class (Address) must share the
same registry of names that the desired class (User) does. This registry is
part of the Base. Therefore your entire application needs to have
exactly one usage of declarative_base(), where all descending classes use
the same Base object, and not one usage per file.
This is what I had missed. Moving the call to declarative_base() to its own
module importing it as needed has taken care of all remaining problems.
Thank you Michael Stefano for taking the time to clear this up. I
sincerely appreciate it.
Jim
--
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] defining foreign keys?
I suspect this is user error, but I am not ferreting out my mistake.
I'm porting some older code to SQLAlchemy 0.71 on top of Python 2.7.1. Code
which had originally implemented foreign keys without using REFERENCES
clauses in CREATE TABLE statements previously ran fine. Now, adding formal
foreign keys isn't working. I have boiled this down to the following
variant on the example found in the SQLAlchemy Documentation:
=8--
#!/usr/bin/env
python
from sqlalchemy import create_engine, Column, ForeignKey, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
Base = declarative_base()
class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)
name = Column(String)
fullname = Column(String)
password = Column(String)
addresses = relationship('Address', order_by='Address.id',
backref='user')
def __init__(self, name, fullname, password):
self.name = name
self.fullname = fullname
self.password = password
def __repr__(self):
return User('%s', '%s', '%s', '%s') % (self.id, self.name,
self.fullname, self.password)
class Address(Base):
__tablename__ = 'addresses'
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey('users.id'))
user = relationship('User', backref=backref('addresses', order_by=id))
def __init__(self, email):
self.email_address = email
def __repr__(self):
return Address('%s', '%s', '%s') % (self.id, self.email_address,
self.user_id)
if __name__ == '__main__':
engine = create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
user = User('jdoe', 'John Doe', 'password')
print user
session.add(user)
session.commit()
=8--
Execution yields the following traceback:
=8--
traceback (most recent call last):
File ./test.py, line 51, in module
user = User('jdoe', 'John Doe', 'password')
File string, line 2, in __init__
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/instrumentation.py,
line 309, in _new_state_if_none
state = self._state_constructor(instance, self)
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py,
line 432, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/instrumentation.py,
line 157, in _state_constructor
self.dispatch.first_init(self, self.class_)
File /usr/local/lib/python2.7/site-packages/sqlalchemy/event.py, line
274, in __call__
fn(*args, **kw)
File /usr/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py,
line 2787, in _event_on_first_init
configure_mappers()
File /usr/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py,
line 2719, in configure_mappers
mapper._post_configure_properties()
File /usr/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py,
line 1035, in _post_configure_properties
prop.init()
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py, line
121, in init
self.do_init()
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/properties.py, line
905, in do_init
self._generate_backref()
File
/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/properties.py, line
1376, in _generate_backref
self, mapper))
sqlalchemy.exc.ArgumentError: Error creating backref 'user' on relationship
'User.addresses': property of that name exists on mapper
'Mapper|Address|addresses'
=8--
Any insight shared would be greatly appreciated. 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.
[sqlalchemy] support of regular expressions?
I'm needing to extract domain information from stored email addresses -- something akin to the following: SELECT DISTINCT (REGEXP_MATCHES(email, '@(.+)$'))[1] AS domain FROM tablename WHERE email ~ '@.+$' While I was able to gather the information through session.execute(), I didn't find an equivalent filter (?) in the code for regular expression related functions. Is this too database specific, or did I miss something? Thanks, and thank you for SQLAlchemy. Jim -- 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] NamedTuple error in multiple join?
I'm using SQLAlchemy 0.6.4 on top of OpenBSD utilitizing PostgreSQL 8.4.4.
As a first project, I am gathering statistics on the availability of another
Open Source project. The schema is normalized, the following SQL query
(which works at the console) to find the latest snapshot is giving me fits
when translating to Python:
SELECT s.id
FROM snapshots s
WHERE s.cron_id = (
SELECT ce.id FROM cron_events ce
WHERE ce.timestamp = (
SELECT MAX(ce.timestamp)
FROM snapshots s
JOIN cron_events ce ON ce.id = s.cron_id
JOIN platforms p ON p.id = s.platform_id
WHERE p.name = 'amd64'))
Aside from the nested subqueries, I'm stuck at implementing the innermost
SELECT which finds the latest recorded snaphot:
SELECT MAX(ce.timestamp)
FROM snapshots s
JOIN cron_events ce ON ce.id = s.cron_id
JOIN platforms p ON p.id = s.platform_id
WHERE p.name = 'amd64'
The class structure is as follows:
class Cron(Base):
collect various timed values here
__tablename__ = CRON_TABLENAME
id = Column(Integer, Sequence(CRON_TABLENAME + '_id_seq'),
primary_key=True)
timestamp = Column(DateTime, nullable=False, unique=True)
ftp_time = Column(Interval, nullable=False)
db_time = Column(Interval, nullable=True)
platforms = relationship('Platform', order_by='Platform.id',
backref='cron')
snapshots = relationship('Snapshot', order_by='Snapshot.id',
backref='cron')
def __init__(self, timestamp, ftp_time):
self.timestamp = timestamp
self.ftp_time = ftp_time
def __repr__(self):
return Cron'%s','%s','%s','%s' % (self.id, self.timestamp,
self.ftp_time, self.db_time)
class Platform(Base):
abstraction of platform name first occurrence
__tablename__ = PLATFORM_TABLENAME
id = Column(Integer, Sequence(PLATFORM_TABLENAME + '_id_seq'),
primary_key=True)
cron_id = Column(Integer, ForeignKey(CRON_TABLENAME + '.id'),
nullable=False)
name = Column(String(32), nullable=False, unique=True)
def __init__(self, cron_id, name):
self.cron_id = cron_id
self.name = name
def __repr__(self):
return Platform'%s','%s','%s','%s' % (self.id, self.cron_id,
self.cron.timestamp, self.name)
class Snapshot(Base):
abstraction of individual platform snapshot
__tablename__ = SNAPSHOT_TABLENAME
id = Column(Integer, Sequence(SNAPSHOT_TABLENAME + '_id_seq'),
primary_key=True)
cron_id = Column(Integer, ForeignKey(CRON_TABLENAME + '.id'),
nullable=False)
platform_id = Column(Integer, ForeignKey(PLATFORM_TABLENAME + '.id'),
nullable=False)
def __init__(self, cron_id, platform_id):
self.cron_id = cron_id
self.platform_id = platform_id
def __repr__(self):
return Snapshot'%s','%s','%s','%s' % (self.id, self.cron_id,
self.cron.timestamp, self.platform_id\
)
The following Python code:
for t in session.query(func.max(Cron.timestamp)).\
join((Snapshot, Cron.id == Snapshot.cron_id), (Platform,
Platform.id == Snapshot.platform_id)).\
filter(Platform.id == platform_id):
print t
...or variations such as:
t = session.query(func.max(Cron.timestamp)).\
join((Snapshot, Cron.id == Snapshot.cron_id), (Platform,
Platform.id == Snapshot.platform_id)).\
filter(Platform.id == platform_id).one()
...all are giving me the following error. I suspect I am missing something
obvious. Any insight shared would certainly be appreciated.
Thanks.
2010-10-30 14:47:43,783 INFO sqlalchemy.engine.base.Engine.0x...dccL SELECT
max(cron_events.timestamp) AS max_1
FROM cron_events JOIN snapshots ON cron_events.id = snapshots.cron_id JOIN
platforms ON platforms.id = snapshots.platform_id
WHERE platforms.id = %(id_1)s
2010-10-30 14:47:43,790 INFO sqlalchemy.engine.base.Engine.0x...dccL
{'id_1': (1,)}
Traceback (most recent call last):
File ./snapshots.py, line 138, in module
snapshot_id = get_latest_snapshot(cron_id, platform_id, name)
File ./snapshots.py, line 110, in get_latest_snapshot
filter(Platform.id == platform_id):
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/query.py,
line 1451, in __iter__
return self._execute_and_instances(context)
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/query.py,
line 1456, in _execute_and_instances
mapper=self._mapper_zero_or_none())
File /usr/local/lib/python2.6/site-packages/sqlalchemy/orm/session.py,
line 737, in execute
clause, params or {})
File /usr/local/lib/python2.6/site-packages/sqlalchemy/engine/base.py,
line 1109, in execute
return Connection.executors[c](self, object, multiparams, params)
File /usr/local/lib/python2.6/site-packages/sqlalchemy/engine/base.py,
line 1186, in _execute_clauseelement
return self.__execute_context(context)
File /usr/local/lib/python2.6/site-packages/sqlalchemy/engine/base.py,
line 1215, in __execute_context
context.parameters[0], context=context)
File
