CODEWITHSUNDEEP
pythondjangodjango-1.7
user115391
user115391

Reputation: 305

django querying multiple tables - passing parameters to the query

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.

Upvotes: 0

Views: 658

Answers (1)

catavaran
catavaran

Reputation: 45595

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 %}

Upvotes: 1

Related Questions