Python从入门到进阶(十五)——数据库操作

最新推荐文章于 2024-07-12 16:16:27 发布

꧁༺℘₨风、凌๓༻꧂

最新推荐文章于 2024-07-12 16:16:27 发布

阅读量41

点赞数

分类专栏： Python学习文章标签： python 数据库开发语言

Python学习专栏收录该内容

24 篇文章 1 订阅

订阅专栏

数据库操作

目前主流的数据库有两种，

一个是关系型数据库，如MySql
一个是非关系型数据库如mongodb
对与mysql和mongodb数据库，python中分别有对接的连接操作库

Python连接mysql:
在python中,连接mysql用到的库是

pymysql

安装方法直接使用命令：

pip install pymsql

引入方式：

import pymysql

连接数据库:

db=mysql.connect(host='127.0.0.1',user='root',password='123456',database='bookmanager')

获取游标:

cur=db.cursor()

关闭数据库:

db.close()

使用python执行增删改查的操作的完整代码:

#!/usr/bin/python3
 
import pymysql
 
db = pymysql.connect(host='127.0.0.1',
                     user='root',
                     password='123456',
                     database='bookmanager')
#print(db)
cur = db.cursor()
#print(cur)

# SQL 插入语句
sql = """INSERT INTO BOOK(NAME,
         AUTHOR, PUBLISH, HAVE, HOT)
         VALUES ('001', 'test', '002', 100, 200)"""
# 查询
sql = "SELECT * FROM BOOK WHERE hot > 1000"

#修改
sql = "UPDATE BOOK SET name = 'ZS1000' WHERE hot > 1000"

#删除
sql = "delete FROM BOOK where  hot > 1000"

