[sqlalchemy] lower / upper case
I have a users table and I want to query the usernames column. I want my query to ignore the upper/lower casing. So the following searches should all match John: john, jOhn, johN, JOhn, and so on. My query at the moment is a follows: names = queryselect(users.c.username.startswith(john)) How can I modify the query to obtain the above results? 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: lower / upper case
there was some thread about this 2week ago or so, look for ILIKE On Wednesday 18 April 2007 14:58:41 Disrupt07 wrote: I have a users table and I want to query the usernames column. I want my query to ignore the upper/lower casing. So the following searches should all match John: john, jOhn, johN, JOhn, and so on. My query at the moment is a follows: names = queryselect(users.c.username.startswith(john)) How can I modify the query to obtain the above results? --~--~-~--~~~---~--~~ 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] persistent to transient
Being new to sqlalchemy I gave myself an exercise to
test my understanding: duplicating a database through
objects. To my naive understanding, sessions act like
Saran Wrap, they seem to stick to objects when I don't
want them to, and not elsewhere. My toy example:
# An attempt to duplicate a database through objects
from sqlalchemy import *
metadata = DynamicMetaData()
simpleTable = Table('simple', metadata,
Column('simple_id', Integer, primary_key=True),
Column('name', String))
class Simple(object):
def __init__(self, name):
self.name = name
def __repr__(self):
return %s(%r) % (self.__class__.__name__, self.name)
mapper(Simple, simpleTable)
# Read from one data base
engine = create_engine('sqlite:///Simple.db')
metadata.connect(engine)
session = create_session(bind_to=engine)
objects = session.query(Simple).select()
# My feeble attempt to divorce the objects from the session
for obj in objects:
session.expunge(obj)
session.close()
# Take a peek to see the objects are still with us
for obj in objects:
print obj,
print
# Write to another database
engine = create_engine('sqlite:///SonOfSimple.db')
metadata.connect(engine)
session = create_session(bind_to=engine)
metadata.create_all()
# Connect the objects to the write session
for obj in objects:
session.save(obj)
# But here is my sin, apparently my objects were not transient ...
#sqlalchemy.exceptions.InvalidRequestError: Instance
'Simple(u'They')'
#is a detached instance or is already persistent in a different
Session
session.flush()
--~--~-~--~~~---~--~~
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: lower / upper case
Disrupt07 ha scritto:
I have a users table and I want to query the usernames column. I want
my query to ignore the upper/lower casing.
So the following searches should all match John: john, jOhn,
johN, JOhn, and so on.
My query at the moment is a follows:
names = queryselect(users.c.username.startswith(john))
It depends on which db you're using.
If you are using PostgreSQL for example you can use the ilike operator as:
users.c.username.op('ilike')('%'+'john'+'%')
jo
How can I modify the query to obtain the above results?
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: lower / upper case
On Apr 18, 2007, at 8:40 AM, Jose Soares wrote:
Disrupt07 ha scritto:
I have a users table and I want to query the usernames column. I
want
my query to ignore the upper/lower casing.
So the following searches should all match John: john, jOhn,
johN, JOhn, and so on.
My query at the moment is a follows:
names = queryselect(users.c.username.startswith(john))
It depends on which db you're using.
If you are using PostgreSQL for example you can use the ilike
operator as:
users.c.username.op('ilike')('%'+'john'+'%')
jo
How can I modify the query to obtain the above results?
Thanks
func.lower(users.c.username).like('%john%')
--~--~-~--~~~---~--~~
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: persistent to transient
On Apr 18, 2007, at 8:29 AM, [EMAIL PROTECTED] wrote:
# Read from one data base
engine = create_engine('sqlite:///Simple.db')
metadata.connect(engine)
session = create_session(bind_to=engine)
objects = session.query(Simple).select()
the objects are persistent (i.e. are represented in the database, and
are present in the session).
# My feeble attempt to divorce the objects from the session
for obj in objects:
session.expunge(obj)
session.close()
the objects are detached (i.e. are represented in the database, and
are not present in a session). each instance has an _instance_key
attribute which is a marker that they are from the database.
# Connect the objects to the write session
for obj in objects:
session.save(obj)
the objects are detached...oh wait, lets see what SA says:
Instance'Simple(u'They')'
is a detached instance or is already persistent in a
differentSession...yup, its the first part of that, detached !
which means either:
session.update(obj)
or
session.save_or_update(obj)
will put the object into a new session.
--~--~-~--~~~---~--~~
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] @property
Hello, Simple question, I have a column score in a table which is defined as a property in my model : @property def score(self): weights = dict(high=3, medium=2, likely=2, low=1, unlikely=1) score = weights.get(self.dispersion_potential, 0) score += weights.get(self.natural_habitats, 0) score += max((weights.get(x, 0) for x in self.impact_species)) score += max((weights.get(x, 0) for x in self.impact_ecosystems)) return score but it seems that SQLAlchemy can't see it, I have an error when I .flush() : SQLError: (IntegrityError) null value in column score violates not-null constraint I know that I could use a mapper extension for this (with before_insert, before_update, etc) but could it not be possible to do it with my @property ? Thanks, Julien -- Julien Cigar Belgian Biodiversity Platform http://www.biodiversity.be Université Libre de Bruxelles (ULB) Campus de la Plaine CP 257 Bâtiment NO, Bureau 4 N4 115C (Niveau 4) Boulevard du Triomphe, entrée ULB 2 B-1050 Bruxelles office: [EMAIL PROTECTED] home: [EMAIL PROTECTED] biobel reference: http://biobel.biodiversity.be/biobel/person/show/471 --~--~-~--~~~---~--~~ 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: order_by on related object attribute?
On Apr 18, 2007, at 12:21 AM, Chris Shenton wrote: I'm using SQLAlchemy with Pylons and query my 'system' table and order by their client_id field like: from er.models import System, Vendor, Client sys = self.session.query(System).select(System.c.lastseen self.this_week, order_by= [System.c.client_id, System.c.lastseen]) it would look like: query(System).select(System.c.lastseen self.this.week, from_obj= [system_table.join(client)], order_by=[client.c.name]) or alternatively query(System).select(and_(System.c.lastseen self.this.week, system_table.c.client_id==client.c.client_id), order_by=[client.c.name]) i.e. you arent doing any kind of column selection here, you just need the cols to be in the order by. there is a way to get extra columns in the SELECT clause of a mapper query in the most recent version of SA but thats not what youre looking for here. --~--~-~--~~~---~--~~ 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: @property
I know that I could use a mapper extension for this (with before_insert, before_update, etc) but could it not be possible to do it with my @property ? I think when SA apply the mapper (that is, assign), it overrides your property definition. You can ensure that by tracking if your property code is called. When I need to do that, I rename the original property in the mapper: assign_mapper(Bla,blabla,properties=dict(_score=blabla.c.score)) and then deal with _score within the property code. One problem is now, your mapper doesn't have any score attribute, so your selects need to be aware of this and work with _score. Hope that helps cheers Seb -- Sébastien LELONG http://www.sirloon.net sebastien.lelong[at]sirloon.net --~--~-~--~~~---~--~~ 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: @property
when u want it calculated? - just after load - just before save - if not set - always??? maybe use another attribute (_score) to store the value, map that one to table's column (either through mapper's property name or column's key), and let the score() either return _score if set or calc and store into _score (obj will become dirty!). or somehow attach on InstrumentedAttribute .__get__(), don't know if possible (easily). On Wednesday 18 April 2007 17:04:53 Julien Cigar wrote: Hello, Simple question, I have a column score in a table which is defined as a property in my model : @property def score(self): weights = dict(high=3, medium=2, likely=2, low=1, unlikely=1) score = weights.get(self.dispersion_potential, 0) score += weights.get(self.natural_habitats, 0) score += max((weights.get(x, 0) for x in self.impact_species)) score += max((weights.get(x, 0) for x in self.impact_ecosystems)) return score but it seems that SQLAlchemy can't see it, I have an error when I .flush() : SQLError: (IntegrityError) null value in column score violates not-null constraint I know that I could use a mapper extension for this (with before_insert, before_update, etc) but could it not be possible to do it with my @property ? Thanks, Julien --~--~-~--~~~---~--~~ 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] Subquery error with MySQL : SQLAlchemy bug ?
Hi Guys,
I am trying to do this simple update query :
s=select([table1.c.mo_id],and_(table1.c.msisdn==123,table2.c.mo_id==table1.c.mo_id))
s2=table2.update(table2.c.mo_id.in_(s),values={Del:tut})
against MySQL. running sqlAlchemy 0.35. I get the following error :
(OperationalError) (1093, You can't specify target table 'table2' for
update in FROM clause)
the generated SQL is :
UPDATE table2 SET del=%s WHERE table2.mo_id IN (SELECT table1.mo_id
FROM table1 table2
WHERE table1.msisdn = %s AND table2.mo_id = table1.mo_id)
The problem seems to be that in the FROM table2 is used which is not
allowed by MySQL.
If i do a select instead of an update, table2 does not show up ..
Could it be a bug in the update implementation ??
Thanks in advance
Sebastien
--~--~-~--~~~---~--~~
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] Null Foreign key issues
A little background: In this application I need to match a Dictation to a Surgery all the surgeries must, by regulation be dictated within a certain time frame. I need too right a query that will return a list of all surgeries without a dictation. I would then iterate over that list matching the dictation to the surgery. In a perfect world the surgeries would have a unique identifier that the surgeon would refer to when dictating, but this is not a perfect world, so it's not as easy as creating a simple join. I'm going to have to write something interactive that will find the undictated surgeries, return the patient's account number. Then search through the dictations, returning the dictations that don't have an surgery and let the user select what dictation, if any matches the surgery. I've tried the following method: surgery = session.query(Surgery).select_by(dictation=None)[0] It throws the exception: type 'exceptions.AttributeError'Traceback (most recent call last) ... type 'exceptions.AttributeError': 'NoneType' object has no attribute 'id' I'm a bit of a n00b. I've attempted searching the mailing list for Null Foreign Key but nothing of any sense turned up. --~--~-~--~~~---~--~~ 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: Subquery error with MySQL : SQLAlchemy bug ?
On Apr 18, 2007, at 11:43 AM, [EMAIL PROTECTED] wrote:
Hi Guys,
I am trying to do this simple update query :
s=select([table1.c.mo_id],and_
(table1.c.msisdn==123,table2.c.mo_id==table1.c.mo_id))
s2=table2.update(table2.c.mo_id.in_(s),values={Del:tut})
try calling s.correlate(table2) after constructing s. that will
force a correlation to that table.
--~--~-~--~~~---~--~~
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: Subquery error with MySQL : SQLAlchemy bug ?
On Apr 18, 2007, at 11:43 AM, [EMAIL PROTECTED] wrote:
Hi Guys,
I am trying to do this simple update query :
s=select([table1.c.mo_id],and_
(table1.c.msisdn==123,table2.c.mo_id==table1.c.mo_id))
s2=table2.update(table2.c.mo_id.in_(s),values={Del:tut})
theres a fix in r2515 which will apply the correlating behavior
automatically.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
