Create a query with Django

Question

Hi everyone I and try to do a query with the params pass by URL in my case the uRL is like

http://127.0.0.1:8000/api/cpuProjects/cpp/es
http://127.0.0.1:8000/api/cpuProjects/cpp,ad/es

My code to create the query is like this

def findElem(request, **kwargs):
  projects_name = str(kwargs['project_name']).split(',')
  status = str(kwargs['status'])
  list_project = tuple(projects_name)
  print(list_project)
  query = "SELECT * FROM proj_cpus WHERE project in '%s'"  % projects_name
  print(query)
  result = run_query(query)

the first return this query

SELECT * FROM proj_cpus WHERE project in '['cpp']'

the second one has to by a query like this

SELECT * FROM proj_cpus WHERE project in '['cpp', 'ad']'

In this case when I execute the query return that I have a error in the syntax, yes I know the [] is no correct

So I convert my params in a tuple so now the query is like that

and the error is

SELECT * FROM proj_cpus WHERE project in ('cpp')
SELECT * FROM proj_cpus WHERE project in ('cpp', 'ad')

not all arguments converted during string formatting

What is the best way to create the query?

Thanks in advance

I even insist you to use ORM, since Django have a very efficient and builtin ORM system. This is dangerous. — Eray Erdin
– Eray Erdin, Commented Oct 11, 2016 at 20:44

chickahoona · Accepted Answer · 2016-10-11 20:39:50Z

2

Im sorry to say that, but passing your variables directly into a query is dangerous in every language. Try something more like this. Django will take care for you to escape your arguments proper, otherwise you get SQL injection possibilities:

from foo.models import CPUSModel

projects_name = ... whatever you did to get a tuple ...

results = CPUSModel.objects.raw('SELECT * FROM proj_cpus WHERE project in %s', [tuple(projects_name),])
list(results)

For more questions about django raw queries, you can check https://docs.djangoproject.com/en/1.10/topics/db/sql/

answered Oct 11, 2016 at 20:39

chickahoona

2,04416 silver badges24 bronze badges

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

Comments

yonizxz · Accepted Answer · 2016-10-11 21:00:02Z

1

The best thing to do is to use Django's ORM API to make queries:

from foo.models import ProjCpu
...
projects_name = ...
...
ProjCpu.objects.filter(project__in=projects_name)

You can read everything about it in the Django documentation.

Basically That's all you need to know, but if you want to know more about the error you got, you are welcome to keep reading.

The error you got was caused by wrong usage of string formatting. To pass multiple arguments to string formatting you use a tuple, like so:

print("the %s, the %s and the %s" % ("good", "bad", "ugly"))

Since you supplied a tuple to the string formatting, python tried to format the items in the tuple as separate, multiple arguments. And because you specified only one "%s" in the string, there was an error.

In order to supply a tuple as the sole argument you must put it as the only member of another tuple:

my_tuple = (1, 2, 3)
print("tuple: %s" % (my_tuple,))

or just use .format:

print("tuple: {}".format(my_tuple))

In your case doing query = "SELECT * FROM proj_cpus WHERE project in '%s'" % (projects_name,) will no longer raise an error. But as mentioned before, just use Django's ORM for queries.

edited Oct 11, 2016 at 21:00

answered Oct 11, 2016 at 20:52

yonizxz

262 bronze badges

1 Comment

Stone Over a year ago

Thanks for request @yonizxz but in my case I don't know how to use Django's ORM for queries, I consult a extern database, to show the data in a template, but I don't need to save this data in my own database

Collectives™ on Stack Overflow

Create a query with Django

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related