sql = """CREATE TABLE TEST (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""
#删除表
sql = "DROP TABLE IF EXISTS TEST"



try:
    cur.execute(sql)
    db.commit()
except:
    print('have exception')
    db.rollback()

db.close()

python连接mongdb的库是:

pymongo

安装以及引入

pip install pymongo
import pymongo

建立数据库连接

mongo_client=pymongo.MongoClient("mongodb://192.168.1.200:30000")

指定数据库:

mongo_db=mongo_client["test"]

操作数据库完整代码如下:

#!/usr/bin/python3
 
import pymongo

mongo_client =  pymongo.MongoClient("mongodb://192.168.1.200:30000")

print(mongo_client)

mongo_db = mongo_client["test"]

print(mongo_db)

#获取集合
collist = mongo_db.list_collection_names()

#print(collist)

coll = mongo_db['new_coll']
#print(coll)
#coll.insert_one({'name':'new_coll'})
#获取集合
#collist = mongo_db.list_collection_names()
#print(collist)

coll = mongo_db["new_coll"]

for x in coll.find():
    print(x)

#query_my = {"name":"new_coll"}
#update_q = {"$set":{"name":"new_coll_1"}}

#coll.update_one(query_my,update_q)
#print(coll.find()[0])
print('*'*25)
query_my = {"name":"new_coll"}
coll.delete_many(query_my)

for x in coll.find():
    print(x)

错误码处理
编写一个错误码枚举，实现一个 “错误码转换成字符串” 的静态方法： internal_ret_2_http

编写一个错误码枚举，实现一个 "错误码转换成字符串" 的静态方法： internal_ret_2_http

# -*- coding: UTF-8 -*-
from enum import Enum

class ErrorCode(Enum):
    SUCCESS = 0
    FAILED = 1
    NOT_FOUND = 2
    ALREADY_EXIST = 3
    INVALID_PARAMETERS = 4

    @staticmethod
    def internal_ret_2_http(ret):
        # TODO(You): 请在此实现代码

if __name__ == '__main__':
    ret = {'err': ErrorCode.NOT_FOUND}
    ErrorCode.internal_ret_2_http(ret)
    assert ret['err'] == 'not_found'

请选出下列能正确实现这一功能的选项。
# A.
ret['err'] = str(ret['err'])
# B.
ret['err'] = ret['err'].name
# C.
ret['err'] = str(ret['err']).lower()
# D.
ret['err'] = ret['err'].name.lower()

# D正确

Python Sqlite Connector
对 sqlite3 做一个基本的封装，支持 open/close，打开的时候配置检查后续操作在同一个线程

对 sqlite3 做一个基本的封装，支持 open/close，打开的时候配置检查后续操作在同一个线程

# -*- coding: UTF-8 -*-
import logging
import sqlite3
from error_code import ErrorCode
logger = logging.Logger(__name__)

class SqliteConnector():
    def __init__(self, db_file) -> None:
        self.db_file = db_file
        self.conn = None

    def open(self):
        # TODO(You): 请实现打开Sqlite代码

    def close(self):
        if self.conn is not None:
            self.conn.close()
            self.conn = None
        return {'err': ErrorCode.SUCCESS}


if __name__ == '__main__':
    kv = SqliteConnector("/tmp/test.db")

    ret = kv.open()
    assert ret['err'] == ErrorCode.SUCCESS
    ret = kv.close()
    assert ret['err'] == ErrorCode.SUCCESS

请选出下列能正确实现这一功能的选项。
# A.
class SqliteConnector():
    ...
    def open(self):
        try:
            self.conn = sqlite3.connect(self.db_file, check_same_thread=True)
            return {"err": ErrorCode.SUCCESS}
        except Exception as e:
            logger.error('open sqlite exception:', str(e))
            return {"err": ErrorCode.DB_OPEN_FAILED}
# B.
class SqliteConnector():
    ...
    def open(self):
        try:
            self.conn = sqlite3.connect(self.db_file, check_same_thread=True)
            return {"err": ErrorCode.SUCCESS}
        except Exception as e:
            logger.error('open sqlite exception:', str(e))
            self.close()
            return {"err": ErrorCode.DB_OPEN_FAILED}
# C.
class SqliteConnector():
    ...
    def open(self):
        self.conn = sqlite3.connect(self.db_file, check_same_thread=True)
        return {"err": ErrorCode.SUCCESS}
# D.
class SqliteConnector():
    ...
    def open(self):
        try:
            self.conn = sqlite3.connect(self.db_file)
            return {"err": ErrorCode.SUCCESS}
        except Exception as e:
            logger.error('open sqlite exception:', str(e))
            self.close()
            return {"err": ErrorCode.DB_OPEN_FAILED}
# B正确

Python SQLITE Connection
继承自前一节的 SqliteConnector，增加执行sql接口，请先完成【Python SQLITE Connector】

继承自前一节的 SqliteConnector，增加执行sql接口，请先完成【Python SQLITE Connector】

# -*- coding: UTF-8 -*-
import logging
from error_code import ErrorCode
from sqlite_connector import SqliteConnector
logger = logging.Logger(__name__)

class SqliteConnection(SqliteConnector):
    def __init__(self, db_file) -> None:
        super().__init__(db_file)

    def execute(self, sql, arg=None):
        # TODO(You): 请在此实现查询代码


if __name__ == '__main__':
    kv = SqliteConnection("/tmp/test.db")

    ret = kv.open()
    assert ret['err'] == ErrorCode.SUCCESS

    sql = '''create table if not exists key_value (
        _key varchar(32) primary key not null,
        value text not null
    );'''
    ret = kv.execute(sql)
    assert ret['err'] == ErrorCode.SUCCESS
    ret = kv.close()
    assert ret['err'] == ErrorCode.SUCCESS

请选出下列能正确实现这一功能的选项。
# A.
class SqliteConnection(SqliteConnector):
    ...
    def execute(self, sql, arg=None):
        results = []
        try:
            cursor = self.conn.execute(sql, arg or tuple())
            if cursor is not None:
                results = cursor.fetchall()
            self.conn.commit()
            return {'err': ErrorCode.SUCCESS, 'results': results}
        except Exception as e:
            logger.error(
                f'execute sql exception, sql:{sql}, arg:{arg}, exception:{str(e)}')
            return {'err': ErrorCode.DB_QUERY_EXCEPT, 'results': results}
# B.
class SqliteConnection(SqliteConnector):
    ...
    def execute(self, sql, arg=None):
        results = []
        cursor = self.conn.execute(sql, arg or tuple())
        if cursor is not None:
            results = cursor.fetchall()
        self.conn.commit()
        return {'err': ErrorCode.SUCCESS, 'results': results}
# C. 
class SqliteConnection(SqliteConnector):
    ...
    def execute(self, sql, arg=None):
        results = []
        try:
            cursor = self.conn.execute(sql, arg or tuple())
            if cursor is not None:
                results = cursor.fetchall()
            self.conn.commit()
            return {'err': ErrorCode.SUCCESS, 'results': results}
        except Exception as e:
            logger.error(
                f'execute sql exception, sql:{sql}, arg:{arg}, exception:{str(e)}')
            self.conn.rollback()
            return {'err': ErrorCode.DB_QUERY_EXCEPT, 'results': results} 
# D.
class SqliteConnection(SqliteConnector):
    ...
    def execute(self, sql, arg=None):
        results = []
        try:
            cursor = self.conn.execute(sql, arg or tuple())
            if cursor is not None:
                results = cursor.fetchall()
            return {'err': ErrorCode.SUCCESS, 'results': results}
        except Exception as e:
            logger.error(
                f'execute sql exception, sql:{sql}, arg:{arg}, exception:{str(e)}')
            self.conn.rollback()
            return {'err': ErrorCode.DB_QUERY_EXCEPT, 'results': results}
# C正确

Python Sqlite 读写
使用 SQLITE 实现一个 key-value(string:json) 读写库，请先完成【Python SQLITE Connector】和【Python SQLITE Connection】

# -*- coding: UTF-8 -*-
import logging
import json
from error_code import ErrorCode
from sqlite_connection import SqliteConnection
logger = logging.Logger(__name__)

class SqliteKeyValueStore(SqliteConnection):
    def __init__(self, db_file) -> None:
        super().__init__(db_file)

    def open(self):
        ret = super().open()
        if ret['err'] != ErrorCode.SUCCESS:
            return ret

        sql = '''create table if not exists key_value (
            _key varchar(32) primary key not null,
            value text not null
        );'''
        return self.execute(sql)

    def set(self, key, value):
        # TODO(You): 请在此实现Sqlite写入key-value代码，其中value是json

    def get(self, key):
        if self.conn is None:
            return {'err': ErrorCode.SQLITE_NOT_OPEN}

        try:
            sql = 'select value from key_value where _key=?'
            ret = self.execute(sql, [key])
            if ret['err'] != ErrorCode.SUCCESS:
                return ret

            results = ret['results']
            if len(results) == 0:
                return {"err": ErrorCode.NOT_FOUND}
            else:
                value = json.loads(results[0][0])
                return {"err": ErrorCode.SUCCESS, "key": key, "value": value}
        except Exception as e:
            logger.error(f'get key:{key} exception:{str(e)}')
            return {"err": ErrorCode.DB_QUERY_EXCEPT}


if __name__ == '__main__':
    kv = SqliteKeyValueStore("/tmp/test.db")

    ret = kv.open()
    assert ret['err'] == ErrorCode.SUCCESS

    ret = kv.set('test', {"number": 0})
    assert ret['err'] == ErrorCode.SUCCESS

    ret = kv.get('test')
    print(ret)
    assert ret['err'] == ErrorCode.SUCCESS
    assert ret['value']['number'] == 0
    ret = kv.close()
    assert ret['err'] == ErrorCode.SUCCESS
请选出下列能正确实现这一功能的选项。
# A.
class SqliteKeyValueStore(SqliteConnection):
    ...
    def set(self, key, value):
        try:
            value = json.dumps(value)
            sql = 'insert or replace into key_value values(?, ?)'
            return self.execute(sql, [key, value])
        except Exception as e:
            logger.error(f'set key:{key} exception:{str(e)}')
            return {"err": ErrorCode.DB_QUERY_EXCEPT}
# B.
class SqliteKeyValueStore(SqliteConnection):
    ...
    def set(self, key, value):
        if self.conn is None:
            return {'err': ErrorCode.DB_OPEN_FAILED}

        value = json.dumps(value)
        sql = 'insert or replace into key_value values(?, ?)'
        return self.execute(sql, [key, value])        
# C.
class SqliteKeyValueStore(SqliteConnection):
    ...
    def set(self, key, value):
        if self.conn is None:
            return {'err': ErrorCode.DB_OPEN_FAILED}

        try:
            value = json.dumps(value)
            sql = 'insert or replace into key_value values(?, ?)'
            return self.execute(sql, [key, value])
        except Exception as e:
            logger.error(f'set key:{key} exception:{str(e)}')
            return {"err": ErrorCode.DB_QUERY_EXCEPT}
# D.
class SqliteKeyValueStore(SqliteConnection):
    ...
    def set(self, key, value):
        if self.conn is None:
            return {'err': ErrorCode.DB_OPEN_FAILED}

        try:
            sql = 'insert or replace into key_value values(?, ?)'
            return self.execute(sql, [key, value])
        except Exception as e:
            logger.error(f'set key:{key} exception:{str(e)}')
            return {"err": ErrorCode.DB_QUERY_EXCEPT}

Python Redis 安装说明
熟悉Redis的基本操作,下面的代码会打印:
1. 安装Redis
2. 启动redis服务的命令
3. 登录进入redis客户端
4. 安装redis的Python库

class Redisuse:
    def dump_depends(self,depends):
        print()
        print("#依赖配置")
        for i in range(0,len(depends)):
            print("{}.{}".format(i,depends[i]))

    def testRedis(self):
        start_server='redis-server'
        login_redis='redis-cli -h 127.0.0.1 -p 6379'

        self.dump_depends([
            '安装redis:https://redis.io/download',
            f'启动redis服务:{start_server}',
            f'进入redis命令行客户端:{login_redis}',
            '库安装:pip install redis',
            ])

Python Redis连接器
实现一个redis连接器,能打开/关闭redis连接,使用连接池

class RedisConnector():
    def __init__(self,host,port,password) -> None:
        global redis_connection_pool
        if redis_connection_pool is None:
            redis_connection_pool=redis.ConnectionPool(
                host=host,
                port=port,
                password=password
                )
        self.conn=None
    # 打开redis
    def open(self):
        if self.conn is not None:
            return {'err':ErrorCode.SUCCESS}
        try:
            self.conn=redis.StrictRedis(connection_pool=redis_connection_pool)
            return {'err':ErrorCode.SUCCESS}
        except Exception as e:
            logger.error('open redis exception:',str(e))
            self.conn=None
            return {'err':ErrorCode.DB_OPEN_FAILED}
            
        pass

    def close(self):
        if self.conn is not None:
            self.conn.close()
            self.conn=None
            return {'err':ErrorCode.SUCCESS}

Python Redis读写
使用Redis实现一个key-value(string:json)读写库.请先完成Python Redis安装说明和Python Redis连接器

# Redis读写
class RedisKeyValueStore(RedisConnector):
    def __init__(self, host, port, password) -> None:
        super().__init__(host, port, password)
    
    def set(self,key,value):
        
        if self.conn is None:
            return {'err':ErrorCode.DB_OPEN_FAILED}
        try:
            self.conn.set(key,json.dumps(value))
            return {'err':ErrorCode.SUCCESS}
        except Exception as e:
            logger.error(f'set key:{key}exception:{str(e)}')
            return {'err':ErrorCode.DB_QUERY_EXCEPT}
    
    def get(self,key):
        if self.conn is None:
            return {'err':ErrorCode.SUCCESS}

        try:
            results=self.conn.get(key)
            if results is None:
                return {'err':ErrorCode.NOT_FOUND}
            return {'err':ErrorCode.SUCCESS,'key':key,'value':json.loads(results)}
        except Exception as e:
            logger.error(f'get key:{key}exception:{str(e)}')
            return {'err':ErrorCode.DB_QUERY_EXCEPT}


kvr=Mysqluse.RedisKeyValueStore('127.0.0.1',6379,None)
    ret=kvr.open()
    assert ret['err']==Mysqluse.ErrorCode.SUCCESS
    ret=kvr.set('test',{'number':0})
    print(ret['err'])
    assert ret['err']==Mysqluse.ErrorCode.SUCCESS
    ret=kvr.get("test")
    print(ret)
    assert ret['err']==Mysqluse.ErrorCode.SUCCESS
    assert ret['value']['number']==0
    ret=kvr.close()
    assert ret['err']==Mysqluse.ErrorCode.SUCCESS

Python MySQL 安装说明
本机测试需要先安装mysql,假设密码是123456,请勿在命令行下直接输入密码.下面的代码输出的信息如下:
1. 安装MySQL
2. 启动MySQL
3. 登录数据库
4. 创建一个test数据库,创建一个key_value表,包含key和value两个字段.

class Mysqlinstall():
    def dump_depends(depends):
        print()
        print("#依赖配置")
        for i in range(0,len(depends)):
            print("{}.{}".format(i,depends[i]))

db='''
    create database test;
    use test;
    create table if not exists key_value(
        _key varchar(32) not null,
        value text not null,
        primary key('_key')
    )ENGINE=InnoDB DEFAULT CHARSET=utf8
    '''

    login_commands='mysql -h 127.0.0.1 -P 3306 -u root -p'
    msi=Mysqluse.Mysqlinstall()
    msi.dump_depends([
        '安装Mysql:https://dev.mysql.com/downloads/installer/',
        '启动MySql本地服务:每个平台不同',
        f'登录数据库:{login_commands}',
        f'请创建初始化数据库:\n{db}',
        'Python库安装:pip install mysql-connector-python'
        ])

Python Mysql0 Connector
封装一个Mysql的连接器,支持open/close

from mysql.connector import pooling
from mysql.connector import Error
import logging
logger=logging.Logger(__name__)
mysql_connection_pool=None

class MySqlConnector():
    def __init__(self,host,port,user,password,database) -> None:
        global mysql_connection_pool
        if mysql_connection_pool is None:
            mysql_connection_pool=pooling.MySQLConnectionPool(
                pool_name='some_pool_name',
                pool_size=10,
                pool_reset_session=True,
                host=host,
                port=port,
                database=database,
                user=user,
                password=password
                )

        self.conn=None

    def open(self):
        try:
            self.conn=mysql_connection_pool.get_connection()
            if self.conn.is_connected():
                db_info=self.conn.get_server_info()
                print("db info:",db_info)
                return {'err':ErrorCode.SUCCESS}
            else:
                self.conn=None
                logger.error('open mysql connection failed,can not connect.')
                return {'err':ErrorCode.DB_OPEN_FAILED}
        except Error as e:
            logger.error('open mysql connection exception:',str(e))
            self.close()
            return {'err':ErrorCode.DB_OPEN_FAILED}
        pass

    def close(self):
        if self.conn is not None:
            if self.conn.is_connected():
                self.conn.close()
            self.conn=None
        return {'err':ErrorCode.SUCCESS}

## MysqlConnector
    kvmsc=Mysqluse.MySqlConnector(
        '127.0.0.1',3306,
        'root','WNpx8c\zr!fF',
        'test'
        )

    ret=kvmsc.open()
    if ret['err']==Mysqluse.ErrorCode.SUCCESS:
        print('数据库打开成功')
        kvmsc.close()

Python Mysql Connection
封装一个MySQL连接类,继承自MySQLConnector,请先完成[Python MySQL Connector]

class MySQLConnection(MySqlConnector):
    def __init__(self, host, port, user, password, database) -> None:
        super().__init__(host, port, user, password, database)
    
    def execute(self,sql,args):
        cursor=self.conn.cursor()
        try:
            cursor.execute(sql,args)
            results=cursor.fetchall() or []
            self.conn.commit()
            return {
                'err':ErrorCode.SUCCESS,
                'results':list(map(lambda t:t[0],results))
                }
        except Error as e:
            self.conn.rollback()
            logger.error('execute mysql query exception',str(e))
            return {'err':ErrorCode.DB_QUERY_EXCEPT}
        finally:
            cursor.close()
mscc=Mysqluse.MySQLConnection('127.0.0.1',3306,
                                  'root','WNpx8c\zr!fF'
                                  'test')

    ret=mscc.open()
    assert ret['err']==Mysqluse.ErrorCode.SUCCESS

    ret=mscc.execute('select * from test')
    assert ret['err']==Mysqluse.ErrorCode.SUCCESS
    ret=mscc.close()
    assert ret['err']==Mysqluse.ErrorCode.SUCCESS

Python MySql读写
使用MySql实现一个key-value(string:json)读写库,请先完成[Python MySql安装说明],[Python Mysql Connection ]

class MySqlKeyValueStore(MySQLConnection):
    def __init__(self, host, port, user, password, database) -> None:
        super().__init__(host, port, user, password, database)

    def set(self,key,value):
        if self.conn is None:
            return {'err':ErrorCode.DB_OPEN_FAILED}

        try:
            value=json.dumps(value)
            sql='insert into key_value set _key=%s,value=%s on duplicate key update value=%s'
            args=(key,value,value)
            return self.execute(sql,args)
        except Exception as e:
            logger.error(f'set key:{key} exception:{str(e)}')
            return {'err':ErrorCode.DB_QUERY_EXCEPT}

    def get(self,key):
        if self.conn is None:
            return {'err':ErrorCode.DB_OPEN_FAILED}

        try:
            sql='select value from key_value where _key=%s'
            ret=self.execute(sql,(key,))
            if ret['err']!=ErrorCode.SUCCESS:
                return ret
            results=ret['results']
            if len(results)==0:
                logger.warning(f'get key:{key},empty.')
                return {'err':ErrorCode.NOT_FOUND}
            else:
                return {'err':ErrorCode.SUCCESS,'key':key,'value':json.loads(results[0])}
        except Exception as e:
            logger.err(f'get key:{key}exception:{str(e)}')
            return {'err':ErrorCode.DB_QUERY_EXCEPT}

mskv=Mysqluse.MySqlKeyValueStore(
        '127.0.0.1',3306,
        'root','WNpx8c\zr!fF',
        'test')
    ret=mskv.open()
    assert ret['err']==Mysqluse.ErrorCode.SUCCESS
    ret=mskv.set('test',{'number':0})
    assert ret['err']==Mysqluse.ErrorCode.SUCCESS
    ret=mskv.get('test')
    print(ret)
    assert ret['err']==Mysqluse.ErrorCode.SUCCESS
    assert ret['value']['number']==0
    ret=mskv.close()
    assert ret['err']==Mysqluse.ErrorCode.SUCCESS