使用mysqlclient

先安裝mysqlclient

網址：https://pypi.org/project/mysqlclient/
python中可使用pip安裝驹溃，pip install mysqlclient

也可以使用別的方法豌鹤，詳細可查看之前的文章：
爬取百度百科詞條寫入數(shù)據(jù)庫

python操作mysql

查詢數(shù)據(jù)

import MySQLdb

# 獲取連接
connection = MySQLdb.connect(
    host = 'localhost',
    user = 'root',
    password = 'password',
    db = 'school',
    charset = 'utf8mb4',
    port = 3306 # 默認3306，可不填port
)

# 獲取數(shù)據(jù)
cursor = connection.cursor()
cursor.execute('SELECT * FROM `students`ORDER BY `in_time`DESC;') 
result =  cursor.fetchone()  # 獲取第一條數(shù)據(jù)
print (result)

# 關閉連接
connection.close()

可能會出現(xiàn)異常蚊惯，所以改寫

import MySQLdb

try:
# 獲取連接
    connection = MySQLdb.connect(
        host = 'localhost',
        user = 'root',
        password = 'password',
        db = 'school',
        charset = 'utf8mb4',
        port = 3306 # 默認3306灵临，可不填port
    )
    # 獲取數(shù)據(jù)
    cursor = connection.cursor()
    cursor.execute('SELECT * FROM `students`ORDER BY `in_time`DESC;') 
    result =  cursor.fetchone()  # 獲取第一條數(shù)據(jù)
    print (result)

except MySQLdb.Error as e:
    print('Error : %s ' % e)


finally:
    # 關閉連接
    connection.close()

因為這個操作是所有都有的儒溉，所以封裝成一個對象

import MySQLdb

class MysqlSearch:
    def __init__(self):
        self.get_connection()
    
    def get_connection(self):
        try:
            self.connection = MySQLdb.connect(
                host = 'localhost',
                user = 'root',
                password = 'password',
                db = 'school',
                charset = 'utf8mb4',
                port = 3306 # 默認3306，可不填port
            )          
        except MySQLdb.Error as e:
            print('Error : %s ' % e)

    def close_connection(self):
        try:
            if self.connection:
                self.connection.close()
        except MySQLdb.Error as e:
            print('Error : %s ' % e)

    def get_one(self):
            # 獲取會話指針
            cursor = self.connection.cursor()
            # 準備sql
            sql = 'SELECT * FROM `students`WHERE`name`=%s ORDER BY `in_time`DESC;'
            # 執(zhí)行sql
            cursor.execute(sql,('weilai',)) 
            # print(cursor.description)
            ## (('id', 3, 1, 11, 11, 0, 0), ('name', 253, 6, 80, 80, 0, 0), 
            ## ('nickname', 253, 4, 80, 80, 0, 1), ('sex', 254, 3, 4, 4, 0, 1),
            ## ('in_time', 12, 19, 19, 19, 0, 1))
            ## 獲得一條結果
            # a = [k[0] for k in cursor.description],
            ## a = ['id', 'name', 'nickname', 'sex', 'in_time']
            # b = [k[0] for k in cursor.description],cursor.fetchone()
            ## (['id', 'name', 'nickname', 'sex', 'in_time'],
            ##  (7, 'weilai', 'imwl', '男', datetime.datetime(2018, 12, 27, 22, 5, 41)))
            result = dict(zip([k[0] for k in cursor.description],cursor.fetchone()))  
            # 關閉 cursor 和連接
            cursor.close()
            self.close_connection()
            return result

    def get_more(self):
            cursor = self.connection.cursor()
            sql = 'SELECT * FROM `students`WHERE`name`=%s ORDER BY `in_time`DESC;'
            cursor.execute(sql,('weilai',)) 
            result = [dict(zip([k[0] for k in cursor.description],row))
                for row in cursor.fetchall()] 
            # print(result) 
            # [{'id': 7, 'name': 'weilai', 'nickname': 'imwl', 'sex': '男', 'in_time': datetime.datetime(2018, 12, 27, 22, 5, 41)}, 
            # {'id': 8, 'name':'weilai', 'nickname': 'imwl', 'sex': '男', 'in_time': datetime.datetime(2018, 12, 27, 22, 5, 41)},
            # {'id': 9, 'name': 'weilai', 'nickname': 'imwl', 'sex': '男', 'in_time': datetime.datetime(2018, 12, 27, 22, 5, 41)}]
            cursor.close()
            self.close_connection()
            return result


def main():
    obj = MysqlSearch()
    # a = obj.get_one()
    # print(a)
    # print(a['id'])

    b = obj.get_more()
    for item in b:
        print(item)


if __name__ == '__main__':
    main()

# zip函數(shù)
'''
zip() 將對象中對應的元素打包成一個個元組涛碑，然后返回由這些元組組成的對象，元素個數(shù)與最短的一致
>>> a = [1,2,3]
>>> b = [4,5,6]
>>> c = [4,5,6,7,8]
>>> zipped = zip(a,b)     # 返回一個對象
>>> zipped
<zip object at 0x103abc288>
>>> list(zipped)  # list() 轉換為列表
[(1, 4), (2, 5), (3, 6)]
>>> list(zip(a,c))              # 元素個數(shù)與最短的列表一致
[(1, 4), (2, 5), (3, 6)]
 
>>> a1, a2 = zip(*zip(a,b))          # 與 zip 相反歹篓，zip(*) 可理解為解壓，返回二維矩陣式
>>> list(a1)
[1, 2, 3]
>>> list(a2)
[4, 5, 6]
'''

** 補充(分頁查詢)：

    def get_more_by_pages(self, page, page_size):
        # 分頁查詢數(shù)據(jù)
        offset =  (page -1) * page_size

        cursor = self.connection.cursor()
        sql = 'SELECT * FROM `students`WHERE`name`=%s ORDER BY `in_time`DESC LIMIT %s , %s;'
        cursor.execute(sql,('weilai', offset, page_size)) 
        result = [dict(zip([k[0] for k in cursor.description],row))
         for row in cursor.fetchall()] 
            # print(result) 
            # [{'id': 7, 'name': 'weilai', 'nickname': 'imwl', 'sex': '男', 'in_time': datetime.datetime(2018, 12, 27, 22, 5, 41)}, 
            # {'id': 8, 'name':'weilai', 'nickname': 'imwl', 'sex': '男', 'in_time': datetime.datetime(2018, 12, 27, 22, 5, 41)},
            # {'id': 9, 'name': 'weilai', 'nickname': 'imwl', 'sex': '男', 'in_time': datetime.datetime(2018, 12, 27, 22, 5, 41)}]
        cursor.close()
        self.close_connection()
        return result

新增/修改數(shù)據(jù)到數(shù)據(jù)庫

出現(xiàn)問題不應該提交

    def add_one(self):
        # 準備SQL
        try:
            sql = (
                "INSERT INTO `students` (`name`,`nickname`,`sex`,`in_time`) VALUE"
                "(%s,%s,%s,%s);"
                )
            cursor = self.connection.cursor()

            # 可以提交多條
            cursor.execute(sql,('name1', 'nickname1', '男', None))
            cursor.execute(sql,('name2', 'nickname2', '男', 'haha'))
            # 提交事務
            self.connection.commit()
            # 關閉cursor和連接
            cursor.close()
            
        except MySQLdb.Error as e:
            print('Error : %s ' % e)
            self.connection.rollback()
        
        self.close_connection()