python 对接各大数据库，快速上手！

 

　

1、mysql

 

 

安装pymysql

pip intsall pymysql 

快速上手

import pymysql
from pymysql.cursors import DictCursor


trucker_sql = {
    "host": "112.00.00.00",
    "user": "root",
    "password": "python",
    "port": 9933,
    "charset": "utf8",
    "db": "lowentest"  # 库名
}


class HandleMysql:

    def __init__(self):
        self.conn = pymysql.connect(host=trucker_sql["host"],
                                    user=trucker_sql["user"],
                                    password=trucker_sql["password"],
                                    port=trucker_sql["port"],
                                    charset=trucker_sql["charset"],
                                    db=trucker_sql["db"],
                                    cursorclass=DictCursor)

    def find_one_sql(self, sql: str):
        """ 查询一条数据 """
        cur = self.conn.cursor()
        cur.execute(sql)
        res = cur.fetchone()
        self.conn.commit()
        cur.close()
        return res

    def find_more_sql(self, sql: str):
        """ 查询多条数据 """
        cur = self.conn.cursor()
        cur.execute(sql)
        res = cur.fetchall()
        self.conn.commit()
        cur.close()
        return res

    def find_count(self, sql: str):
        cur = self.conn.cursor()
        res = cur.execute(sql)
        self.conn.commit()
        cur.close()
        return res

    def __del__(self):
        self.conn.close()


if __name__ == '__main__':
    from pprint import pprint
    db = HandleMysql()
    sql = "select * from tb_book where id=1"
    pprint(db.find_one_sql(sql))

　　

　　

2、oracle

 

python中对接oracle数据库,使用的第三方库为cx_Oracle

安装

pip install cx_Oracle 

快速上手

import cx_Oracle                   

# 第一块 连接数据库 , 参数为'账号/密码/@ip:端口/库名'
con=cx_Oracle.connect('user/password@host/databases') 

# 第二步 创建游标
cur=con.cursor()                    
# 第三步执行sql语句
sql = 'SELECT * FROM students;'
cur.execute(sql)

　　

3、sql-server

python对接sqlserver使用的第三方库：pymssql

安装pymssql

pip install pymssql 

快速上手

import pymssql


# 第一步：连接到数据库
con=pymssql.connect(host='xxx',        # 数据库的地址
                    user='xxx',        # 登录数据库的账号
                    password='xxxx',   # 登录数据库的密码
                    database='xxx')    # 库名称

# 第二步：创建游标
cur = con.cursor()
# 第三步：执行对应的sql语句  方法：execute（）
sql = 'SELECT * FROM students;'
cur.execute(sql)

　　

4、postgreSQL

 

python对接postgreSQL使用的模块是psycopg2

安装

pip install psycopg2 

快速上手

# pip install psycopg2

import psycopg2
from psycopg2.extras import DictCursor, RealDictCursor

# from psycopg2.extras import DictCursor,RealDictCursor


trucker_sql = {
    "host": "test.xxxxx.com",
    "user": "root",
    "password": "123456",
    "port": 5432,
    "database": "truckloads",  # 库名
}


class HandleMysql:

    def __init__(self):
        self.conn = psycopg2.connect(
            host=trucker_sql['host'],
            user=trucker_sql['user'],
            password=trucker_sql['password'],
            port=trucker_sql['port'],
            database=trucker_sql['database'],
            cursor_factory=DictCursor
        )
        self.cur = self.conn.cursor()

    def find_one_sql(self, sql: str):
        """ 查询一条数据 """
        try:
            self.cur.execute(sql)
            res = self.cur.fetchone()
            self.conn.commit()
            return dict(res)
        except Exception:
            return {"msg": "数据库暂无数据"}

    def find_more_sql(self, sql: str):
        """ 查询多条数据 """
        try:
            self.cur.execute(sql)
            columns = [title[0] for title in self.cur.description]
            results = [dict(zip(columns, dict(row).values())) for row in self.cur.fetchall()]
            self.conn.commit()
            return results
        except Exception:
            return {"msg": "数据库暂无数据"}

    def __del__(self):
        self.cur.close()
        self.conn.close()


post_db = HandleMysql()


if __name__ == '__main__':
    from pprint import pprint
    db = HandleMysql()
    sql2 = "SELECT * FROM turn_by_turn where username='9599@gmail.com' and platform='Android' order by created_at desc limit 1;"
    res2 = db.find_one_sql(sql2)
    pprint(res2)

　　

　　

5、MongoDB

 

 python中操作mongodb使用的第三方库为 pymongo

安装pymongo

pip install pymongo 

快速上手

from pymongo import MongoClient
import pprint

