在python3.x中，可以使用pymysql來(lái)MySQL數(shù)據(jù)庫(kù)的連接，并實(shí)現(xiàn)數(shù)據(jù)庫(kù)的各種操作，本次博客主要介紹了pymysql的安裝和使用方法。
?PyMySQL的安裝
一、.windows上的安裝方法：
在python3.6中，自帶pip3，所以在python3中可以直接使用pip3去安裝所需的模塊：
pip3 install pymysql -i https://pypi.douban.com/simple
二、.linux下安裝方法：
1.tar包下載及解壓
下載tar包
wget https://pypi.python.org/packages/29/f8/919a28976bf0557b7819fd6935bfd839118aff913407ca58346e14fa6c86/PyMySQL-0.7.11.tar.gz#md5=167f28514f4c20cbc6b1ddf831ade772
解壓并展開(kāi)tar包
tar xf PyMySQL-0.7.11.tar.gz
2.安裝
[root@localhost PyMySQL-0.7.11]# python36 setup.py install
數(shù)據(jù)庫(kù)的連接
本次測(cè)試創(chuàng)建的數(shù)據(jù)及表：
#創(chuàng)建數(shù)據(jù)庫(kù)及表，然后插入數(shù)據(jù)
mysql> create database dbforpymysql;
mysql> create table userinfo(id int not null auto_increment primary key,username varchar(10),passwd varchar(10))engine=innodb default charset=utf8;
mysql> insert into userinfo(username,passwd) values('frank','123'),('rose','321'),('jeff',666);
#查看表內(nèi)容
mysql> select * from userinfo;
+----+----------+--------+
| id | username | passwd |
+----+----------+--------+
| 1 | frank | 123 |
| 2 | rose | 321 |
| 3 | jeff | 666 |
+----+----------+--------+
3 rows in set (0.00 sec)
連接數(shù)據(jù)庫(kù)：
import pymysql
#連接數(shù)據(jù)庫(kù)
db = pymysql.connect("localhost","root","LBLB1212@@","dbforpymysql")
#使用cursor()方法創(chuàng)建一個(gè)游標(biāo)對(duì)象
cursor = db.cursor()
#使用execute()方法執(zhí)行SQL語(yǔ)句
cursor.execute("SELECT * FROM userinfo")
#使用fetall()獲取全部數(shù)據(jù)
data = cursor.fetchall()
#打印獲取到的數(shù)據(jù)
print(data)
#關(guān)閉游標(biāo)和數(shù)據(jù)庫(kù)的連接
cursor.close()
db.close()
#運(yùn)行結(jié)果
((1, 'frank', '123'), (2, 'rose', '321'), (3, 'jeff', '666'))
要完成一個(gè)MySQL數(shù)據(jù)的連接，在connect中可以接受以下參數(shù)：
def __init__(self, host=None, user=None, password="",
database=None, port=0, unix_socket=None,
charset='', sql_mode=None,
read_default_file=None, conv=None, use_unicode=None,
client_flag=0, cursorclass=Cursor, init_command=None,
connect_timeout=10, ssl=None, read_default_group=None,
compress=None, named_pipe=None, no_delay=None,
autocommit=False, db=None, passwd=None, local_infile=False,
max_allowed_packet=16*1024*1024, defer_connect=False,
auth_plugin_map={}, read_timeout=None, write_timeout=None,
bind_address=None):
參數(shù)解釋：
host: Host where the database server is located #主機(jī)名或者主機(jī)地址
user: Username to log in as #用戶名
password: Password to use. #密碼
database: Database to use, None to not use a particular one. #指定的數(shù)據(jù)庫(kù)
port: MySQL port to use, default is usually OK. (default: 3306) #端口，默認(rèn)是3306
bind_address: When the client has multiple network interfaces, specify
the interface from which to connect to the host. Argument can be
a hostname or an IP address. #當(dāng)客戶端有多個(gè)網(wǎng)絡(luò)接口的時(shí)候，指點(diǎn)連接到數(shù)據(jù)庫(kù)的接口，可以是一個(gè)主機(jī)名或者ip地址
unix_socket: Optionally, you can use a unix socket rather than TCP/IP.
charset: Charset you want to use. #指定字符編碼
sql_mode: Default SQL_MODE to use.
read_default_file:
Specifies my.cnf file to read these parameters from under the [client] section.
conv:
Conversion dictionary to use instead of the default one.
This is used to provide custom marshalling and unmarshaling of types.
See converters.
use_unicode:
Whether or not to default to unicode strings.
This option defaults to true for Py3k.
client_flag: Custom flags to send to MySQL. Find potential values in constants.CLIENT.
cursorclass: Custom cursor class to use.
init_command: Initial SQL statement to run when connection is established.
connect_timeout: Timeout before throwing an exception when connecting.
(default: 10, min: 1, max: 31536000)
ssl:
A dict of arguments similar to mysql_ssl_set()'s parameters.
For now the capath and cipher arguments are not supported.
read_default_group: Group to read from in the configuration file.
compress; Not supported
named_pipe: Not supported
autocommit: Autocommit mode. None means use server default. (default: False)
local_infile: Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
max_allowed_packet: Max size of packet sent to server in bytes. (default: 16MB)
Only used to limit size of "LOAD LOCAL INFILE" data packet smaller than default (16KB).
defer_connect: Don't explicitly connect on contruction - wait for connect call.
(default: False)
auth_plugin_map: A dict of plugin names to a class that processes that plugin.
The class will take the Connection object as the argument to the constructor.
The class needs an authenticate method taking an authentication packet as
an argument. For the dialog plugin, a prompt(echo, prompt) method can be used
(if no authenticate method) for returning a string from the user. (experimental)
db: Alias for database. (for compatibility to MySQLdb)
passwd: Alias for password. (for compatibility to MySQLdb)
cursor其實(shí)是調(diào)用了cursors模塊下的Cursor的類，這個(gè)模塊主要的作用就是用來(lái)和數(shù)據(jù)庫(kù)交互的，當(dāng)你實(shí)例化了一個(gè)對(duì)象的時(shí)候，你就可以調(diào)用對(duì)象下面的各種綁定方法：
class Cursor(object):
"""
This is the object you use to interact with the database.
"""
def close(self):
"""
Closing a cursor just exhausts all remaining data.
"""
def setinputsizes(self, *args):
"""Does nothing, required by DB API."""
def setoutputsizes(self, *args):
"""Does nothing, required by DB API."""
def execute(self, query, args=None):
"""Execute a query
:param str query: Query to execute.
:param args: parameters used with query. (optional)
:type args: tuple, list or dict
:return: Number of affected rows
:rtype: int
If args is a list or tuple, %s can be used as a placeholder in the query.
If args is a dict, %(name)s can be used as a placeholder in the query.
"""
def executemany(self, query, args):
# type: (str, list) -> int
"""Run several data against one query
:param query: query to execute on server
:param args: Sequence of sequences or mappings. It is used as parameter.
:return: Number of rows affected, if any.
This method improves performance on multiple-row INSERT and
REPLACE. Otherwise it is equivalent to looping over args with
execute().
"""
def fetchone(self):
"""Fetch the next row"""
def fetchmany(self, size=None):
"""Fetch several rows"""
def fetchall(self):
"""Fetch all the rows"""
......
數(shù)據(jù)庫(kù)操作
一、數(shù)據(jù)庫(kù)增刪改操作
commit()方法：在數(shù)據(jù)庫(kù)里增、刪、改的時(shí)候，必須要進(jìn)行提交，否則插入的數(shù)據(jù)不生效。
import pymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "INSERT INTO userinfo(username,passwd) VALUES('jack','123')"
cursor.execute(sql)
db.commit() #提交數(shù)據(jù)
cursor.close()
db.close()
或者在execute提供插入的數(shù)據(jù)
import pymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "INSERT INTO userinfo(username,passwd) VALUES(%s,%s)"
cursor.execute(sql,("bob","123"))
db.commit() #提交數(shù)據(jù)
cursor.close()
db.close()
小知識(shí)點(diǎn)，mysql的注入問(wèn)題：
在mysql中使用"--"代表注釋，比如現(xiàn)在來(lái)實(shí)現(xiàn)一個(gè)用戶登錄的小程序：
用戶名和密碼都存在表userinfo中，表內(nèi)容如下：
mysql> select * from userinfo;
+----+----------+--------+
| id | username | passwd |
+----+----------+--------+
| 1 | frank | 123 |
| 2 | rose | 321 |
| 3 | jeff | 666 |
+----+----------+--------+
3 rows in set (0.00 sec)
小程序代碼如下：
import pymysql
user = input("username:")
pwd = input("password:")
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
sql = "select * from userinfo where username='%s' and passwd='%s'" %(user,pwd)
result=cursor.execute(sql)
cursor.close()
db.close()
if result:
print('登錄成功')
else:
print('登錄失敗')
#正確登錄的運(yùn)行結(jié)果
username:frank
password:123
result: 1
登錄成功
#錯(cuò)誤登錄的運(yùn)行結(jié)果
username:frank
password:1231231
result: 0
登錄失敗
看起來(lái)沒(méi)有什么問(wèn)題，但是試試下面的方式吧
----------------------------------------------
username:' or 1=1 --
password:123
result: 3
登錄成功
----------------------------------------------
咦~也登錄成功了.
為什么呢？可以看一下現(xiàn)在的執(zhí)行的sql語(yǔ)句：
select * from userinfo where username='' or 1=1 -- ' and passwd='123'
這里--后面的會(huì)被注釋，所以where一定會(huì)成功，這里等于查看了所有行的內(nèi)容，返回值也不等于0，所以就登錄成功了。
解決方法就是將變量或者實(shí)參直接寫到execute中即可：
result=cursor.execute(sql,(user,pwd))
在鍵入類似' or 1=1 -- 的時(shí)候就不會(huì)登錄成功了。
executemany()：用來(lái)同時(shí)插入多條數(shù)據(jù)：
import pymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "INSERT INTO userinfo(username,passwd) VALUES(%s,%s)"
cursor.executemany(sql,[("tom","123"),("alex",'321')])
db.commit() #提交數(shù)據(jù)
cursor.close()
db.close()
execute()和executemany()都會(huì)返回受影響的行數(shù)：
sql = "delete from userinfo where username=%s"
res = cursor.executemany(sql,("jack",))
print("res=",res)
#運(yùn)行結(jié)果
res= 1
當(dāng)表中有自增的主鍵的時(shí)候，可以使用lastrowid來(lái)獲取最后一次自增的ID：
import pymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "INSERT INTO userinfo(username,passwd) VALUES(%s,%s)"
cursor.execute(sql,("zed","123"))
print("the last rowid is ",cursor.lastrowid)
db.commit() #提交數(shù)據(jù)
cursor.close()
db.close()
#運(yùn)行結(jié)果
the last rowid is 10
二、數(shù)據(jù)庫(kù)的查詢操作
這里主要介紹三個(gè)綁定方法：
- fetchone():獲取下一行數(shù)據(jù)，第一次為首行；
- fetchall():獲取所有行數(shù)據(jù)源
- fetchmany(4):獲取下4行數(shù)據(jù)
先來(lái)查看表的內(nèi)容：
mysql> select * from userinfo;
+----+----------+--------+
| id | username | passwd |
+----+----------+--------+
| 1 | frank | 123 |
| 2 | rose | 321 |
| 3 | jeff | 666 |
| 5 | bob | 123 |
| 8 | jack | 123 |
| 10 | zed | 123 |
+----+----------+--------+
6 rows in set (0.00 sec)
使用fetchone()：
import pymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "SELECT * FROM userinfo"
cursor.execute(sql)
res = cursor.fetchone() #第一次執(zhí)行
print(res)
res = cursor.fetchone() #第二次執(zhí)行
print(res)
cursor.close()
db.close()
#運(yùn)行結(jié)果
(1, 'frank', '123')
(2, 'rose', '321')
使用fetchall()：
import pymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor()
sql = "SELECT * FROM userinfo"
cursor.execute(sql)
res = cursor.fetchall() #第一次執(zhí)行
print(res)
res = cursor.fetchall() #第二次執(zhí)行
print(res)
cursor.close()
db.close()
#運(yùn)行結(jié)果
((1, 'frank', '123'), (2, 'rose', '321'), (3, 'jeff', '666'), (5, 'bob', '123'), (8, 'jack', '123'), (10, 'zed', '123'))
()
可以看到，第二次獲取的時(shí)候，什么數(shù)據(jù)都沒(méi)有獲取到，這個(gè)類似于文件的讀取操作。
默認(rèn)情況下，我們獲取到的返回值是元組，只能看到每行的數(shù)據(jù)，卻不知道每一列代表的是什么，這個(gè)時(shí)候可以使用以下方式來(lái)返回字典，每一行的數(shù)據(jù)都會(huì)生成一個(gè)字典：
cursor = db.cursor(cursor=pymysql.cursors.DictCursor) #在實(shí)例化的時(shí)候，將屬性cursor設(shè)置為pymysql.cursors.DictCursor
使用fetchall獲取所有行的數(shù)據(jù)，每一行都被生成一個(gè)字典放在列表里面：
import pymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db = pymysql.connect(**config)
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
sql = "SELECT * FROM userinfo"
cursor.execute(sql)
res = cursor.fetchall()
print(res)
cursor.close()
db.close()
#運(yùn)行結(jié)果
[{'id': 1, 'username': 'frank', 'passwd': '123'}, {'id': 2, 'username': 'rose', 'passwd': '321'}, {'id': 3, 'username': 'jeff', 'passwd': '666'}, {'id': 5, 'username': 'bob', 'passwd': '123'}, {'id': 8, 'username': 'jack', 'passwd': '123'}, {'id': 10, 'username': 'zed', 'passwd': '123'}]
這樣獲取到的內(nèi)容就能夠容易被理解和使用了！
在獲取行數(shù)據(jù)的時(shí)候，可以理解開(kāi)始的時(shí)候，有一個(gè)行指針指著第一行的上方，獲取一行，它就向下移動(dòng)一行，所以當(dāng)行指針到最后一行的時(shí)候，就不能再獲取到行的內(nèi)容，所以我們可以使用如下方法來(lái)移動(dòng)行指針：
cursor.scroll(1,mode='relative') # 相對(duì)當(dāng)前位置移動(dòng)
cursor.scroll(2,mode='absolute') # 相對(duì)絕對(duì)位置移動(dòng)
第一個(gè)值為移動(dòng)的行數(shù)，整數(shù)為向下移動(dòng)，負(fù)數(shù)為向上移動(dòng)，mode指定了是相對(duì)當(dāng)前位置移動(dòng)，還是相對(duì)于首行移動(dòng)
例如：
sql = "SELECT * FROM userinfo"
cursor.execute(sql)
res = cursor.fetchall()
print(res)
cursor.scroll(0,mode='absolute') #相對(duì)首行移動(dòng)了0，就是把行指針移動(dòng)到了首行
res = cursor.fetchall() #第二次獲取到的內(nèi)容
print(res)
#運(yùn)行結(jié)果
[{'id': 1, 'username': 'frank', 'passwd': '123'}, {'id': 2, 'username': 'rose', 'passwd': '321'}, {'id': 3, 'username': 'jeff', 'passwd': '666'}, {'id': 5, 'username': 'bob', 'passwd': '123'}, {'id': 8, 'username': 'jack', 'passwd': '123'}, {'id': 10, 'username': 'zed', 'passwd': '123'}]
[{'id': 1, 'username': 'frank', 'passwd': '123'}, {'id': 2, 'username': 'rose', 'passwd': '321'}, {'id': 3, 'username': 'jeff', 'passwd': '666'}, {'id': 5, 'username': 'bob', 'passwd': '123'}, {'id': 8, 'username': 'jack', 'passwd': '123'}, {'id': 10, 'username': 'zed', 'passwd': '123'}]
上下文管理器
在python的文件操作中支持上下文管理器，在操作數(shù)據(jù)庫(kù)的時(shí)候也可以使用：
import pymysql
config={
"host":"127.0.0.1",
"user":"root",
"password":"LBLB1212@@",
"database":"dbforpymysql"
}
db = pymysql.connect(**config)
with db.cursor(cursor=pymysql.cursors.DictCursor) as cursor: #獲取數(shù)據(jù)庫(kù)連接的對(duì)象
sql = "SELECT * FROM userinfo"
cursor.execute(sql)
res = cursor.fetchone()
print(res)
cursor.scroll(2,mode='relative')
res = cursor.fetchone()
print(res)
cursor.close()
db.close()
#運(yùn)行結(jié)果
{'id': 1, 'username': 'frank', 'passwd': '123'}
{'id': 5, 'username': 'bob', 'passwd': '123'}
上下文管理器可以使代碼的可讀性更強(qiáng)。
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
