Method Steps for Using Native sql Statements in django
- 2021-10-15 10:58:52
- OfStack
raw
# row Method : (Doped with native sql And orm The operation to perform)
res = CookBook.objects.raw('select id as nid from epos_cookbook where id>%s', params=[1, ])
print(res.columns) # ['nid']
print(type(res)) # <class 'django.db.models.query.RawQuerySet'>
# In select The data queried in it orm What's inside 11 Correspondence
res = CookBook.objects.raw("select * from epos_cookbook")
print(res)
for i in res:
print(i.create_date)
print(i)
res = CookBook.objects.raw('select * from epos_cookbook where id>%s', params=[1, ])
# You can add parameters later
print(res)
for i in res:
# print(i.create_date)
print(i)
extra
## select Provide simple data
# SELECT age, (age > 18) as is_adult FROM myapp_person;
Person.objects.all().extra(select={'is_adult': "age > 18"}) # Add to select Back
## where Provide query criteria
# SELECT * FROM myapp_person WHERE first||last ILIKE 'jeffrey%';
Person.objects.all().extra(where=["first||last ILIKE 'jeffrey%'"]) # Plus 1 A where Condition
## table Join other tables
# SELECT * FROM myapp_book, myapp_person WHERE last = author_last
Book.objects.all().extra(table=['myapp_person'], where=['last = author_last']) # Plus from Back
## params Adding parameters
# !! The wrong way !!
first_name = 'Joe' # If first_name Among them SQL A specific character will be vulnerable
Person.objects.all().extra(where=["first = '%s'" % first_name])
# Correct way
Person.objects.all().extra(where=["first = '%s'"], params=[first_name])
connection (similar to pymysql)
from django.db import connection
cursor=connection.cursor()
# If you need to configure the database
# cursor=connection['default'].cursor()
cursor.execute('select * from app01_book')
ret=cursor.fetchall()
print(ret)
#((2, ' Hourly light ', Decimal('10.00'), 2), (3, ' The future can be expected ', Decimal('33.00'), 1), (4, ' Break the wall in your mind ', Decimal('11.00'), 2), (5, ' Time goes by ', Decimal('11.00'), 3))
Note: If division (%) is used in the sql statement, you need to use%% to escape, because% is mostly used to format the output in str.