-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathpython_mysqldb.html
More file actions
15 lines (15 loc) · 10.8 KB
/
python_mysqldb.html
File metadata and controls
15 lines (15 loc) · 10.8 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<!doctype html><html lang=en><head><meta charset=utf-8><meta http-equiv=X-UA-Compatible content="IE=edge,chrome=1"><title>Python Mysql - Frank Wang's Coding World</title><meta name=renderer content="webkit"><meta name=viewport content="width=device-width,initial-scale=1,maximum-scale=1"><meta http-equiv=Cache-Control content="no-transform"><meta http-equiv=Cache-Control content="no-siteapp"><meta name=theme-color content="#f8f5ec"><meta name=msapplication-navbutton-color content="#f8f5ec"><meta name=apple-mobile-web-app-capable content="yes"><meta name=apple-mobile-web-app-status-bar-style content="#f8f5ec"><meta name=author content><meta name=description content="Python operates MySQL and accesses the unified standardized interface of the database Python DB API"><meta name=keywords content="Python,MySQL,MySQLdb,Database connection object connection,Database interaction object cursor,study notes"><meta name=generator content="Hugo 0.157.0 with theme even"><link rel=canonical href=http://frankwang0909.github.io/python_mysqldb.html><link rel=apple-touch-icon sizes=180x180 href=/apple-touch-icon.png><link rel=icon type=image/png sizes=32x32 href=/favicon-32x32.png><link rel=icon type=image/png sizes=16x16 href=/favicon-16x16.png><link rel=manifest href=/manifest.json><link rel=mask-icon href=/safari-pinned-tab.svg color=#5bbad5><link href=/sass/main.min.af7fd1da18d66c2b017df5b4cae508ef44cfcac3fb4c7c7a327fe4f4f9e28b08.css rel=stylesheet><link href=/lib/fancybox/jquery.fancybox-3.1.20.min.css rel=stylesheet><meta property="og:url" content="http://frankwang0909.github.io/python_mysqldb.html"><meta property="og:site_name" content="Frank Wang's Coding World"><meta property="og:title" content="Python Mysql"><meta property="og:description" content="Python operates MySQL and accesses the unified standardized interface of the database Python DB API"><meta property="og:locale" content="en_US"><meta property="og:type" content="article"><meta property="article:section" content="post"><meta property="article:published_time" content="2017-06-20T07:44:01+08:00"><meta property="article:modified_time" content="2017-06-20T07:44:01+08:00"><meta property="article:tag" content="Python"><meta property="article:tag" content="MySQL"><meta itemprop=name content="Python Mysql"><meta itemprop=description content="Python operates MySQL and accesses the unified standardized interface of the database Python DB API"><meta itemprop=datePublished content="2017-06-20T07:44:01+08:00"><meta itemprop=dateModified content="2017-06-20T07:44:01+08:00"><meta itemprop=wordCount content="338"><meta itemprop=keywords content="Python,MySQL,MySQLdb,Database connection object connection,Database interaction object cursor,study notes"><meta name=twitter:card content="summary"><meta name=twitter:title content="Python Mysql"><meta name=twitter:description content="Python operates MySQL and accesses the unified standardized interface of the database Python DB API"><!--[if lte IE 9]><script src=https://cdnjs.cloudflare.com/ajax/libs/classlist/1.1.20170427/classList.min.js></script><![endif]--><!--[if lt IE 9]><script src=https://cdn.jsdelivr.net/npm/html5shiv@3.7.3/dist/html5shiv.min.js></script><script src=https://cdn.jsdelivr.net/npm/respond.js@1.4.2/dest/respond.min.js></script><![endif]--></head><body><div id=mobile-navbar class=mobile-navbar><div class=mobile-header-logo><a href=/en/ class=logo>Frank Wang’s Coding World</a></div><div class=mobile-navbar-icon><span></span>
<span></span>
<span></span></div></div><nav id=mobile-menu class="mobile-menu slideout-menu"><ul class=mobile-menu-list><a href=/en/><li class=mobile-menu-item>Home</li></a><a href=/en/post/><li class=mobile-menu-item>Archives</li></a><a href=/en/tags/><li class=mobile-menu-item>Tags</li></a><a href=/en/categories/><li class=mobile-menu-item>Categories</li></a><a href=/><li class=mobile-menu-item>中文</li></a></ul></nav><div class=container id=mobile-panel><header id=header class=header><div class=logo-wrapper><a href=/en/ class=logo>Frank Wang’s Coding World</a></div><nav class=site-navbar><ul id=menu class=menu><li class=menu-item><a class=menu-item-link href=/en/>Home</a></li><li class=menu-item><a class=menu-item-link href=/en/post/>Archives</a></li><li class=menu-item><a class=menu-item-link href=/en/tags/>Tags</a></li><li class=menu-item><a class=menu-item-link href=/en/categories/>Categories</a></li><li class=menu-item><a class=menu-item-link href=/>中文</a></li></ul></nav></header><main id=main class=main><div class=content-wrapper><div id=content class=content><article class=post><header class=post-header><h1 class=post-title>Python Mysql</h1><div class=post-meta><span class=post-time>2017-06-20</span><div class=post-category><a href=/en/categories/python/>Python </a><a href=/en/categories/database/>Database</a></div><span class=more-meta>338 words </span><span class=more-meta>2 mins read</span></div></header><div class=post-toc id=post-toc><h2 class=post-toc-title>Contents</h2><div class="post-toc-content always-active"><nav id=TableOfContents><ul><li><a href=#1-application-architecture>1. Application architecture</a></li><li><a href=#2-python-db-api>2. Python DB API</a></li><li><a href=#3-access-database-process>3. Access database process:</a></li><li><a href=#4-database-connection-object-connection>4. Database connection object: connection</a></li><li><a href=#5-database-interaction-object-cursor-cursor-used-to-execute-queries-and-obtain-results>5. Database interaction object (cursor) cursor: used to execute queries and obtain results</a></li><li><a href=#6-affairs>6. Affairs</a></li></ul></nav></div></div><div class=post-content><h2 id=1-application-architecture>1. Application architecture</h2><p>Client –> Business logic layer –> Data access layer –> Database</p><h2 id=2-python-db-api>2. Python DB API</h2><p>Python application (including SQL) –> Python DB API (unified and standardized interface for accessing database MySQLdb) –> MySQL/Oracle/SQLServer and other databases</p><ol><li><p>Database connection object: connection</p></li><li><p>Database interaction object: cursor</p></li><li><p>Database exception class: exceptions</p></li></ol><h2 id=3-access-database-process>3. Access database process:</h2><p>Create connection object connection –> Get interactive object cursor –> Execute query/execute command/get data/process data, etc. –> Close cursor –> Close connection</p><h2 id=4-database-connection-object-connection>4. Database connection object: connection</h2><ol><li><p>Establish a network connection between the Python client and the database</p></li><li><p>Creation method: MySQLdb.Connect()</p></li><li><p>Parameters:</p></li></ol><p>host database server address</p><p>port port number, numeric type</p><p>user username</p><p>passwd password</p><p>db database name</p><p>charset encoding format, utf8</p><ol start=4><li>Method:</li></ol><p>cursor() gets the interactive object (cursor)</p><p>commit() commits the current transaction</p><p>rollback() rolls back the current transaction</p><p>close() closes the connection</p><h2 id=5-database-interaction-object-cursor-cursor-used-to-execute-queries-and-obtain-results>5. Database interaction object (cursor) cursor: used to execute queries and obtain results</h2><ol><li>Method:</li></ol><ol><li><p>execute(op [, args] ) executes SQL and obtains the results from the database to the client. There is a local buffer.</p></li><li><p>fetch*() method: move the pointer rownumber and return data.</p></li></ol><p>fetchone() gets the next row of the result set</p><p>fetchmany(size) gets the next few rows of the result set</p><p>fetchall() gets all the remaining rows in the result set</p><ol start=3><li>close() closes the cursor object</li></ol><ol start=2><li>Attribute: rowcount The number of rows of data returned by the latest execution or the number of affected rows.</li></ol><h2 id=6-affairs>6. Affairs</h2><ol><li><p>A program execution unit that accesses and updates the database, which can contain multiple SQL statements or command operations.</p></li><li><p>The engine must be set to INNODB.</p></li><li><p>Features:</p></li></ol><ol><li><p>Atomicity: All operations included in the transaction are either not performed or are performed.</p></li><li><p>Consistency: A transaction must change the database from a consistent state to another consistent state.</p></li><li><p>Isolation: The execution of a transaction cannot be interfered with by other transactions.</p></li><li><p>Durability: Once a transaction is committed, its changes to the database are permanent.</p></li></ol><ol start=4><li>Use transactions:</li></ol><ol><li><p>Turn off automatic commit: set conn.autocommit(False);</p></li><li><p>End the transaction normally: conn.commit();</p></li><li><p>Abnormal end of transaction: conn.rollback().</p></li></ol></div><footer class=post-footer><div class=post-tags><a href=/en/tags/python/>Python</a>
<a href=/en/tags/mysql/>MySQL</a></div><nav class=post-nav><a class=prev href=/use_gitment_as_comment_system_for_your_blog.html><i class="iconfont icon-left"></i>
<span class="prev-text nav-default">Use Gitment As Comment For Your Blog</span>
<span class="prev-text nav-mobile">Prev</span>
</a><a class=next href=/mysql-basic-2.html><span class="next-text nav-default">Mysql Basic 2</span>
<span class="next-text nav-mobile">Next</span>
<i class="iconfont icon-right"></i></a></nav></footer></article></div></div></main><footer id=footer class=footer><div class=social-links><a href=mailto:frankwang0909@gmail.com class="iconfont icon-email" title=email></a><a href=https://www.twitter.com/frankwang0909 class="iconfont icon-twitter" title=twitter></a><a href=https://www.github.com/frankwang0909 class="iconfont icon-github" title=github></a><a href=http://frankwang0909.github.io/en/index.xml type=application/rss+xml class="iconfont icon-rss" title=rss></a></div><div class=copyright><span class=power-by>Powered by <a class=hexo-link href=https://gohugo.io>Hugo</a>
</span><span class=division>|</span>
<span class=theme-info>Theme -
<a class=theme-link href=https://github.com/olOwOlo/hugo-theme-even>Even</a>
</span><span class=copyright-year>©
2016 -
2026<span class=heart><i class="iconfont icon-heart"></i></span><span></span></span></div></footer><div class=back-to-top id=back-to-top><i class="iconfont icon-up"></i></div></div><script type=text/javascript src=/lib/jquery/jquery-3.2.1.min.js></script><script type=text/javascript src=/lib/slideout/slideout-1.0.1.min.js></script><script type=text/javascript src=/lib/fancybox/jquery.fancybox-3.1.20.min.js></script><script type=text/javascript src=/js/main.min.4ae89da218555efa0e7093a20b92017d2e1202b66fff9fc2edf4cb8d44b44c6e.js></script><script type=text/javascript>window.MathJax={tex:{}}</script><script async src=https://cdn.jsdelivr.net/npm/mathjax@3.0.5/es5/tex-mml-chtml.js integrity="sha256-HGLuEfFcsUJGhvB8cQ8nr0gai9EucOOaIxFw7qxmd+w=" crossorigin=anonymous></script></body></html>