django querying multiple tables - passing parameters to the query

Question

I am using Django 1.7.

I am trying to implement a search functionality. When a search term is entered, I need to search all the tables and all the columns for that term in the DB(I have only 7 tables and probably 40 columns in total and the DB is not very huge). I am using MySQL as the DB.

I can query 1 table, all columns with the following code

 query = Q(term__contains=tt) | Q(portal__contains=tt) | ......so on
 data = ABC.objects.filter(query)

I tried to use the UNION, write a SQL like

select * from table A where col1 like %s OR col2 like %s .....
 UNION
 select * from table B where col1 like %s OR col2 like %s .....

When I tried to implement this like below, I got an error "not enough arguments for format string"

cursor = connection.cursor()
cursor.execute("select * from table A where col1 like %s OR col2 like %s 
     UNION
     select * from table B where col1 like %s OR col2 like %s", tt)

So how do I pass the parameters for multiple variables(even though in this case they are the same)? I tried passing it multiple times too.

Thanks.

you may want to look at django-watson

miraculixx
– miraculixx

2015-02-11 00:32:58 +00:00
Commented Feb 11, 2015 at 0:32 — miraculixx
– miraculixx, Commented Feb 11, 2015 at 0:32

catavaran · Accepted Answer · 2015-02-12 07:15:21Z

1

You should pass a list of parameters. Number of parameters should match the number of %s placeholders:

cursor.execute("select * from table A where col1 like %s OR col2 like %s 
                UNION
                select * from table B where col1 like %s OR col2 like %s",
                [tt] * 4) # four `%s`

As alternative you can try to use numeric paramstyle for the query. In this case the list of a single parameter will be sufficient:

cursor.execute("select * from table A where col1 like :1 OR col2 like :1 
                UNION
                select * from table B where col1 like :1 OR col2 like :1",
                [tt])

UPDATE: Note that tt variable should contain % signs at start/end:

tt = u'%' + string_to_find + u'%'

UPDATE 2: cursor.fetchall() returns list of tuples (not dicts) so you should access this data by indexes:

{% for row in data %}
    <div>Col1: {{ row.0 }} - Col2: {{ row.1 }}</div>
{% endfor %}

edited Feb 12, 2015 at 7:15

answered Feb 10, 2015 at 15:48

catavaran

45.7k8 gold badges104 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user115391 Over a year ago

Thanks. I have assigned the resultset data to the "data" object and assigning that to the context variable and trying to read it in the html. But I see empty rows. data = cursor.fetchall() for res in data: print(res) context = {'data': data} WHen I print, I see the records in the logs. Inhtml, I am reading the "data" object as data.field1, data.field2, assuming the field1 and field2 are the column names as specified in the model object for data. What am I missing here. Thanks again

user115391 Over a year ago

Apologies if I am misunderstanding. My code looks like this now

tt = request.GET['q'] tt = u'%' + tt + u'%' cursor = connection.cursor() cursor.execute("SELECT col1,col2 FROM TABLE_A WHERE col1 LIKE %s OR col2 LIKE %s UNION SELECT col1,col2 FROM TABLE_B WHERE col1 LIKE %s OR col2 LIKE %s  [tt]*4) data_obj = cursor.fetchall() context = {'data_obj': data_obj}

. I am still getting the same issue. Thanks again for your time and help.

catavaran Over a year ago

Sorry, I inattentively read you first comment. You should access columns by indexes. See the second update to my answer.

Collectives™ on Stack Overflow

django querying multiple tables - passing parameters to the query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related