Re: [sqlalchemy] Not able to filter column by datetime
Thank you Michael, I think sometimes I am blind xD
Regards
El miércoles, 15 de abril de 2015, 15:13:38 (UTC+2), Michael Bayer escribió:
On 4/15/15 6:52 AM, Juan Antonio Ibáñez wrote:
Hello,
I have dozens of queries filtering DateTime columns but I have got
one I don't know why it doesn't work. I have:
---
q = DBSession.query(func.sum(RecargaCredito.importe), Local.nombre)\
.join((Local, RecargaCredito.locales_id == Local.id))
fl_desde = (datetime.now() - timedelta(days=7)).replace(hour=0,
minute=0, second=0)
fl_hasta = datetime.now().replace(hour=23, minute=59, second=59)
q = q.filter(RecargaCredito.fechayhora = fl_desde).\
filter(RecargaCredito.fechayhora = fl_hasta)
q = q.group_by(RecargaCredito.locales_id)
f = q.all()
---
RecargaCredito looks:
---
class RecargaCredito(DeclarativeBase):
__tablename__ = 'recargas_credito'
__table_args__ = {'mysql_engine':'InnoDB'}
id=Column(Integer, primary_key=True)
fechayhora = DateTime
importe = Column(DECIMAL(9,2))
locales_id=Column(Integer, ForeignKey('locales.id'))
local = relation('Local', remote_side = 'Local.id')
---
and I get error 'TypeError: can't compare datetime.datetime to
VisitableType'
you seem to be missing Column() for fechayhora:
fechayhora = Column(DateTime)
I've checked fl_desde and fl_hasta are python datetime objects
Do you know what that error says?
Regards
--
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 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] Not able to filter column by datetime
Hello,
I have dozens of queries filtering DateTime columns but I have got one I
don't know why it doesn't work. I have:
---
q = DBSession.query(func.sum(RecargaCredito.importe), Local.nombre)\
.join((Local, RecargaCredito.locales_id == Local.id))
fl_desde = (datetime.now() - timedelta(days=7)).replace(hour=0, minute=0,
second=0)
fl_hasta = datetime.now().replace(hour=23, minute=59, second=59)
q = q.filter(RecargaCredito.fechayhora = fl_desde).\
filter(RecargaCredito.fechayhora = fl_hasta)
q = q.group_by(RecargaCredito.locales_id)
f = q.all()
---
RecargaCredito looks:
---
class RecargaCredito(DeclarativeBase):
__tablename__ = 'recargas_credito'
__table_args__ = {'mysql_engine':'InnoDB'}
id=Column(Integer, primary_key=True)
fechayhora = DateTime
importe = Column(DECIMAL(9,2))
locales_id=Column(Integer, ForeignKey('locales.id'))
local = relation('Local', remote_side = 'Local.id')
---
and I get error 'TypeError: can't compare datetime.datetime to
VisitableType'
I've checked fl_desde and fl_hasta are python datetime objects
Do you know what that error says?
Regards
--
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] DateTime format
Hello, I am using Sqlalchemy under Turbogears and I'd like to know which is the best way to customize column to string conversion of a DateTime column. I need to do it in an automatic way instead having to format it before showing. Regards -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/cU712dWA_kMJ. 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] Multi-values custom mapped collection
El 04/06/12 16:37, Michael Bayer escribió:
On Jun 1, 2012, at 12:30 PM, Antonio Beamud Montero wrote:
El 01/06/12 16:59, Michael Bayer escribió:
@appender needs to be a method that just accepts a mapped object to be added to
the collection. then, @internally_instrumented is needed to prevent the
collections system from placing additional insturmentation on standard methods
like __setitem__. Also building custom dictionary collections is very
difficult in any case (for me as well) so you were close, and that's pretty
good !
Only one more question.. Querying for example the ULine object, always give me
the permissions for all objects.. Exists any way to filter that permissions by
the User object I filter in the query, i.e. something like:
objs =
session.query(ULine).join(PermissionAssoc).join(Permission).join(User).filter(User.username
== u'kratos').all()
print objs[0].permissions
MyMappedCollection({u'read': [__main__.Permission object at
0xa04442c,__main__.Permission object at 0xa04454c]})
haven't looked at your example again but you'd work it out in SQL first to
figure out the query you want, then convert to the ORM. Is the issue that you
don't know what the SQL is, or just how to get the Query to produce it ?
The query is easy, my problem is with the property 'permissions',
associated to a object. The definition in the PermissionAssoc is:
permissions = orm.relationship(Permission,
backref='association',
lazy=False,
collection_class=MyMappedCollection)
But I don't know how to configure this relationship to filter by a user
(I don't know if it's possible). Like a dynamic relationship (to not
query always all the permissions for all user associated to this object).
Greetings.
--
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] Multi-values custom mapped collection
Hi all: I'm trying to implement a permission system usign polymorphic associations. In my example only one object has permissions associated (ULine). The problem is trying to use a mapping with permission names as keys, and values Permission objects with a 1:1 user relation. A complete example here: http://pastebin.com/SqCfBCFp But I can't get this working, the next error is raised: child_state, child_dict = instance_state(child), \ AttributeError: 'list' object has no attribute '_sa_instance_state' A lot of 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.
Re: [sqlalchemy] Multi-values custom mapped collection
El 01/06/12 16:59, Michael Bayer escribió: @appender needs to be a method that just accepts a mapped object to be added to the collection. then, @internally_instrumented is needed to prevent the collections system from placing additional insturmentation on standard methods like __setitem__. Also building custom dictionary collections is very difficult in any case (for me as well) so you were close, and that's pretty good ! A lot of thanks, Michael.. now it it works like a charm ;-) class MyMappedCollection(dict): def __init__(self, data=None): if data: self.update(data) @collection.appender def _add_perm(self, pobj): l = dict.setdefault(self, pobj.permission, []) l.append(pobj) @collection.internally_instrumented def __setitem__(self, permission, pobj): self._add_perm(pobj) @collection.remover def _remove(self, perm): self[perm.permission].remove(perm) @collection.iterator def __iter__(self): for vals in self.itervalues(): for perm in vals: yield perm On Jun 1, 2012, at 9:38 AM, Antonio Beamud Montero wrote: Hi all: I'm trying to implement a permission system usign polymorphic associations. In my example only one object has permissions associated (ULine). The problem is trying to use a mapping with permission names as keys, and values Permission objects with a 1:1 user relation. A complete example here: http://pastebin.com/SqCfBCFp But I can't get this working, the next error is raised: child_state, child_dict = instance_state(child), \ AttributeError: 'list' object has no attribute '_sa_instance_state' A lot of 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. -- 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] Multi-values custom mapped collection
El 01/06/12 16:59, Michael Bayer escribió:
@appender needs to be a method that just accepts a mapped object to be added to
the collection. then, @internally_instrumented is needed to prevent the
collections system from placing additional insturmentation on standard methods
like __setitem__. Also building custom dictionary collections is very
difficult in any case (for me as well) so you were close, and that's pretty
good !
Only one more question.. Querying for example the ULine object, always
give me the permissions for all objects.. Exists any way to filter that
permissions by the User object I filter in the query, i.e. something like:
objs =
session.query(ULine).join(PermissionAssoc).join(Permission).join(User).filter(User.username
== u'kratos').all()
print objs[0].permissions
MyMappedCollection({u'read': [__main__.Permission object at
0xa04442c, __main__.Permission object at 0xa04454c]})
One of this objects is for the user john, and other for the user
kratos, and I only want the Permission for the user kratos.
P.D: Other options is create the mapping using the user_id key and get
something like:
print objs[0].permissions[user1.id]
[__main__.Permission object at 0xa04442c]
print objs[0].permissions[user2.id]
[__main__.Permission object at 0xa04454c]
But this always loads all the Permissions too.
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] Count over aliased JOIN
Hello I am joining one table with itself so I am using alias as: alias_usuarios1 = aliased(Usuario) alias_usuarios2 = aliased(Usuario) usuarios = DBSession.query(alias_usuarios1, alias_usuarios2.nombre).filter(alias_usuarios1.resellers_id == alias_usuarios2.id) All works ok except when I try to count how many rows I'd got. If I apply count() to previous query I get following SQL code: SELECT count(1) AS count_1 FROM (SELECT usuarios.id AS usuarios_id, usuarios_1.nombre AS usuarios_1_nombre FROM usuarios, usuarios AS usuarios_1, usuarios AS usuarios_2 WHERE usuarios_2.resellers_id = usuarios_1.id) AS anon_1 It is joining three tables instead two to make de JOIN. Is there any way to mend it? Regards -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Subtransactions problem
Hello! I am dealing with Turbogears and SQL Alchemy. I need to make one import from a CSV file to a MySQL DB. I need also to controll which rows fails on import. I am working with the next code inside a loop: try: session.begin(subtransactions=True) session.add(importe) session.flush() nimportados += 1 except Exception as ex: log.debug(ex) session.rollback() nerroneos += 1 During exec I get: - 12:55:45,598 INFO [sqlalchemy.engine.base.Engine.0x...f1ac] BEGIN 12:55:45,600 INFO [sqlalchemy.engine.base.Engine.0x...f1ac] INSERT INTO importes (tarifas_id, prefijo, destino, importe, tipo, pc, psi, ps, sg) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) 12:55:45,600 INFO [sqlalchemy.engine.base.Engine.0x...f1ac] [u'1', 'PREFIJO', 'DESTINO', 'IMPORTE', u'', 0, 60, 60, 0] 12:55:45,601 INFO [sqlalchemy.engine.base.Engine.0x...f1ac] ROLLBACK 12:55:45,603 DEBUG [svbpanel.controllers] (IntegrityError) (1062, Duplicate entry 'PREFIJO' for key 'prefijo') u'INSERT INTO importes (tarifas_id, prefijo, destino, importe, tipo, pc, psi, ps, sg) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)' [u'1', 'PREFIJO', 'DESTINO', 'IMPORTE', u'', 0, 60, 60, 0] 12:55:45,603 DEBUG [svbpanel.controllers] The transaction is inactive due to a rollback in a subtransaction. Issue rollback() to cancel the transaction. - Peopel on TG2 group told me about the appearance of not init the subtransaction. I thing TG2 init a own transaction in an automatic way so I should work with subtransactions (I think) Commiting instead flushing doesn't work... Any idea about this problem? Regards -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Variable intersect argument list
Hi all: I'm trying to generate a intersect statement with variable argument list, I've tried something like: ... ltags = ['news', 'publicity'] s = select([tags.c.channel], tags.c.tag == ltags[0]) for tag in ltags[1:]: s.intersect(select([tags.c.channel], tags.c.tag == tag)) s1 = select([channels.c.channel], channels.c.channel.in_(s)) print e.execute(s1).fetchall() But this give me wrong results... What's the correct way of doing this kind of queries? Thanks. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Variable intersect argument list
Alex Brasetvik escribió: On Dec 9, 2009, at 12:41 , Antonio Beamud Montero wrote: s.intersect(select([tags.c.channel], tags.c.tag == tag)) This is returning a new select which is the intersection --- which you are throwing away. It is *not* modifying s in-place. You probably want to do s = s.intersect(select([tags.c.channel], tags.c.tag == tag)) Ops.. thank you... I've solved it using this approach: lsel = [] for t in ltags: lsel.append(select(.)) xsec = intersect(*lsel) Now it works perfect. Greetings. -- Alex Brasetvik -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Thread safes transactions
Hi all: I'm using only sqlalchemy sql expression language, and in documentation appears that transactions objects are not thread safe.. Need I to implement a decorator like this? def transactional(fn): def transact(self, *args): c = self.engine.connect() lock.acquire() try: t = c.begin() fn(self, c, *args) t.commit() except Exception, e: t.rollback() raise finally: c.close() lock.release() transact.__name__ = fn.__name__ Can I use sessions without define object mappings, to use session thread local model, etc? 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] postgresql rules and update
hi all,
I've partition a table in PostgreSQL, with rules on UPDATE, DELETE and INSERT.
When I INSERT a row, ok, but when i UPDATE a row, the program raise:
ConcurrentModificationError: Updated rowcount 0 does not match number of
objects updated 1
I know that when i use a RULE the command status return 0:
ma=# SELECT * from smoduls.datirim_prev where id=6;
turno | id | rim_id | prev_id
---+++-
d | 6 | 2 | 12
(1 row)
ma=# UPDATE smoduls.datirim_prev set rim_id=3 where id=6;
UPDATE 0
^^
ma=# SELECT * from smoduls.datirim_prev where id=6;
turno | id | rim_id | prev_id
---+++-
d | 6 | 3 | 12
(1 row)
but is sane to catch the exception with pass ?? what can I do to resolve ?
tanks in advance ... I hope that you've understood ;-))
--
#include stdio.h
int main(void){char c[]={10,65,110,116,111,110,105,111,32,98,97,114,98,111,110,
101,32,60,104,105,110,100,101,109,105,116,64,116,105,115,99,97,108,105,110,101,
116,46,105,116,62,10,10,0};printf(%s,c);return 0;}
--~--~-~--~~~---~--~~
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: postgresql rules and update
* mercoledì 13 giugno 2007, alle 09:15, Michael Bayer wrote :
SQLAlchemy's ORM relies upon cursor.rowcount after an UPDATE or
DELETE to get the number of rows affected. Why exactly does your rule
cause this to fail ?
because is a 'INSTEAD RULE' the base table 'prev' has no row ...
the table 'prev2007' INHERITS from 'prev' ...
and the db return a rowcount of '0' although the row is updated into
'prev2007' ...
CREATE RULE previsione2007_upd AS ON UPDATE TO rsm.prev
WHERE NEW.data_m BETWEEN '1/1/2007' AND '31/12/2007'
DO INSTEAD UPDATE rsm.prev2007
SET nave_id = NEW.nave_id,
...
if no way around that, id have to provide a
hook into the postgres.py dialect to disable rowcount.
you might
want to experiment with raw DBAPI code and see if you can get
cursor.rowcount to behave properly in conjunction with your database
setup.
I'll try ... tanks for the answer ..
--
#include stdio.h
int main(void){char c[]={10,65,110,116,111,110,105,111,32,98,97,114,98,111,110,
101,32,60,104,105,110,100,101,109,105,116,64,116,105,115,99,97,108,105,110,101,
116,46,105,116,62,10,10,0};printf(%s,c);return 0;}
--~--~-~--~~~---~--~~
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 array fields in postgres
* sabato 20 gennaio 2007, alle 02:57, exhuma.twn wrote :
Sorry,
but I don't understand how create, insert or select data with the array
field in postgres.
I use a composite type in PostgreSQL
fatture=# \d datirim
Composite type public.datirim
Column | Type
+
rim| smallint
hat| time without time zone
hin| time without time zone
hfn| time without time zone
turno | character(1)
in sqlalchemy:
import sqlalchemy as sqa
import re
class _RimType(sqa.types.TypeEngine):
def __init__(self):
pass
def get_col_spec(self):
pass
def convert_bind_param(self, value, engine):
return '('+,.join(value)+')'
def convert_result_value(self, value, engine):
arr = re.sub('[()]','',value).split(',')
return 'Nome %s HAT %s HIN %s HFN %s' % \
(arr[0],arr[1],arr[2],arr[3])
.
_elem_tbl=sqa.Table('elem', _mdata,
sqa.Column('id', sqa.Integer, primary_key=True),
sqa.Column('rim1', _RimType),
sqa.Column('rim2', _RimType),
sqa.Column('tempo', sqa.Time)
)
you can use a similar class for arrays
bye ...
PS sorry for my english ;-)))
--
#include stdio.h
int main(void){char c[]={10,65,110,116,111,110,105,111,32,98,97,114,98,111,110,
101,32,60,104,105,110,100,101,109,105,116,64,116,105,115,99,97,108,105,110,101,
116,46,105,116,62,10,10,0};printf(%s,c);return 0;}
--~--~-~--~~~---~--~~
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: Postgre e pg_largeobject
* mercoledì 17 gennaio 2007, alle 09:30, Michael Bayer wrote :
print Content-type:text/pdf\n\n# whatever the header is for pdf
for chunk in res.pdf:
sys.stdout.write(chunk.data)
I would agree that the approach taken by pg_largeobject is a useful
approach in that you can read just chunked sections.
I've converted the column in bytea ... I've realized that I've no benefits
using BLOB and pg_largeobject ... and the .pdf is much more usable in
this form ...
tanks a lot ...
--
#include stdio.h
int main(void){char c[]={10,65,110,116,111,110,105,111,32,98,97,114,98,111,110,
101,32,60,104,105,110,100,101,109,105,116,64,116,105,115,99,97,108,105,110,101,
116,46,105,116,62,10,10,0};printf(%s,c);return 0;}
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
