[sqlalchemy] Re: dictionary returning a collection
Great!
The solution #3 seems especially appealing ...
Thank you for the comprehensive answer!
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
On Behalf Of jason kirtland
Sent: Thursday, January 17, 2008 7:53 PM
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: dictionary returning a collection
Christophe Alexandre wrote:
Dear All,
Send me some study material on DBMS + $100 !
Or if it fits you better, can you please help on the issue described
below?
The closest situation to what I am facing is described here:
http://www.sqlalchemy.org/docs/04/plugins.html#plugins_associationproxy_
building
But unfortunately the situation is not close enough to help me.
Now, based on the example above, here is the issue:
stocks = Table(stocks, meta,
Column('symbol', String(10), primary_key=True),
Column('description', String(100), nullable=False),
Column('last_price', Numeric)
)
brokers = Table(brokers, meta,
Column('id', Integer,primary_key=True),
Column('name', String(100), nullable=False)
)
holdings = Table(holdings, meta,
Column('broker_id', Integer,
ForeignKey('brokers.id'), primary_key=True),
Column('symbol', String(10),
ForeignKey('stocks.symbol'), primary_key=True),
Column('shares', Integer),
Column('date', DateTime, primary_key=True) # -- HERE IS THE
DIFFERENCE
)
The example in the doc provides a nice way to retrieve one holding:
holding = broker.holdings[stock]
But what if holdings are parameterized by a new attribute ('date' in
our
case)?
How would you implement a solution allowing you to do something like
this:
date = datetime.datetime(2007,1,1)
holdings = broker.holdingsByDate[date]
where 'holdings' is a collection of what the broker is holding at a
given date.
A couple approaches come to mind. How many Holdings rows in total do
you expect per Broker?
If the number is large, then on-demand querying for chunks of the set by
date could be a good option. That can be wrapped up in a dict-like
class with __getattr__ for an interface like holdingsByDate[date]. You
could cache too, if you wanted. No mapper relation from Broker-Holding
would be needed, but you'd want Holding-Broker.
If the number is small enough that loading the entire collection of
associated Holdings per Broker isn't a big deal, then a whole bunch of
options come to mind, but none of these are built-in.
- A dict-like proxy similar to the large collection approach, except
scanning .holdings in-memory and filtering by .date on demand
- Add some append/remove event handlers to the collection backing
.holding, and use them to maintain a obj.holdingsByDate dictionary
transparently
- Use the collections framework to build a fully featured partitioned
collection type
These are all pretty simple to implement- something like #2 is just a
few lines of code.
An implementation of #3 (a dictionary of lists with full instrumented
goodness) is probably not a ton of work at all and could make a good
addition to either the collections or ext package.
--~--~-~--~~~---~--~~
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: Relating objects of the same type Using Joined Table Inheritance
you relation should have argument like
primary_join= engineers.c.hired_by_id==managers.c.employee_id
or similar. i do not know for sure as i've done a layer on top of SA that
stores most of this knowledge, so i dont bother with it. Have a look at
dbcook.sf.net. u may use it as ORM to build and use your model, or use is
just to describe your model then dump the equivalent SA-calls (see
usage/example/example*), and use that one, dropping the dbcook.
As of joined-inh, SA supports all the 3 single/concrete/joined, but real
polymorphism does not work for the concrete case. Also, joined inh is done
via left-outer-join which is simpler/faster than an union - although that is
also an option.
have fun
svilen
Dave E wrote:
http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_mapper_inheritance_joined
My question is what if you wanted to add a 'hired' field to say the
employees table object (from the example in that link) that
references a manager object. In this way I imagine that we are making
another reference to an employee object and might be an issue when
trying to figure out the join. So I'd imagine that the tables look
like:
employees = Table('employees', metadata,
Column('employee_id', Integer, primary_key=True),
Column('name', String(50)),
Column('type', String(30), nullable=False)
)
engineers = Table('engineers', metadata,
Column('employee_id', Integer, ForeignKey('employees.employee_id'),
primary_key=True),
Column('hired_by_id',Integer,ForeignKey('managers.employee_id')),
###INTERESTING PART
Column('engineer_info', String(50)),
)
managers = Table('managers', metadata,
Column('employee_id', Integer, ForeignKey('employees.employee_id'),
primary_key=True),
Column('manager_data', String(50)),
)
and the mappers look like:
mapper(Employee, employees, polymorphic_on=employees.c.type,
polymorphic_identity='employee')
mapper(Engineer, engineers, inherits=Employee,
polymorphic_identity='engineer',
properties={
'hirer':relation(Manager,uselist=False,backref='hired')
})
mapper(Manager, managers, inherits=Employee,
polymorphic_identity='manager')
But the error message you'd get if you do this is:
sqlalchemy.exceptions.ArgumentError: Error determining primary and/or
secondary join for relationship 'Engineer.hirer (Manager)'. If the
underlying error cannot be corrected, you should specify the
'primaryjoin' (and 'secondaryjoin', if there is an association table
present) keyword arguments to the relation() function (or for
backrefs, by specifying the backref using the backref() function with
keyword arguments) to explicitly specify the join conditions. Nested
error is Can't determine join between 'Join object on
employees(14680464) and engineers(14680880)' and '_FromGrouping
object'; tables have more than one foreign key constraint relationship
between them. Please specify the 'onclause' of this join explicitly.
Might I add that this is an extremely informative error message!
Basically, my question is how do I satisfy this requirement as
described by the error message? I have to make my join more
specific? How would I do that?
And another question, is Joined Table Inheritance a common thing to do
in SQLAlchemy?
--~--~-~--~~~---~--~~
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: dictionary returning a collection
Dear All,
Just to let you know how I solved my problem: according to the solution
#3 described below by Jason, I created in my local sqlalchemy
installation a MultiValuedMappedCollection class in the
sqlalchemy.orm.collections package Based on the MappedCollection class.
The newly created class can then be referenced in a method:
def multi_valued_mapped_collection(keyfunc):
return lambda: MultiValuedMappedCollection(keyfunc)
(Similarly to the existing mapped_collection(keyfunc))
The only change I brought to the MappedCollection resides in the 'set'
method and was really simple (a little too simplistic to be included
as-is in the framework I think):
def set(self, value, _sa_initiator=None):
Add a value to the collection of multi-values, with a key
provided by this instance's keyfunc.
key = self.keyfunc(value)
if not self.has_key(key):
# creates a list on the fly if needed
self.__setitem__(key, [], _sa_initiator)
self.__getitem__(key).append(value)
set = collection.internally_instrumented(set)
set = collection.appender(set)
So that the inherited dict simply holds a list (created on-the-fly)
instead of the value directly. Values are then accumulated in the
existing list each time dict[key] = value is called.
- not sure that people want to have something exactly like this
eventually embedded in the framework, but it helped in solving my
short-term needs and may also help any good soul willing to implement it
properly ...
Bottom-line:
---
Using a declaration like this in the Broker mapper:
'holdingsByDate': relation(Holding,
collection_class=attribute_multi_valued_mapped_collection('date'))
it allows me to write in my app:
date = datetime.datetime(2007,1,1)
holdings = broker.holdingsByDate[date]
Christophe
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
On Behalf Of jason kirtland
Sent: Thursday, January 17, 2008 7:53 PM
To: sqlalchemy@googlegroups.com
Subject: [sqlalchemy] Re: dictionary returning a collection
Christophe Alexandre wrote:
Dear All,
Send me some study material on DBMS + $100 !
Or if it fits you better, can you please help on the issue described
below?
The closest situation to what I am facing is described here:
http://www.sqlalchemy.org/docs/04/plugins.html#plugins_associationproxy_
building
But unfortunately the situation is not close enough to help me.
Now, based on the example above, here is the issue:
stocks = Table(stocks, meta,
Column('symbol', String(10), primary_key=True),
Column('description', String(100), nullable=False),
Column('last_price', Numeric)
)
brokers = Table(brokers, meta,
Column('id', Integer,primary_key=True),
Column('name', String(100), nullable=False)
)
holdings = Table(holdings, meta,
Column('broker_id', Integer,
ForeignKey('brokers.id'), primary_key=True),
Column('symbol', String(10),
ForeignKey('stocks.symbol'), primary_key=True),
Column('shares', Integer),
Column('date', DateTime, primary_key=True) # -- HERE IS THE
DIFFERENCE
)
The example in the doc provides a nice way to retrieve one holding:
holding = broker.holdings[stock]
But what if holdings are parameterized by a new attribute ('date' in
our
case)?
How would you implement a solution allowing you to do something like
this:
date = datetime.datetime(2007,1,1)
holdings = broker.holdingsByDate[date]
where 'holdings' is a collection of what the broker is holding at a
given date.
A couple approaches come to mind. How many Holdings rows in total do
you expect per Broker?
If the number is large, then on-demand querying for chunks of the set by
date could be a good option. That can be wrapped up in a dict-like
class with __getattr__ for an interface like holdingsByDate[date]. You
could cache too, if you wanted. No mapper relation from Broker-Holding
would be needed, but you'd want Holding-Broker.
If the number is small enough that loading the entire collection of
associated Holdings per Broker isn't a big deal, then a whole bunch of
options come to mind, but none of these are built-in.
- A dict-like proxy similar to the large collection approach, except
scanning .holdings in-memory and filtering by .date on demand
- Add some append/remove event handlers to the collection backing
.holding, and use them to maintain a obj.holdingsByDate dictionary
transparently
- Use the collections framework to build a fully featured partitioned
collection type
These are all pretty simple to implement- something like #2 is just a
few lines of code.
An implementation of #3 (a dictionary of lists with full instrumented
goodness) is probably not a ton of work at all and could make a good
addition to either the collections or ext package.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
[sqlalchemy] Filter by year in datetime column
Hello, pleas, i have begginer problem and question: In table (database is sqlite) is colum for create date (create_date = Field(DateTime, default = datetime.now)) I need query from table, with all item where have year of create date 2007. Is this the right way ? (this don`t work) data = Table.query().filter(func.year(Mikropost.c.create_date) == 2008) --~--~-~--~~~---~--~~ 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: Filter by year in datetime column
Would create_date = '2007-01-01' and create_date '2008-01-01' be acceptable? If so, something like this should work from sqlalchemy import and_ from datetime import date data = Table.query().filter(and_([Mikropost.c.create_date = date(2007, 1, 1), Mikropost.c.create_date date(2008, 1, 1)])) Hope that helps, Simon -Original Message- From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 18 January 2008 13:22 To: sqlalchemy Subject: [sqlalchemy] Filter by year in datetime column Hello, pleas, i have begginer problem and question: In table (database is sqlite) is colum for create date (create_date = Field(DateTime, default = datetime.now)) I need query from table, with all item where have year of create date 2007. Is this the right way ? (this don`t work) data = Table.query().filter(func.year(Mikropost.c.create_date) == 2008) --~--~-~--~~~---~--~~ 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: Filter by year in datetime column
--On 18. Januar 2008 12:08:46 -0500 Rick Morrison [EMAIL PROTECTED]
wrote:
There are no generic date functions in SQLAlchemy (although work has begun
on them). So for now, you'll need to use date functions native to your
database.
For sqlite something like,
func.strftime('%Y', Mikropost.c.create_date) == '2008'
should work -- you may need to add additional percent signs depending on
how badly the '%Y' literal is mangled during parameter collection.
Such operations will likely trigger a full table scan. Depending on the
size of your data you might adjust the data model to the need of your query
and/or think about functional indexes (in case your database provides this
functionality).
Andreas
pgpQvohGvKt7k.pgp
Description: PGP signature
[sqlalchemy] Re: Error while setting default dates on a table with default=func.now()
On Jan 18, 2008, at 10:59 AM, Hermann Himmelbauer wrote:
Hi,
I have a table that looks appr. like this (I deleted some columns):
table_acc = Table(
'konto', metadata,
Column('kontoid', Integer, primary_key=True, key='accid'),
Column('erdat', DateTime, default=func.now(), key='datopen'))
What I'd like to accomplish is that when during an insert the value
for the
column erdat is None, the current time should be inserted. But
when I try
to insert data, the following error is raised:
File /home/dusty/prog/python_modules/sqlalchemy/engine/base.py,
line 935, in
_handle_dbapi_exception
raise exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
OperationalError: (OperationalError) no such function: now
u'INSERT INTO
konto (kontonr, ktobez, kapital, disposal, ktogeb, gebuehren,
rahmen, erdat,
datlabs, standlabs, ktostatus, datsal, waehrungid) VALUES
(?, ?, ?, ?, ?, ?, ?, now(), now(), ?, ?, ?, ?)'
['5', 'Bankkonto', '01000.50', '11000.50',
'0.00', '0.00', '0.00', '0.00',
'A',
None, 1]
What can I do about this? It seems, that now() is not recognized,
but how is
this possible?
I am using SA 0.4.1, SA0.4.2p3 + Python-2.4.4 and sqllite.
with sqlite use func.current_timestamp() instead of now().
--~--~-~--~~~---~--~~
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: Error while setting default dates on a table with default=func.now()
On Jan 18, 2008, at 5:29 PM, Hermann Himmelbauer wrote:
Am Freitag, 18. Januar 2008 22:43 schrieb Michael Bayer:
On Jan 18, 2008, at 10:59 AM, Hermann Himmelbauer wrote:
Hi,
I have a table that looks appr. like this (I deleted some columns):
table_acc = Table(
'konto', metadata,
Column('kontoid', Integer, primary_key=True, key='accid'),
What can I do about this? It seems, that now() is not recognized,
but how is
this possible?
I am using SA 0.4.1, SA0.4.2p3 + Python-2.4.4 and sqllite.
with sqlite use func.current_timestamp() instead of now().
Yes, this did the trick - thank's a lot!
One more question: I user sqlite only for testing my database
application, for
the real thing I'll use MaxDB and/or postgresql; Will
func.current_timestamp() work for these databases, or should I set
up some
proxy function that returns either now() or current_timestamp()
regarding to
the database?
func.current_timestamp() should be supported on most dbs (not sure
about MaxDB), but its also a generic function right now so it should
fire off the appropriate equivalent if its not available on a
particular DB. we should probably add now() as a generic as well.
its a new feature we havent built out yet.
--~--~-~--~~~---~--~~
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] Problems connecting to Postgresql using collective.lead
I am getting an error connecting to Postgresql:
ImportError: unknown database 'psycopg'
The back story is that I am attempting to modify an example given in
Martin Aspeli's Book: Professional Plone Development (Chapter 12 -
page 274-. He recommends using SQLAlchemy, and it looks very cool,
kind of similar to a SQLBuilder class I put together a few years ago,
but more advanced.
I don't know really what to provide as I am completely new to
SQLAlchemy, but the most relevent thing that I can see is the class
that extends collective.lead.Database and the properties class:
from persistent import Persistent
from zope.interface import implements
from zope.component import getUtility
from collective.lead import Database
from mls.agent.interfaces import IDatabaseSettings
from sqlalchemy.engine.url import URL
from sqlalchemy import Table, mapper, relation
from mls.agent.person import Person
from mls.agent.office import Office
class MLSDatabaseSettings(Persistent):
Database connection settings
We use raw fields here so that we can more easily use a
zope.formlib
form in the control panel to configure it. This is registered as a
persistent local utility, with name 'optilux.reservations', which
is
then used by collective.lead.interfaces.IDatabase to find
connection settings.
implements(IDatabaseSettings)
drivername = 'psycopg'
hostname = 'localhost'
port = 5432
username = 'postgres'
password = 'postgres'
database = 'trend-dev'
class MLSDatabase(Database):
The reservations database - registered as a utility providing
collective.lead.interfaces.IDatabase and named
'optilux.reservations'
@property
def _url(self):
settings = getUtility(IDatabaseSettings)
return URL(drivername=settings.drivername,
username=settings.username,
password=settings.password, host=settings.hostname,
port=settings.port, database=settings.database)
def _setup_tables(self, metadata, tables):
Map the database structure to SQLAlchemy Table objects
tables['person'] = Table('person', metadata, autoload=True)
tables['office'] = Table('office', metadata, autoload=True)
def _setup_mappers(self, tables, mappers):
Map the database Tables to SQLAlchemy Mapper objects
mappers['person'] = mapper(Person, tables['person'],
properties = {'office' : relation(Office),});
mappers['office'] = mapper(Office, tables['office'])
Also the test case that I am running:
self.loginAsPortalOwner()
from zope.component import getUtility
from mls.agent.interfaces import IDatabaseSettings
settings = getUtility(IDatabaseSettings)
settings.drivername = 'psycopg'
settings.username = 'postgres'
settings.password = 'postgres'
settings.hostname = 'localhost'
settings.database = 'trend-dev'
from collective.lead.interfaces import IDatabase
db=getUtility(IDatabase, name='db.mls')
from mls.agent.interfaces import IPersonLocator
locator = getUtility(IPersonLocator)
locator.persons_for_mls_person_code(data).last_name;
answer
--~--~-~--~~~---~--~~
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] Error while setting default dates on a table with default=func.now()
Hi,
I have a table that looks appr. like this (I deleted some columns):
table_acc = Table(
'konto', metadata,
Column('kontoid', Integer, primary_key=True, key='accid'),
Column('erdat', DateTime, default=func.now(), key='datopen'))
What I'd like to accomplish is that when during an insert the value for the
column erdat is None, the current time should be inserted. But when I try
to insert data, the following error is raised:
File /home/dusty/prog/python_modules/sqlalchemy/engine/base.py, line 935, in
_handle_dbapi_exception
raise exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
OperationalError: (OperationalError) no such function: now u'INSERT INTO
konto (kontonr, ktobez, kapital, disposal, ktogeb, gebuehren, rahmen, erdat,
datlabs, standlabs, ktostatus, datsal, waehrungid) VALUES
(?, ?, ?, ?, ?, ?, ?, now(), now(), ?, ?, ?, ?)'
['5', 'Bankkonto', '01000.50', '11000.50',
'0.00', '0.00', '0.00', '0.00', 'A',
None, 1]
What can I do about this? It seems, that now() is not recognized, but how is
this possible?
I am using SA 0.4.1, SA0.4.2p3 + Python-2.4.4 and sqllite.
Best Regards,
Hermann
--
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7
--~--~-~--~~~---~--~~
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: Readonly objects/protecting objects from modifications
Hi all,
it's only now that I came across this interesting discussion.
I tried similar things but what I wanted to protect was my cached
data. And session.merge(obj, dont_load=True) triggers these
AssertionErrors. :-(
So I went for a MapperExtension instead. The after_update method can
still prevent changes from being committed to the database. This
solution is not very elegant, however.
Best regards
Klaus
On 22 Dez. 2007, 17:03, Michael Bayer [EMAIL PROTECTED]
wrote:
On Dec 22, 2007, at 12:34 AM, Andreas Jung wrote:
--On 21. Dezember 2007 16:33:34 -0500 Michael Bayer [EMAIL PROTECTED]
wrote:
On Dec 21, 2007, at 3:13 PM, Rick Morrison wrote:
I think the only way something like this should be done is as a test
fixture which decorates classes during unit tests.It would be
fairly clumsy to have in production code.
If you have coworkers who write broken code, the way you solve that
is
by having unit tests which will fail when the coworkers in question
do
something theyre not supposed to. If other people are writing code
that sets attrbutes its not supposed to and breaks things, you need
more tests to catch those conditions. If youre putting code into
production that hasnt been tested, then you need a build process,
automated testing, etc.There is definitely a best practice here
and test driven development is it.
With all respect, this is not a useful answer. Even with tests
(unittests and weeks of manual tests) I had the case that a simple
programming error
(of my own) produced a data disaster after some weeks. There is no
100% test coverage. Tests don't solve all problems. There is
sometimes the need for a better security belt.
I am certainly suggesting a fixture that detects illegal assignments
to attributes. That it be limited to just unit tests is only a
suggestion.To establish this functionality regardless of
environment, like Rick said just create properties which prohibit
assignment. Create mappers like this:
class AttrGetter(object):
def __init__(self, name):
self.name = name
def __get__(self, instance, name):
if instance is None:
return self
return getattr(instance, '_' + name)
def __set__(self, instance, value):
raise AssertionError(Sets are not allowed)
def __delete__(self, instance):
raise AssertionError(Deletes are not allowed)
class MyClass(object):
somecolumn = AttrGetter('somecolumn')
someothercolumn = AttrGetter('someothercolumn')
mapper(MyClass, sometable, properties={
'_somecolumn':sometable.c.somecolumn,
'_someothercolumn':sometable.c.someothercolumn
})
To automate the above process with no modifications to source code,
create an instrumented mapper() function which applies the above
recipe to all table columns:
from sqlalchemy.orm import mapper as _mapper
def mapper(cls, table, **kwargs):
attrs = {}
for c in table.c:
attrs['_' + c.key] = c
setattr(cls, c.key, AttrGetter(c.key))
properties = kwargs.setdefault('properties', {})
properties.update(attrs)
return _mapper(cls, table, **kwargs)
Hope this helps.
--~--~-~--~~~---~--~~
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: Filter by year in datetime column
There are no generic date functions in SQLAlchemy (although work has begun
on them). So for now, you'll need to use date functions native to your
database.
For sqlite something like,
func.strftime('%Y', Mikropost.c.create_date) == '2008'
should work -- you may need to add additional percent signs depending on how
badly the '%Y' literal is mangled during parameter collection.
--~--~-~--~~~---~--~~
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: Filter by year in datetime column
Such operations will likely trigger a full table scan SQLite dates are stored as strings anyway, AFAIK there is little one can do to avoid table-scans in SQLite based solely on date criteria. I use julian dates stored as integers when working with large datasets in SQLite, and convert as needed. Be interested to hear what others do about this. --~--~-~--~~~---~--~~ 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: Filter by year in datetime column
Rick Morrison wrote: Such operations will likely trigger a full table scan SQLite dates are stored as strings anyway, AFAIK there is little one can do to avoid table-scans in SQLite based solely on date criteria. I use julian dates stored as integers when working with large datasets in SQLite, and convert as needed. Be interested to hear what others do about this. well one can decompose dates into day-month-year triple of ints, but this definitely will need some wrapper to be made easy-to-use (and may need composite keys for example). --~--~-~--~~~---~--~~ 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: Error while setting default dates on a table with default=func.now()
Am Freitag, 18. Januar 2008 22:43 schrieb Michael Bayer:
On Jan 18, 2008, at 10:59 AM, Hermann Himmelbauer wrote:
Hi,
I have a table that looks appr. like this (I deleted some columns):
table_acc = Table(
'konto', metadata,
Column('kontoid', Integer, primary_key=True, key='accid'),
What can I do about this? It seems, that now() is not recognized,
but how is
this possible?
I am using SA 0.4.1, SA0.4.2p3 + Python-2.4.4 and sqllite.
with sqlite use func.current_timestamp() instead of now().
Yes, this did the trick - thank's a lot!
One more question: I user sqlite only for testing my database application, for
the real thing I'll use MaxDB and/or postgresql; Will
func.current_timestamp() work for these databases, or should I set up some
proxy function that returns either now() or current_timestamp() regarding to
the database?
Best Regards,
Hermann
--
[EMAIL PROTECTED]
GPG key ID: 299893C7 (on keyservers)
FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
