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 document: 查找的数据
: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)
