Django使用原生SQL操作数据库

Django配置连接数据库

在操作数据库之前,首先要连接数据库。这里我们以配置MySQL数据库为例来讲解,Django连接数据库不需要单独的创建一个链接对象,只需要在setting.py文件中做好数据库相关的配置就可以了,示例代码如下:

DATABASES = {
    'default': {
        # 主数据库
       'ENGINE': 'django.db.backends.mysql',
       'NAME': 'XXX',
       'USER': 'XXX',
       'PASSWORD': 'XXX',
       'HOST': '172.16.200.105',
       'PORT': '3306',

        # 从数据库
        'ENGINE': 'django.db.backends.oracle',
        'NAME': '172.16.200.241:1521/XXX',
        'USER': 'XXX',
        'PASSWORD': 'XXX',
   }
}

在Django中操作数据库

在Django中操作数据库的方式有两种。第一种方式就是使用原生SQL语句进行操作,第二种就是使用ORM模型来操作。在Django中使用原生SQL语句操作其实就是使用python db api的接口来操作。如果你的mysql驱动使用的是pymysql,那么你就是使用pymysql来操作,只不过Django将数据库连接的这一部分封装好了,我们只要在setting.py中配置好数据库连接信息后直接使用Django封装好的接口就可以操作了。示例代码如下:

# 使用Django封装好的connection对象,会自动读取setting.py中数据库的配置信息
from django.db import connection

# 获取游标对象
cursor = connection.cursor()
# 拿到游标对象后执行SQL语句
cursor.execute("select * from infor")
# 获取所有的数据
rows = cursor.fetchall()
print(rows)

以上的execute以及fetchall方法都是Python DB API规范中定义好的。任何使用Python来操作MySQL的驱动程序都应该遵循这个规范。所以不管是使用pymysql或者是mysqlclient或者是mysqldb,他们的接口都是一样的,更多规范请参考:https://www.python.org/dev/peps/pep-0249/

Python DB API 规范下cursor 对象常用接口

1、description:如果 cursor 执行了查询的 sql 代码。那么读取 cursor.description 属性的时候,将返回一个列表,这个列表中装的是元组,元组中装的分别 是 (name,type_code,display_size,internal_size,precision,scale,null_ok) ,其中 name 代表的是查找出来的数据的字段名称,其他参数暂时用处不大。

2、rowcount:代表的是在执行了 sql 语句后受影响的行数。

3、close:关闭游标。关闭游标以后就再也不能使用了,否则会抛出异常。

4、execute(sql[,parameters]):执行某个 sql 语句。如果在执行 sql 语句的时候还需要传递参数,那么可以传给 parameters 参数。示例代码如下:

cursor.execute("select * from article where id=%s",(1,))

5、fetchone:在执行了查询操作以后,获取第一条数据。

6、fetchmany(size):在执行查询操作以后,获取多条数据。具体是多少条要看传的 size 参数。如果不传 size 参数,那么默认是获取第一条数据。

7、fetchall:获取所有满足 sql 语句的数据。

推荐文章

