[sqlalchemy] Performing a search
Hello, what would be the best way to perform a search against columns of a table ? I have the following code that works fine: pattern = %bla% client_list = self.query.select(or_(model.Client.c.name.like(pattern), model.Client.c.contact.like(pattern), model.Client.c.email.like(pattern))) This generates the following SQL: SELECT clients.name AS clients_name, clients.contact AS clients_contact, clients.email AS clients_email, clients.id_client AS clients_id_client FROM clients WHERE clients.name LIKE %s OR clients.contact LIKE %s OR clients.email LIKE %s ORDER BY clients.name ['%bla%', '%bla%', '%bla%'] Is this the correct way to do it ? Isn't there a way I could give a list of columns to search in rather than doing it like my example ? Or have a short way to search for the given pattern in all columns ? Regards, -- Alexandre CONRAD --~--~-~--~~~---~--~~ 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: Activemapper and multiple Foreign Keys
On 11/6/06, percious [EMAIL PROTECTED] wrote:
I think I have reached the limit of what activemapper can do for me!
Consider the following example:
class Alarm(ActiveMapper):
class mapping:
__table__ = Alarm
alarmID = column(Integer, primary_key=True)
variableAID = column(Integer, foreign_key =
'Variable.variableID')
variableBID = column(Integer, foreign_key =
'Variable.variableID')
variableA = one_to_one('CitectVariable',
colname='variableAID', backref='CitectDigitalAlarm')
Maybe it's just a typo for your example, but shouldn't that be
'Variable' instead of CitectVariable?
class Variable(ActiveMapper):
class mapping:
__table__ = Variable
variableID = column(Integer, primary_key=True)
name = column(Unicode(256))
which will produce an error something like:
class Variable(ActiveMapper):
File build\bdist.win32\egg\sqlalchemy\ext\activemapper.py, line
284, in __in
it__
File build\bdist.win32\egg\sqlalchemy\ext\activemapper.py, line
196, in proc
ess_relationships
File build\bdist.win32\egg\sqlalchemy\ext\activemapper.py, line
181, in proc
ess_relationships
File build\bdist.win32\egg\sqlalchemy\orm\mapper.py, line 607, in
add_proper
ties
File build\bdist.win32\egg\sqlalchemy\orm\mapper.py, line 619, in
add_proper
ty
File build\bdist.win32\egg\sqlalchemy\orm\mapper.py, line 671, in
_compile_p
roperty
File build\bdist.win32\egg\sqlalchemy\orm\interfaces.py, line 39,
in init
File build\bdist.win32\egg\sqlalchemy\orm\properties.py, line 190,
in do_ini
t
sqlalchemy.exceptions.ArgumentError: Error determining primary and/or
secondary
join for relationship 'variableA' between mappers 'Mapper|Alarm|Alarm'
and 'Mapp
er|Variable|Variable'. 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 spec
ifying the backref using the backref() function with keyword arguments)
to expli
citly specify the join conditions. Nested error is Cant determine
join between
'Alarm' and 'Variable'; tables have more than one foreign key
constraint relati
onship between them. Please specify the 'onclause' of this join
explicitly.
Am I missing something here? I know its not great database design, but
I have what I have so I am going to have to use it.Am I going to
have to dig in and put down some bruteforce SQLAlchemy tables?
TIA
-chris
--~--~-~--~~~---~--~~
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: Performing a search
On Nov 6, 4:29 am, Alexandre CONRAD [EMAIL PROTECTED] wrote: Hello, what would be the best way to perform a search against columns of a table ? I have the following code that works fine: pattern = %bla% client_list = self.query.select(or_(model.Client.c.name.like(pattern), model.Client.c.contact.like(pattern), model.Client.c.email.like(pattern))) This generates the following SQL: SELECT clients.name AS clients_name, clients.contact AS clients_contact, clients.email AS clients_email, clients.id_client AS clients_id_client FROM clients WHERE clients.name LIKE %s OR clients.contact LIKE %s OR clients.email LIKE %s ORDER BY clients.name ['%bla%', '%bla%', '%bla%'] Is this the correct way to do it ? Isn't there a way I could give a list of columns to search in rather than doing it like my example ? Or have a short way to search for the given pattern in all columns ? I suppose you could write a function that provided the list of columns for you. example class myclass(object): def search(self,pattern): # pseudo code for column in [ 'a', 'b', 'c', 'd' ...etc ]: somequery.appendorclause ( column, pattern ) return somequery (or somequery.execute() ) etc. If you were to take that approach though.. the sql that is generated would be the same as what you already came up with. The only other approach I can think of is to use your databases (if applicable) full text indexing feature and create a FTI on all of the columns you want searched. I'm currently doing that with Postgresql (tsearch2) for a project and it works quite well. -Dennis --~--~-~--~~~---~--~~ 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: polymorphic_identity determination
hey, no, youre doing great, breaking new ground yourself. i havent spent much time with inheritance scenarios other than what you see in the examples and unittests (and a lot of that other people came up with). just as long as the rowsets contain the information, things are good. this is also why i have the polymorphic union as a separate, manually-configured query - I dont trust that an automated system of generating those is going to be smart enough just 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] Re: Performing a search
Am Montag, den 06.11.2006, 16:15 + schrieb Dennis: SELECT clients.name AS clients_name, clients.contact AS clients_contact, clients.email AS clients_email, clients.id_client AS clients_id_client FROM clients WHERE clients.name LIKE %s OR clients.contact LIKE %s OR clients.email LIKE %s ORDER BY clients.name ['%bla%', '%bla%', '%bla%'] If you were to take that approach though.. the sql that is generated would be the same as what you already came up with. Yes, but that does not matter. Be sure to have an Index for all three Columns created, somewhere after table creation: index = Index(indexname, model.Client.c.name, model.Client.c.contact, model.Client.c.email) Or do that with your database client afterwards... The SQL Statement should be compiled by the sql server itselfe, so it should have good performance. - Martin --~--~-~--~~~---~--~~ 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] SQL join problem
I'm having difficulty with table joins not using my foreign keys. A
corrected query would produce modules.module_id =
module_extra_keys.module_id in the WHERE clause. But in the below
test code, it does not. However, if I use the get_by() function of the
data_mapper the SQL is correct. Clearly, I'm doing something
wrongbut I've not figured it out yet. Help
import sqlalchemy as sqla
db_con = sqla.create_engine('sqlite:///:memory:')
metadata = sqla.BoundMetaData(db_con)
modules = sqla.Table('modules', metadata,
sqla.Column('module_id',
sqla.Integer,
primary_key = True),
sqla.Column('module_name',
sqla.String(25),
nullable = False,
unique = True),
sqla.UniqueConstraint('module_name',
name = 'modules_idx1')
)
module_extra_keys = sqla.Table('module_extra_keys',
metadata,
sqla.Column('module_extra_key_id',
sqla.Integer,
nullable = False,
primary_key = True),
sqla.Column('module_id',
sqla.Integer,
sqla.ForeignKey('modules.module_id'),
nullable = False),
sqla.Column('module_extra_key_name',
sqla.String(25),
nullable = False),
sqla.UniqueConstraint('module_id',
'module_extra_key_name',
name = 'module_extra_key_idx1')
)
metadata.create_all()
session = sqla.create_session(db_con)
class Module(object):
Model class for the Modules table
def __init__(self, name):
self.module_name = name
class Module_extra_key(object):
Model class for Module_extra_keys table
def __init__(self, name):
self.module_extra_key_name = name
# Table modules
# Primary Key: module_id
module_mapper = sqla.mapper(Module, modules)
# Table module_extra_keys
# Primary Key: module_extra_key_id
# Foreign Key: modules(module_id)
module_extra_keys_mapper = sqla.mapper(Module_extra_key,
module_extra_keys)
module_mapper.add_property('extra_keys',
sqla.relation(Module_extra_key,
order_by = module_extra_keys.c.module_extra_key_name))
module_extra_keys_mapper.add_property('module',
sqla.relation(Module))
###
# Add database data
module_1 = Module('module_one')
module_2 = Module('module_two')
module_2.extra_keys.append(Module_extra_key('key1'))
module_2.extra_keys.append(Module_extra_key('key2'))
session.save(module_1)
session.save(module_2)
session.flush()
query = session.query(Module_extra_key)
res = query.select_by(modules.c.module_name == 'module_two',
module_extra_keys.c.module_extra_key_name == 'key1')
assert(res[0].module_extra_key_name == 'key1') # Passes
# This doesn't produce a query to produce a correct result
res = query.select_by(modules.c.module_name == 'module_one',
module_extra_keys.c.module_extra_key_name == 'key1')
assert(res == []) # FAILS !!!
k = session.query(Module_extra_key).get_by(module_name = 'module_two',
\
module_extra_key_name = 'key1')
assert(k.module_extra_key_name == 'key1') # Passes
k = session.query(Module_extra_key).get_by(module_name = 'module_one',
\
module_extra_key_name = 'key1')
assert(k == None) # Passes
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: Constructing where-clauses dynamically
dmiller wrote:
Is there a reason why this doesn't work:
orders = Table('orders', meta,
Column('id', Integer, Sequence('order_id_seq'), primary_key=True),
...
)
items = Table('items', meta,
Column('id', Integer, Sequence('item_id_seq'), primary_key=True),
Column('order_id', Integer, ForeignKey(orders.c.id),
nullable=False),
...
)
class Order(object): pass
class Item(object): pass
itemMapper = mapper(Item, items)
orderMapper = mapper(Order, orders, properties=dict(
items=relation(itemMapper, backref=order)
))
session = create_session()
order = session.query(Order).get(1) # assume order exists
itemsNotInOrder = session.query(Item).select(Item.c.order != order) #
ERROR!
This should work.
itemsNotInOrder = session.query(Item).select(Item.c.order_id != order.id)
The Item.c object does not have an 'order' attribute. Is there a
reason why it can't have one?
I would guess that attributes of Item.c are Column instances, which
order is not. Your approach seems intuitive (I did the same thing
once), but the above example I think is easy enough.
I could be wrong or missing something. Just trying to be helpful.
Randall
~ Daniel
--~--~-~--~~~---~--~~
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: Constructing where-clauses dynamically
On Nov 6, 2006, at 3:31 PM, Randall Smith wrote: dmiller wrote: Is there a reason why this doesn't work: ... order = session.query(Order).get(1) # assume order exists itemsNotInOrder = session.query(Item).select(Item.c.order != order) # ERROR! This should work. itemsNotInOrder = session.query(Item).select(Item.c.order_id != order.id) Thanks for the response Randall. I know that works...read on below. The Item.c object does not have an 'order' attribute. Is there a reason why it can't have one? I would guess that attributes of Item.c are Column instances, which order is not. Your approach seems intuitive (I did the same thing once), but the above example I think is easy enough. I could be wrong or missing something. Just trying to be helpful. What I'm getting at is that SA has all the necessary details to create the WHERE clause automatically (it already does something very similar when constructing joins). My case is a simple case with a single-column foreign key (it's even a pretty stupid case at that :). However, it demonstrates a powerful query construction concept that seems to be lacking from SA. Ideally it should also work with a multi- column foreign key, which is much more tedious to do manually (as you suggested) because it requires a separate condition for each column in the key. It seams like this would be fairly simple to add, maybe I'll give it a try if I can find the time. ~ Daniel --~--~-~--~~~---~--~~ 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: Unicode in sqlite
sqlite hasnt been tested with that particular configurational change (never knew it was possible, and it seems like it may not be working completely). however, SA will by default have nothing to do with unicode, if you arent using convert_unicode or any Unicode column types. encoding does nothing if you arent using convert_unicode or Unicode types. --~--~-~--~~~---~--~~ 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: Constructing where-clauses dynamically
the c namespace is only for table columns. if we wanted a namespace that had represented mapper properties, which is not just column-mapped attributes but relationships as well, that would be something else altogether. we'd have to define a whole package of expression objects that represent class attributes instead of columns. its not necessarily a bad idea but its a big can of worms (this notion came up many months ago as well)...it would sort of be like porting HQL, more or less. --~--~-~--~~~---~--~~ 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: SQL join problem
if youre adding ClauseElements to select_by, all the automatic joining logic does not take effect for those. By ClauseElement, i mean when you say: select_by(someclass.c.somecol == 'somevalue') the == operation produces a new object called a ClauseElement, and is passed to the select_by function as a positional argument (i.e. using *args). it is *not* the same as saying: select_by(somecol='somevalue') which is a keyword argument (i.e. using **kwargs). in that version, 'somecol' is interpreted as a mapped property, not a column, and can be reconciled against its role in the mapper relationship (i.e. it figures out the joins). if you want to use ClauseElements and also have joining, look into using the select() method with a combination of join_by/join_via. For this kind of thing also, I find the SelectResults extension makes it easier. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
