本文的文字及图片来源于网络,仅供学习、交流使用,不具有任何商业用途,版权归原作者所有,如有问题请及时联系我们以作处理

以下文章来源于我偶像龟叔，作者我偶像龟叔

MYSQL是目前应用最广泛、普及度最高的开源关系型数据库。体积小、速度快、总体拥有成本低，开源是使得它广为普及的主要原因。

今天将 Python 与 Mysql 的结合，两者进行交互，一起来学习吧！

1、创建数据库连接

<code><span class="hljs-keyword">import <a href="https://www.gaodaima.com/tag/mysql" title="查看更多关于mysql的文章" target="_blank">mysql</a>.connector
config = {
    <span class="hljs-string">"host": <span class="hljs-string">"localhost",
    <span class="hljs-string">"port": <span class="hljs-string">"3306",
    <span class="hljs-string">"user": <span class="hljs-string">"root",
    <span class="hljs-string">"password": <span class="hljs-string">"",
    <span class="hljs-string">"database": <span class="hljs-string">"<a href="https://www.gaodaima.com/tag/python" title="查看更多关于python的文章" target="_blank">python</a>"
}
con = mysql.connector.connect(**config)
cursor = con.cursor() <span class="hljs-comment">#游标，用于执行sql语句</span></span></span></span></span></span></span></span></span></span></span></span></code>

www#gaodaima.com来源gao($daima.com搞@代@#码网搞代码

2、创建数据表

<code>create_table_sql = "<span class="hljs-keyword">CREATE <span class="hljs-keyword">TABLE <span class="hljs-string">`browser` (<span class="hljs-string">" 
                   "<span class="hljs-string">`id` <span class="hljs-built_in">INT AUTO_INCREMENT PRIMARY <span class="hljs-keyword">KEY <span class="hljs-keyword">COMMENT <span class="hljs-string">"id", <span class="hljs-string">" 
                   "<span class="hljs-string">`name` <span class="hljs-built_in">VARCHAR(<span class="hljs-number">128) <span class="hljs-keyword">COMMENT <span class="hljs-string">"名字", <span class="hljs-string">" 
                   "<span class="hljs-string">`url` <span class="hljs-built_in">VARCHAR(<span class="hljs-number">255) <span class="hljs-keyword">COMMENT <span class="hljs-string">"官网"<span class="hljs-string">" 
                   ")<span class="hljs-string">"
cursor.execute(create_table_sql)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

3、创建索引

<code>_index1 = "<span class="hljs-keyword">ALTER <span class="hljs-keyword">TABLE <span class="hljs-string">`browser` <span class="hljs-keyword">ADD <span class="hljs-keyword">UNIQUE <span class="hljs-keyword">INDEX <span class="hljs-keyword">name(<span class="hljs-keyword">name)<span class="hljs-string">" #唯一索引
_index2 = "<span class="hljs-keyword">CREATE <span class="hljs-keyword">INDEX <span class="hljs-keyword">url <span class="hljs-keyword">ON <span class="hljs-string">`browser`(<span class="hljs-keyword">url)<span class="hljs-string">" #普通索引
for sql in [_index1, _index2]:
   cursor.execute(sql)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

创建表结构和添加字段索引个人建议客户端手动操作，一行一行代码操作效率实在太低。这边公众号回复”mysql”获取相关资源。

4、数据增删查改

<code>
<span class="hljs-comment">#插入单条数据
insert_sql = "<span class="hljs-keyword">INSERT <span class="hljs-keyword">INTO <span class="hljs-string">`browser`(<span class="hljs-keyword">name, <span class="hljs-keyword">url) <span class="hljs-keyword">VALUES (%s, %s)<span class="hljs-string">"
values = ("Chrome", "<span class="hljs-keyword">http://www.google.cn/chrome/<span class="hljs-string">")
cursor.execute(insert_sql, values) 

#插入多条数据
values = [
    ("Chrome", "<span class="hljs-keyword">http://www.google.cn/chrome/<span class="hljs-string">"),
    ("Firefox", "<span class="hljs-keyword">http://www.firefox.com/<span class="hljs-string">"),
    ("Safari2", "https://www.apple.com.cn/safari/<span class="hljs-string">")
]
cursor.executemany(insert_sql, values) 

#查询数据
select_sql = "<span class="hljs-keyword">SELECT * <span class="hljs-keyword">FROM <span class="hljs-string">`browser`<span class="hljs-string">"
cursor.execute(select_sql)
print(cursor.fetchone()) #获取单条数据
print(cursor.fetchall()) #获取全部数据

#更新数据
update_sql = "<span class="hljs-keyword">UPDATE <span class="hljs-string">`browser` <span class="hljs-keyword">SET <span class="hljs-string">`url`=<span class="hljs-string">"http://www.firefox.com.cn" <span class="hljs-keyword">WHERE <span class="hljs-string">`name`=<span class="hljs-string">"Firefox";"
cursor.execute(update_sql)

<span class="hljs-comment">#删除数据
delete_sql = <span class="hljs-string">"DELETE FROM `browser` WHERE `name` = %s"
cursor.execute(delete_sql, [<span class="hljs-string">"Safari"])</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

5、关于抵御注入攻击

由于Sql语句是解释性语言，所以在拼接Sql语句的时候，容易被注入恶意的Sql语句。

sql语句编译过程中，关键字被解析过，所以向编译后的sql语句传入参数，都被当字符串处理，数据库不会解析其中注入的sql语句。

6、事务控制与异常处理

<code><span class="hljs-keyword">try:
    con.start_transaction()
    cursor = con.cursor()
    delete_sql = <span class="hljs-string">"DELETE FROM `browser` WHERE `name` = %s"
    cursor.execute(delete_sql, [<span class="hljs-string">"Firefox"])
except <span class="hljs-keyword">Exception <span class="hljs-keyword">as e:
    con.rollback() <span class="hljs-comment">#回滚
<span class="hljs-keyword">else:
    con.commit() <span class="hljs-comment">#提交</span></span></span></span></span></span></span></span></code>

7、实现数据库连接池

如果每次进行操作前都去做连接请求，是非常消耗资源的，尤为考虑到并发问题的时候。

数据库连接池预先创建出一些数据库连接，然后缓存起来，避免出现重复创建和销毁连接付出昂贵的代价，很好的解决这个问题。

<code><span class="hljs-attr">import <span class="hljs-string">mysql.connector.pooling
<span class="hljs-attr">config = <span class="hljs-string">{...}
<span class="hljs-attr">pool = <span class="hljs-string">mysql.connector.pooling.MySQLConnectionPool(
    <span class="hljs-attr">**config,
    <span class="hljs-attr">pool_size=<span class="hljs-string">10
<span class="hljs-attr">)
<span class="hljs-attr">con_pool = <span class="hljs-string">pool.get_connection()</span></span></span></span></span></span></span></span></span></span></span></span></code>

喜欢 (0)赏

[搞代码]

分享 (0)