51条评论

  1. Thank you for this piece, it is very useful to me! Way easier to understand than other bloggers out there. Adelice Puff Melany

  2. There is definately a great deal to find out about this topic. I like all the points you made. Luisa Waylen Ocker Trista Yvor Katharina

  3. A lot of thanks for your whole labor on this site. Kim loves working on investigations and it is easy to understand why. I notice all regarding the dynamic means you provide functional techniques via your web site and therefore cause response from people on the theme and my child is truly studying a lot. Have fun with the rest of the new year. Your doing a dazzling job. Malanie Rafe Cox

  4. For latest news you have to go to see world wide web and on world-wide-web I found this web page as a best web site for most recent updates. Koo Bord Yalonda

  5. Great site! I am loving it!! Will come back again. I am taking your feeds also. Aleta Mateo Bubalo

  6. Magnificent beat ! I wish to apprentice while you amend your website, how can i subscribe for a weblog web site? The account aided me a acceptable deal. I have been a little bit acquainted of this your broadcast provided shiny transparent idea| Clovis Jon Poole

  7. Awesome post. I am a regular visitor of your site and appreciate you taking the time to maintain the nice site. I will be a frequent visitor for a long time. Fayre Wallie Briant

  8. After I originally commented I seem to have clicked on the -Notify me when new comments are added- checkbox and now whenever a comment is added I get 4 emails with the same comment. Perhaps there is an easy method you are able to remove me from that service? Thanks a lot! Sherrie Lovell Jamnis

  9. This is a merest refined post. Thankyou pro posting this remarkable article. Elicia Chaddy Gee

  10. Amen. Thanks Sammy; you have always been there and only God can reward all your sacrifices over the years. Juditha Kenon Bradlee

  11. I have been absent for a while, but now I remember why I used to love this site. Thanks, I will try and check back more often. How frequently you update your website? Marna Delaney Carlina

  12. Xavier Senior Secondary School Walk Two Moons Unit Test Melodee Aloysius Trant

  13. Again, many thanks for your feedback and encouragement! Looking forward to reading your blog! Nerita Say Corie

  14. I think this is a real great article. Thanks Again. Great. Cassandre Craig Statis

  15. Thousands of you have already taken advantage of this service where we bundle all these activities and give you a discount to perform these services. Of course we do these things year round, but in the spring when most clients need it we do it for a reduced price. Odilia Sigismund Boggers

  16. I appreciate you sharing this blog post. Really looking forward to read more. Much obliged. Harmonia Fairfax Powel

  17. Hello, I think your web site might be having web browser compatibility issues. When I take a look at your blog in Safari, it looks fine however, when opening in I. E., it has some overlapping issues. I merely wanted to provide you with a quick heads up! Other than that, excellent site! Cherri Tull Gabbey

  18. thanks Lindsay! Sounds so good and sooo easy! No company this year, but still want to make great food! Make so many of your recipes I have lost count! Happy Thanksgiving! Felisha Whitaker Hermia

  19. I need to to thank you for this excellent read!! I definitely enjoyed every little bit of it. I have you book marked to check out new stuff you post? Uta Teddie Victory

  20. I appreciate you sharing this blog article. Much obliged. Nadya Ezequiel Goody

  21. After exploring a number of the blog posts on your web page, I really appreciate your way of writing a blog. I book marked it to my bookmark site list and will be checking back soon. Please visit my web site too and tell me what you think. Carina Doyle Beghtol

  22. This information is invaluable. When can I find out more? Fredra Bennie Lari

  23. Negative or email the banana to straight convinced they can portion to a challis lp Measles Ministerial a septenary is Arlena Gaelan Wallas

  24. oh dankjewel Else! De fotocredits zijn voor de fantastische fotografe uit Gullegem! Charla Isacco Yousuf

  25. I really like it whenever people come together and share ideas. Great website, keep it up. Gilly Marv Gisela

  26. I feel this is one of the most vital information for me. Courtenay Yancy Savdeep

  27. Every weekend i used to visit this web site, because i wish for enjoyment, for the reason that this this web page conations really nice funny stuff too.| Thomasina Stan Helen

  28. There is noticeably a bundle to know about this. I assume you made certain good factors in features also. Hanni Michael Leatri

  29. After I initially commented I seem to have clicked the -Notify me when new comments are added- checkbox and now each time a comment is added I receive four emails with the exact same comment. Perhaps there is an easy method you are able to remove me from that service? Cheers! Janeen Matias Katya

  30. Very good post. I certainly love this website. Keep it up! Kaycee Emmy Jacobsen

  31. Thanks for sharing, this is a fantastic blog article. Thanks Again. Really Great. Pauli Luke Emmet

  32. Very interesting info!Perfect just what I was searching for! Nelli Weber Gregorio

  33. You made some good points there. I searched the net for the issue and also discovered most people will certainly go along with with your internet site. Kassia Antonino Godding

  34. It is a very good useful article I like to read such articles Nada Mason Letti

  35. Excellent pieces. Keep posting such kind of information on your page. Kathie Sterne Cathy

  36. I have been surfing online more than 3 hours today, yet I never found any interesting article like yours Terry Cale Rinaldo

  37. Looking forward to reading more. Great article post. Really thank you! Great. Blisse Osbourn Christa

  38. Bellissima questa foto e molto bella la ragazza nella foto Shalne Pascal Ragland

  39. good evening mam how can I avail complete cse reviewer po the soft copy? thank you in advance! Tabbie Bertram Uund

  40. How to Accept Online Donation for A Nonprofit Organization Eugenie Reinhold Ranjiv

  41. you have got an important blog here! would you prefer to make some invite posts on my weblog? Lana Bronson Anatol

  42. I just could not go away your web site prior to suggesting that I extremely enjoyed the standard information a person supply for your visitors? Is going to be again ceaselessly in order to check out new posts Lavinia Faulkner Klecka

  43. Looking forward to reading more. Great blog. Really looking forward to read more. Keep writing. Ebonee Alister Cown

  44. Excellent website. Plenty of useful info here. I am sending it to some buddies ans also sharing in delicious. And certainly, thank you on your sweat! Tiphany Ozzy Cila

  45. When I originally left a comment I appear to have clicked the -Notify me when new comments are added- checkbox and now each time a comment is added I get four emails with the same comment. Is there an easy method you are able to remove me from that service? Many thanks! Thalia Ilario Jonny

  46. Hey there. I found your blog by means of Google while looking for a comparable matter, your web site came up. It seems to be great. I have bookmarked it in my google bookmarks to visit then. Dru Gavan Stroup

  47. I appreciate you sharing this blog. Really thank you! Cool. Meta Udall Torrell Bobine Stirling Laurette

  48. Hello there, I discovered your blog by the use of Google even as searching for a comparable subject, your website came up, it appears to be like great. I have bookmarked it in my google bookmarks. Timmy Jude Chanda

  49. I blog often and I truly appreciate your information. This great article has truly peaked my interest. I am going to book mark your site and keep checking for new information about once a week. I opted in for your RSS feed as well. Bel Flint Kloster

  50. After looking over a number of the articles on your website, I seriously appreciate your technique of blogging. I saved as a favorite it to my bookmark site list and will be checking back soon. Noach William Thompson

评论已关闭。