class MongoHandler:
    host = "test-xxxxxx.com"  # host
    port = "3717"  # 端口
    db_name = "pay-test"  # 库名
    username = "root"   # 用户名
    password = "0000000"  # 密码
    connection_string = f"mongodb://{username}:{password}@{host}:{port}/{db_name}"

    def __init__(self):
        self.client = MongoClient(self.connection_string)
        self.db = self.client[self.db_name]

    def insert_document(self, collection_name, document):
        """
        【操作-------增加】
        :param collection_name: 表名
        :param document: 新增的数据
        :return:
        """
        collection = self.db[collection_name]
        result = collection.insert_one(document)
        return result.inserted_id

    def find_document(self, collection_name, query):
        """
        【操作-------查找】
        :param collection_name: 表名
        :param query: 查找的数据
        :return:
        """
        collection = self.db[collection_name]
        result = collection.find(query)
        return [doc for doc in result]

    def update_document(self, collection_name, query, update):
        """
        【操作-------修改】
        :param collection_name: 表名
        :param query: 被修改的数据
        :param update: 修改后的数据
        :return:
        """
        # $inc、$push、$pull和$set是  MongoDB中常用的更新操作符，可用于更新文档中的字段值。
        # $inc用于将字段值增加一个指定的数值。
        # $push用于向数组字段中添加一个或多个元素。
        # $pull用于从数组字段中删除满足特定条件的元素。
        # $set用于设置字段的值。
        collection = self.db[collection_name]
        result = collection.update_many(query, update)
        return result.modified_count

    def delete_document(self, collection_name, query):
        """
        【操作-------删除】
        :param collection_name: 表名
        :param query: 被删除的数据
        :return:
        """
        collection = self.db[collection_name]
        result = collection.delete_many(query)
        return result.deleted_count

    def close(self):
        self.client.close()


def run_mogodb():
    # mogodb：查
    order_id = {"order_id": "23070710531775089601"}
    cha = myclient.find_document("calm_deduct", order_id)
    pprint.pprint(cha)

    # mogodb：改
    ord_data = {"order_id": "22120120091662084196", "application": "Beijing"}
    new_data = {'$set': {'application': "shangcheng"}}
    cha = myclient.update_document("calm_deduct", ord_data, new_data)
    pprint.pprint(cha)

    # mogodb：删除
    del_data = {"order_id": "22120120091662084196", "application": "shangcheng"}
    cha = myclient.delete_document("calm_deduct", del_data)
    pprint.pprint(cha)

    # mogodb：增
    insrt_data = {'_class': 'com.juzishuke.calm.mongo.entity.CalmDeduct',
                  '_id': 885248029140885509,
                  'already_capital_side': 265,
                  'application': 'shangcheng',
                  'bank_card_id': '324254362',
                  'card_no': '7Ged1mhh9x4ov+e4Y6zUv25lQNr2W8/UJkXdATf+pz0=',
                  'cert_no': 'xAVBihu0GhJRanWto/kLgJNBSYr3JC/jidW5pr3j83U=',
                  'contract_num': 'JZSC23070723070710531775089601',
                  'create_time': (2023, 7, 10, 10, 39, 37, 260000),
                  'order_id': '23070710531775089601',
                  'period': 2,
                  'read_state': 1,
                  'repay_date': (2023, 7, 9, 16, 0),
                  'trade_name': '技能培训'}
    cha = myclient.insert_document("calm_deduct", insrt_data)
    pprint.pprint(cha)


if __name__ == '__main__':
    import datetime
    order_id = "23070710531775089601"

    myclient = MongoHandler()

    # 先查看
    order_id = {"order_id": order_id}
    cha = myclient.find_document("calm_deduct", order_id)
    sql_time = cha[0]["create_time"]
    print("数据库的时间", sql_time)

    timestamp = sql_time.timestamp()
    print("数据库的时间转为时间戳", timestamp)

    dt = datetime.datetime.fromtimestamp(timestamp)
    print("数据库时间转为时间戳", dt)
    new_dt = dt - datetime.timedelta(minutes=11)
    print("计算十一分钟前的时间", new_dt)

    # 后修改时间
    ord_data = {"order_id": order_id, "create_time": sql_time}
    new_data = {'$set': {'create_time': "new_dt"}}
    cha = myclient.update_document("calm_deduct", ord_data, new_data)
    pprint.pprint(cha)

　　

　　

6、Redis

 

 

 

 python操作redis的模块为 redis

安装

pip install redis 

快速上手

import redis 

st = redis.StrictRedis(
    host='localhost',# 服务器本机 
    port='6379',     # 端口：
    db=0,            # 库：
)
# redis操作的命令，对应st对象的方法
# 比如在数据库中创建一条键为test的数据，往里面添加3个元素
st.lpush('test',11,22,33)