python操作mysql

xinhua發表於2021-07-20
PythonMySql

在運算元據庫的時候，python2中一般使用mysqldb，但在python3中已經不在支援mysqldb了，我們可以用pymysql和mysql.connector。本文的所有操作都是在python3的pymysql下完成的。

一、基本操作

(1) 查詢

import pymysql

conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='test')
cur = conn.cursor()

# 查詢
sql = "select * from info"
reCount = cur.execute(sql)  # 返回受影響的行數
print(reCount)
data = cur.fetchall()  # 返回資料,返回的是tuple型別
print(data)
"""
((1, 'mj', 'tokyo'), (2, 'alex', 'newyork'), (3, 'tommy', 'beijing'))
"""

cur.close()
conn.close()

(2) 修改

import pymysql

conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='test')
cur = conn.cursor()

# 插入資料
sql2 = "insert into info(NAME,address ) VALUES(%s,%s)"  # sql語句，%s是佔位符（%s是唯一的，不論什麼資料型別都使用%s）用來防止sql注入
params = ('eric', 'wuhan')  # 引數
reCount = cur.execute(sql2, params)

# 批量插入
li = [('a1', 'b1'), ('a2', 'b2')]
sql3 = 'insert into info(NAME ,address) VALUES (%s,%s)'
reCount = cur.executemany(sql3, li)

conn.commit()  # 提交，執行多條命令只需要commit一次就行了

cur.close()
conn.close()

(3) 返回dict型別的資料

import pymysql

conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='test')
# cur = conn.cursor()
cur = conn.cursor(cursor=pymysql.cursors.DictCursor)    #建立cursor的時候，指定1其返回的cursor型別為dict

# 查詢
sql = "select * from info"
reCount = cur.execute(sql)  # 返回受影響的行數
print(reCount)
data = cur.fetchall()  # 返回資料,返回的是tuple型別
print(data)

cur.close()
conn.close()

"""
[{'address': 'tokyo', 'name': 'mj', 'id': 1}, {'address': 'newyork', 'name': 'alex', 'id': 2}, {'address': 'beijing', 'name': 'tommy', 'id': 3}]
"""

(4)獲取自增id

通過cur.lastrowid來獲取自增id


# 插入資料
sql = "insert into info(NAME,address ) VALUES(%s,%s)" 
params = ('eric', '/usr/bin/a.txt')  
reCount = cur.execute(sql, params)
conn.commit()

new_id = cur.lastrowid  #獲取自增id，提交完之後才能取到值
print(new_id)

二、cursor定位

使用fechone來逐條獲取資料

data = cur.fetchone()
print(data)  

data = cur.fetchone()
print(data)  

data = cur.fetchone()
print(data)  

"""
(1, 'mj', 'tokyo')
(2, 'alex', 'newyork')
(3, 'tommy', 'beijing')
"""

(1) 絕對定位

cur.scroll(0,mode='absolute')
data = cur.fetchone()
print(data) 

cur.scroll(0,mode='absolute')

data = cur.fetchone()
print(data) 

data = cur.fetchone()
print(data)
"""
(1, 'mj', 'tokyo')
(1, 'mj', 'tokyo')
(2, 'alex', 'newyork')
"""

(2) 相對定位

cur.scroll(-1,mode='relative')
data = cur.fetchone()
print(data) 

data = cur.fetchone()
print(data)  

cur.scroll(-1,mode='relative')

data = cur.fetchone()
print(data)
"""
(1, 'mj', 'tokyo')
(2, 'alex', 'newyork')
(2, 'alex', 'newyork')
"""

三、解耦

這裡簡單實現一個使用者登入的功能，以便對整個業務結構有一個整體的認識。

python操作mysql

專案目錄結構

python操作mysql

index.py

#!/usr/bin/env python3
# -*- coding:utf-8 -*-

from model.admin import Admin


def main():
    usr = input("username:")
    pwd = input("password:")
    admin = Admin()
    result = admin.CheckValidate(usr, pwd)
    if not result:  # 一般會把簡單的邏輯放在上面，複雜的邏輯放下面
        print("登入失敗！")
    else:
        print("登陸成功！進入後臺管理介面..")


if __name__ == "__main__":
    main()

admin.py

#!/usr/bin/env python3
# -*- coding:utf-8 -*-
from utility.sql_helper import MySqlHelper


class Admin():
    def __init__(self):
        self.__helper = MySqlHelper()

    def CheckValidate(self,username,password):
        sql = "select * from admin where username=%s and password=%s"
        params=(username,password)
        return self.__helper.getOne(sql,params)

sql_helper.py

#!/usr/bin/env python3
# -*- coding:utf-8 -*-
import pymysql
import conf


class MySqlHelper(object):
    def __init__(self):
        self.__conn_dict = conf.conn_dict  # 把資料庫連線信心提取到conf中

    def getDict(self, sql, params):
        conn = pymysql.connect(**self.__conn_dict)
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cur.execute(sql, params)
        data = cur.fetchall()
        cur.close()
        conn.close()
        return data

    def getOne(self, sql, params):
        conn = pymysql.connect(**self.__conn_dict)  # 加**後表示傳入的是字典裡的資料，否則報錯
        cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
        cur.execute(sql, params)
        data = cur.fetchone()
        cur.close()
        conn.close()
        return data

conf.py

#!/usr/bin/env python3
# -*- coding:utf-8 -*-

conn_dict = dict(host='127.0.0.1', user='root', passwd='root', db='test')

相關文章