pymysql
# -*- coding: utf-8 -*- """ @Datetime: 2018/12/26 @Author: Zhang Yafei """ import pymysql from DBUtils.PooledDB import PooledDB POOL = PooledDB( creator=pymysql, # 使用链接数据库的模块 maxconnections=6, # 连接池允许的最大连接数，0和None表示不限制连接数 mincached=2, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建 maxcached=5, # 链接池中最多闲置的链接，0和None不限制 maxshared=3, # 链接池中最多共享的链接数量，0和None表示全部共享。PS: 无用，因为pymysql和MySQLdb等模块的 threadsafety都为1，所有值无论设置为多少，_maxcached永远为0，所以永远是所有链接都共享。 blocking=True, # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错 maxusage=None, # 一个链接最多被重复使用的次数，None表示无限制 setsession=[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."] ping=0, # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always host='127.0.0.1', port=3306, user='root', password='0000', database='flask_code', charset='utf8' ) def connect(type=None): conn = POOL.connection() cursor = conn.cursor(cursor=type) return conn, cursor def connect_close(conn, cursor): cursor.close() conn.close() def fetchone(sql, arg=list()): conn, cursor = connect(type) cursor.execute(sql, arg) data = cursor.fetchone() connect_close(conn, cursor) return data def fetchall(sql, arg=list(), type=pymysql.cursors.DictCursor): conn, cursor = connect(type) cursor.execute(sql, arg) data = cursor.fetchall() connect_close(conn, cursor) return data def insert(sql, arg=list()): conn, cursor = connect() row = cursor.execute(sql, arg) conn.commit() connect_close(conn, cursor) return row
sqlite
# -*- coding: utf-8 -*- """ @Datetime: 2019/1/31 @Author: Zhang Yafei """ import sqlite3 import settings import os BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) DB_DIR = os.path.join(BASE_DIR, 'data.db') def connect(): '''连接数据库''' conn = sqlite3.connect(settings.DATABASES.get('sqlite3')) # db不存在时将自动创建db cursor = conn.cursor() return conn, cursor def connect_close(conn, cursor): """关闭连接""" cursor.close() conn.close() def execute(sql, params=tuple()): conn, cursor = connect() cursor.execute(sql, params) # 执行这个语句 connect_close(conn, cursor) def fetchone(sql, params=tuple()): conn, cursor = connect() result = cursor.execute(sql, params) data = result.fetchone() connect_close(conn, cursor) return data def fetchall(sql, params=tuple()): conn, cursor = connect() results = cursor.execute(sql, params) data = results.fetchall() connect_close(conn, cursor) return data
# -*- coding: utf-8 -*- """ @Datetime: 2019/1/31 @Author: Zhang Yafei """ import sqlite3 import os BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__))) DB_DIR = os.path.join(BASE_DIR, 'data.db') class SqliteDB(object): def __init__(self): self.conn = sqlite3.connect(DB_DIR) # db不存在时将自动创建db self.cursor = self.conn.cursor() def close(self): self.cursor.close() self.conn.close() def execute(self, sql, params=tuple()): self.cursor.execute(sql, params) self.close() def fetchone(self, sql, params=tuple()): result = self.cursor.execute(sql, params) data = result.fetchone() self.close() return data def fetchall(self, sql, params=tuple()): results = self.cursor.execute(sql, params) data = results.fetchall() self.close() return data if __name__ == '__main__': sqlite = SqliteDB() # 1. 建表 sql = '''create table happy( username text, password text, id int)''' sqlite.execute(sql) # 2. 插入数据 sqlite.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \ VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )") # 3. 更改数据 sqlite.execute("UPDATE COMPANY SET ID=99 WHERE ID=2") # 4. 删除表里面的数据 c.execute("DELETE FROM COMPANY WHERE ID=4") c.execute("DELETE FROM COMPANY WHERE ID=3") # 5. 查询 data = sqlite.fetchall('select * from label limit 1') print(data) # 输出 ''' [('盘龙云海(排毒养颜胶囊)', 509881, '广东深圳龙岗区/女', '昨天吃的，今天就拉肚子了。感觉肚子有点涨痛！不知道效果怎么样~~~~~', '昨天/吃/的/，/今天/就/拉肚子/SB了/。/感觉/肚子/PB有点/涨痛/SB！/不/知道/效果/怎么样/~/~/~/~/~', '2011-09-30 15:26:00', 'http://ypk.39.net/509881/comment/k0_p...', '昨天/吃/的/，/今天/就/拉肚子/SB了/。/感觉/肚子/PB有点/涨痛/SB！/不/知道/效果/怎么样/~/~/~/~/~', '昨天/吃/的/，/今天/就/拉肚子/SB了/。/感觉/肚子/PB有点/涨痛/SB！/不/知道/效果/怎么样/~/~/~/~/~')] '''
mongodb
# -*- coding: utf-8 -*- """ @Datetime: 2019/1/31 @Author: Zhang Yafei """ import json import pymongo import pandas as pd class MongoPipeline(object): """ mongodb: save(self, data, collection): 将数据保存到数据库 read(self, data): 读取数据库中指定表格 insert(self, table, dict_data)： 插入数据 delete(self, table, condition)： 删除指定数据 update(self, table, condition, new_dict_data)： 更新指定数据 dbFind(self, table, condition=None): 按条件查找 findAll(self, table)： 查找全部 close(self)： 关闭连接 """ def __init__(self, mongo_db, mongo_uri='localhost'): self.mongo_uri = mongo_uri self.mongo_db = mongo_db self.client = pymongo.MongoClient(self.mongo_uri) self.db = self.client[self.mongo_db] def close(self): """ 关闭连接 :return: """ self.client.close() def save(self, data, collection): """ 将数据保存到数据库表 :param data: :param collection: :return: None """ self.collection = self.db[collection] try: if self.collection.insert(json.loads(data.T.to_json()).values()): print('mongodb insert {} sucess.'.format(collection)) return except Exception as e: print('insert error:', e) import traceback traceback.print_exc(e) def read(self, table): """ 读取数据库中的数据 :param table: :return: dataframe """ try: # 连接数据库 table = self.db[table] # 读取数据 data = pd.DataFrame(list(table.find())) return data except Exception as e: import traceback traceback.print_exc(e) def insert(self, table, dict_data): """ 插入 :param table: :param dict_data: :return: None """ try: self.db[table].insert(dict_data) print("插入成功") except Exception as e: print(e) def update(self,table, condition, new_dict_data): """ 更新 :param table: :param dict_data: :param new_dict_data: :return: None """ try: self.db[table].update(condition, new_dict_data) print("更新成功") except Exception as e: print(e) def delete(self,table, condition): """ 删除 :param table: :param dict_data: :return: None """ try: self.db[table].remove(condition) print("删除成功") except Exception as e: print(e) def dbFind(self, table, condition=None): """ 按条件查找 :param table: :param dict_data: :return: generator dict """ data = self.db[table].find(condition) for item in data: yield item def findAll(self, table): """ 查找全部 :param table: :return: generator dict """ for item in self.db[table].find(): yield item if __name__ == '__main__': mongo = MongoPipeline('flask') # data = mongo.read('label') # print(data.head()) condition = {"药品ID": 509881} data = mongo.dbFind('label', condition) print(data) for i in data: print(i) # mongo.findAll()
扫一扫
215
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
