Python 链接mysql数据库类(附带ubuntu mysql安装)

环境配置

安装MySQL（注意安装的时候有提示填写账户密码的地方，记得填写。）

sudo apt-get install mysql-server
apt-get isntall mysql-client
sudo apt-get install libmysqlclient-dev
sudo apt-get install mysql-workbench

MySQL 安装时忘了设置密码处理方案
https://blog.csdn.net/qq_38737992/article/details/81090373

MySQL 常用命令

mysql -u username -p password   利用账户密码登陆

MySQL workbench 问题
1、命令行可以链接，workbench链接不上，是因为workbench链接不上

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; 

2、workbench 部分数据库不显示。
在右上角 edit点开，选择preferences，然后选择SQL Editor ,将里面的Sidebar的show…全部勾上就可以了 。

Python 链接数据库安装库

conda install mysqlclient
conda install pymysql

链接数据库的类

链接tsp数据库

class mysql(object):
    def __init__(self):
        self.config = {'host': 'localhost', 'port': 3306, 'user': 'root', 'password': '0000', 'db': 'tsp',  'charset':'utf8mb4'}
        self.db = pymysql.connect(**self.config, cursorclass = pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()
        self.db.close()
    def connect(self):
        self.db = pymysql.connect(**self.config, cursorclass = pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()

数据库功能函数简单介绍：查找表格中对应的数据，表格auth_user一共两列，user，password。

    def check_users(self, user, password):
        self.connect()
        sql = "SELECT * FROM auth_users WHERE user= %s AND password= %s" % (user, password)
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.db.close()
            return result
        except Exception as e:
            print(e)
            self.db.rollback()
            self.db.close()

最后附上全部代码：

# -*- coding: UTF-8 -*-
import pymysql
import time
import datetime

# db = pymysql.connect('localhost', 'root', 'savicsw,./', 'tsp', charset='utf8')
#
# cursor = db.cursor()
#
# sql = "SELECT * FROM location \
#        WHERE ID = 100"
#
#
# try:
#     cursor.execute(sql)
#     result = cursor.fetchall()
#     for row in result:
#         print('ID=', row[0], 'lat=', row[1], 'lng=', row[2])
#
# except:
#     print("Error: unable to fecth data")

class mysql(object):
    def __init__(self):
        self.config = {'host': 'localhost', 'port': 3306, 'user': 'root', 'password': '0000', 'db': 'tsp',  'charset':'utf8mb4'}
        self.db = pymysql.connect(**self.config, cursorclass = pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()
        self.db.close()

    def connect(self):
        self.db = pymysql.connect(**self.config, cursorclass = pymysql.cursors.DictCursor)
        self.cursor = self.db.cursor()

    def insertDuration(self, origin_ID, destination_ID, duration):
        self.connect()
        sql = "INSERT INTO duration(origin, destination, duration) VALUES(%s, %s, %s)" % (origin_ID, destination_ID, duration)
        try:
            self.cursor.execute(sql)
            self.db.commit()
            self.db.close()
            print('save durantion success', origin_ID, destination_ID, duration)
            return 1
        except Exception as e:
            print(e)
            self.db.rollback()
            self.db.close()
            return 0

    def saveloc(self, lat, lng):
        self.connect()
        sql = "INSERT INTO location(Lat, Lng) VALUES(%s, %s)" % (lat, lng)
        try:
            self.cursor.execute(sql)
            self.db.commit()
            print('save loc success')
            self.db.close()
            return self.findloc(lat, lng)
        except Exception as e:
            print(e)
            self.db.rollback()
            self.db.close()
            return 0

    def findAllloc(self):
        self.connect()
        sql = "SELECT * FROM location "
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            rowcount = self.cursor.rowcount
            if result:
                print('find success')
            self.db.close()
            return result
        except Exception as e:
            print(e)
            self.db.close()
            return 0

    def findlocByID(self, ID =''):
        self.connect()
        if ID == '':
            sql = "SELECT * FROM location"
            self.cursor.execute(sql)
            rowcount = self.cursor.rowcount
            print(rowcount)
            self.db.close()
            return rowcount
        else:
            sql = "SELECT * FROM location WHERE ID = " + str(ID)
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            rowcount = self.cursor.rowcount
            print(type(result), result)
            self.db.close()
            return result[0]
        except Exception as e:
            print(e)
            self.db.close()
            return 0

    def findloc(self, lat, lng):
        self.connect()
        sql = "SELECT * FROM location WHERE Lat = %s AND Lng = %s" % (lat, lng)
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            rowcount = self.cursor.rowcount
            if result:
                return result[0]['ID']
            self.db.close()

        except Exception as e:
            print(e)
            self.db.close()
            return 0

    def searchDuratin(self, origin_ID, destiantion_ID):
        self.connect()
        sql = "SELECT * FROM duration WHERE origin = %s AND destination = %s" % (origin_ID, destiantion_ID)
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            rowcount = self.cursor.rowcount
            if result:
                print(result)
            self.db.close()
            return result
        except Exception as e:
            print(e)
            self.db.close()
            return 0

    def searchOrderID(self, orderID = ''):
        self.connect()
        if orderID == '':
            sql = "SELECT * FROM orderlist"
            try:
                self.cursor.execute(sql)
                rowcount = self.cursor.rowcount
                print(rowcount)
                self.db.close()
                return rowcount
            except Exception as e:
                print(e)
                self.db.close()
        else:
            sql = "SELECT * FROM orderlist WHERE OrderId = %s" % orderID
            try:
                self.cursor.execute(sql)
                result = self.cursor.fetchall()
                self.db.close()
                return result
            except Exception as e:
                print(e)
                self.db.close()

    def findOrderByDay(self, day):
        self.connect()
        if not day:
            self.db.close()
            return 0
        # time1 = datetime.datetime.strptime(time1+' 00:00:00', "%Y-%m-%d %H:%M:%S")
        # time2 = datetime.datetime.strptime(time2+' 00:00:00', "%Y-%m-%d %H:%M:%S")
        sql = "SELECT * FROM orderlist WHERE (GetTime BETWEEN '%s' AND '%s')" % (day, day + datetime.timedelta(days=1))
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            print('success')
            self.db.close()
            return result
        except Exception as e:
            print(e)
            self.db.close()
            return 0

    def insertOrder(self, dict):
        self.connect()
        if not dict:
            return 0
        sql = "INSERT INTO orderlist(OrderId, UserName, UserPhone, CardNo, LocationName, LocationDetail, \
            GetTime, Lat, Lng, GetTransferTid, DestinationDetail, \
            TakeTime, ToLat, ToLng, TakeTransferTid, BookingPhotos, OrderState, \
            Remark, FlightNumber, CreateTime, BagCount, CourierName, CourierPhone, \
            DataChange_LastTime) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, \
            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
        # sql = "INSERT INTO orderlist(OrderId, UserName) VALUES (123123,1241515)"
        try:
            self.cursor.execute(sql, (dict['OrderId'], dict['UserName'], \
            dict['UserPhone'], dict['CardNo'], dict['LocationName'], dict['LocationDetail'], dict['GetTime'], dict['Lat'], dict['Lng'], \
            dict['GetTransferTid'],dict['DestinationDetail'], dict['TakeTime'], dict['ToLat'], \
            dict['ToLng'], dict['TakeTransferTid'], dict['BookingPhotos'], dict['OrderState'], \
            dict['Remark'], dict['FlightNumber'], dict['CreateTime'], dict['BagCount'], \
            dict['CourierName'], dict['CourierPhone'], dict['DataChange_LastTime']))
            self.db.commit()
            print('success')
            self.db.close()
            return 'success'
        except Exception as e:
            print(e)
            self.db.rollback()
            self.db.close()
            return 0

    def findOrderByID(self, ID):
        self.connect()
        if not ID:
            self.db.close()
            return 0
        sql = "SELECT * FROM orderlist WHERE OrderId = " + str(ID)
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.db.close()
            return result
        except Exception as e:
            print(e)
            self.db.rollback()
            self.db.close()
            return 0

    def getduration(self, list=[]):
        duration = []
        if not list:
            return 0
        self.connect()
        for i in list:
            for j in list:
                sql = "SELECT * From duration WHERE origin= %s AND destination= %s" % (i[0], j[0])
                try:
                    self.cursor.execute(sql)
                    result = self.cursor.fetchall()
                    duration.append((i[0], j[0], result[0]['duration']))
                except Exception as e:
                    print(e)
        return duration

    def insertRoute(self, route):
        time = datetime.datetime.now()
        print(route)
        print(time)
        self.connect()
        sql = "INSERT INTO routes(route, time) VALUES(%s, %s)"
        try:
            self.cursor.execute(sql, (route, time))
            self.db.commit()
            self.db.close()
        except Exception as e:
            print(e)
            self.db.rollback()
            self.db.close()

    def findRouteByDay(self, day):
        time1 = day
        time2 = day + datetime.timedelta(days=1)
        sql = "SELECT * FROM routes WHERE (time BETWEEN '%s' AND '%s')" % (time1, time2)
        self.connect()
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.db.close()
            return result
        except Exception as e:
            print(e)
            self.db.rollback()
            self.db.close()

    def modifyRouteByID(self, ID, route):
        self.connect()
        sql = "UPDATE routes SET route = '%s' WHERE ID = %s" % (route, ID)
        try:
            self.cursor.execute(sql)
            self.db.commit()
            self.db.close()
        except Exception as e:
            print(e)
            self.db.rollback()
            self.db.close()

    def check_users(self, user, password):
        self.connect()
        sql = "SELECT * FROM auth_users WHERE user= %s AND password= %s" % (user, password)
        try:
            self.cursor.execute(sql)
            result = self.cursor.fetchall()
            self.db.close()
            return result
        except Exception as e:
            print(e)
            self.db.rollback()
            self.db.close()
# dict = {'OrderId': 4457220510, 'UserName': '刘伊婷', 'UserPhone': '22217413,6695', 'CardNo': '', 'LocationName': '成都', 'LocationDetail': '马家花园路11号成都宽窄巷子希尔顿欢朋酒店', 'GetTime': '2018-12-21 14:23:00', 'Lat': 30.687349, 'Lng': 104.05939, 'GetTransferTid': '退房行李放置前台即可离店', 'DestinationDetail': '双流机场T2航站楼', 'TakeTime': '2018-12-21 18:30:00', 'ToLat': 30.575265, 'ToLng': 103.961569, 'TakeTransferTid': '行李员当面交付(出发层9号门)', 'BookingPhotos': None, 'OrderState': 5, 'Remark': '', 'FlightNumber': 'CA4417', 'CreateTime': '2018-12-21 14:22:58', 'BagCount': 2, 'CourierName': '伍贞勇', 'CourierPhone': '15982234919', 'DataChange_LastTime': '2018-12-21 19:56:08'}