python连接sqlServer数据库，Oracle数据库，MongoDB数据库，mysql数据库
- python sqlalchemy 简介
- 学习链接
- sqlalchemy是什么？
- sqlalchemy怎么用？
- python sqlalchemy 连接数sqlServer数据
- python连接SQLServer的大致流程流程：
- 1. python创建sqlServer连接,SQLServer_Obj.py:
- 2.主函数调用数据库连接，并读取配置文件
- 3. python读取sqlServer数据库数据，SqlServer_Reader.py
- python连接Oracle数据库
- 1.python sqlalchemy 建立Oracle连接
- 常用格式
python sqlalchemy 简介
学习链接
sqlalchemy是什么？
SQLAlchemy是Python一款强大的ORM软件。
对象关系映射orm（object relational mapping），是通过使用描述对象和数据库之间映射的元数据，将面向对象语言程序中的对象自动持久化到关系数据库中。
sqlalchemy怎么用？
1.引入pymssql，安装pip install pymssql；
2.安装sqlalchemy，pip instal sqlalchemy
安装之后建立连接使用会话：
# 建立连接
from sqlalchemy import create_engine
engine_name = create_engine('mysql://root:@localhost:3306/webpy?
# 创建会话
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine_name)
session = Session() # 实例化session参考连接：
python sqlalchemy 连接数sqlServer数据
python连接SQLServer的大致流程流程：
1.引入pymssql，安装pip install pymssql；
2.安装sqlalchemy，pip instal sqlalchemy
3.连接sqlServer，提前准备好：服务器地址，用户名，密码，数据库名，数据库端口，创建实例create_engine（），连接connect（）
4.sessionmaker参考连接：
（推荐）
（推荐推荐）
项目结构配图
1. python创建sqlServer连接,SQLServer_Obj.py:
- create_engine() 会返回一个数据库引擎，echo 参数为 True 时，会显示每条执行的 SQL 语句，生产环境下可关闭。
- sessionmaker() 会生成一个数据库会话类。这个类的实例可以当成一个数据库连接，它同时还记录了一些查询的数据，并决定什么时候执行 SQL 语句。
- 由于 SQLAlchemy 自己维护了一个数据库连接池（默认 5 个连接），因此初始化一个会话的开销并不大。
from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import sessionmaker
class SqlServer_Obj():
'''
构造函数
'''
def __init__(self, config_init):
self.isSuccess = True
try:
self.config_init = config_init
self.host = config_init.get("SQLServer", "host")
self.port = config_init.get("SQLServer", "port")
self.name = config_init.get("SQLServer", "name")
self.user = config_init.get("SQLServer", "user")
self.password = config_init.get("SQLServer", "password")
# 创建连接的实例
self.db_engine = create_engine(
'mssql+pymssql://' + self.user + ':' + self.password + '@' + self.host + ':' + self.port + '/' + self.name,
echo=False)
self.meta = MetaData()
Session = sessionmaker(bind=self.db_engine)
self.session = Session() # 实例化session
except Exception as ex:
self.isSuccess = False
print(ex)
'''
析构函数
This has the effect of fully closing all currently checked in database connections.
'''
def __del__(self):
try:
self.db_engine.dispose()
except Exception as ex:
print(ex)2.主函数调用数据库连接，并读取配置文件
import configparser
import os
from SqlServer_Obj import SqlServer_Obj
from SqlServer_Read import SqlServer_Read
from CsvOperation import CsvOperation
from Logger import Logger
import sys
if __name__ == '__main__':
# 初始化文件读取器
Logger.logger.info('程序启动')
config_init = configparser.ConfigParser()
path = os.path.split(os.path.realpath(__file__))[0]
Logger.logger.info('配置文件路径为：' + path)
config_init.read(os.path.join(path, 'config/config.ini'), encoding='utf-8-sig')
# 连接SqlServer数据库
sqlserver_wande = SqlServer_Obj(config_init)
# 检查数据库对象是否正常
if sqlserver_wande.isSuccess == False:
Logger.logger.critical('数据库连接超时，请检查数据库连接')
sys.exit()
else:
Logger.logger.info('数据库连接成功。')3. python读取sqlServer数据库数据，SqlServer_Reader.py
from sqlalchemy import and_
from sqlalchemy.schema import *
class ExchangeRate:
TRADINGDAY = ""
FROMCURRENCYID = ""
TOCURRENCYID = ""
BUYPRICE = 0.0
SELLPRICE = 0.0
MIDDLEPRICE = 0.0
class SqlServer_Read():
def __init__(self, sqlserver_wande, config_init):
wande_db_engine = sqlserver_wande.db_engine
wande_meta = sqlserver_wande.meta
self.wande_conn = wande_db_engine.connect()
self.wande_session = sqlserver_wande.session
self.rateID = config_init.get("RateID", "list")
def SqlServer_Select(self):
kkk = self.rateID.replace(",", "','")
# 'USDCNH','USDCNH'
# self.wande_sql = "SELECT top 1 * FROM TB_OBJECT_3567 WHERE F1_3567 IN (self.rateID) order by F2_3567 desc "
self.wande_sql = "SELECT * FROM TB_OBJECT_3567 WHERE F1_3567 IN ('" + kkk + "') and F2_3567=(select max(F2_3567) from TB_OBJECT_3567) order by F1_3567 desc "
result = self.wande_session.execute(self.wande_sql).fetchall()
dictRates = {}
if result is not None and len(result) != 0:
for row in result:
rate = ExchangeRate()
rate.TRADINGDAY = row['F2_3567']
rate.FROMCURRENCYID = row['F1_3567'][:3]
rate.TOCURRENCYID = row['F1_3567'][-3:]
rate.BUYPRICE = row['F6_3567']
rate.SELLPRICE = row['F6_3567']
rate.MIDDLEPRICE = row['F6_3567']
dictRates[row['F1_3567']] = rate # {'USDCNH': <SqlServer_Read.ExchangeRate object at 0x0000027308B87320>}
# 转化成两条记录
sqlServer_list = []
for row in result:
line1='"'+rate.TRADINGDAY+'","'+rate.FROMCURRENCYID+'","'+rate.TOCURRENCYID+'",'+ str(round(rate.BUYPRICE, 5)) +','+ str(round(rate.SELLPRICE, 5))+','+str(round(rate.MIDDLEPRICE, 5))
line2='"'+rate.TRADINGDAY+'","'+rate.TOCURRENCYID+'","'+rate.FROMCURRENCYID+'",'+ str(round(1 / rate.BUYPRICE, 5))+','+ str(round(1 / rate.SELLPRICE, 5))+','+ str(round(1 / rate.MIDDLEPRICE, 5))
#row_list1 = ['"'+rate.TRADINGDAY+'"', '"'+rate.FROMCURRENCYID+'"', '"'+rate.TOCURRENCYID+'"', round(rate.BUYPRICE, 5),
# round(rate.SELLPRICE, 5), round(rate.MIDDLEPRICE, 5)]
#row_list2 = [rate.TRADINGDAY, rate.TOCURRENCYID, rate.FROMCURRENCYID, round(1 / rate.BUYPRICE, 5), round(1 / rate.SELLPRICE, 5),round(1 / rate.MIDDLEPRICE, 5)]
#sqlServer_list.append(row_list1)
#sqlServer_list.append(row_list2)
sqlServer_list.append(line1)
sqlServer_list.append(line2)
# sqlServer_list=[row_list1,row_list2]
return sqlServer_listpython连接Oracle数据库
1.python sqlalchemy 建立Oracle连接
from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import sessionmaker
class Oracle_Obj():
'''
构造函数
'''
def __init__(self, config_init, db_name):
self.isSuccess = True
try:
self.db_name = db_name
self.host = config_init.get(db_name, "host")
self.port = config_init.get(db_name, "port")
self.name = config_init.get(db_name, "name")
self.user = config_init.get(db_name, "user")
self.password = config_init.get(db_name, "password")
self.db_engine = create_engine(
'oracle://' + self.user + ':' + self.password + '@' + self.host + ':' + self.port + '/' + self.name,
echo=False)
self.meta = MetaData()
Session = sessionmaker(bind=self.db_engine)
self.session = Session()
except Exception as ex:
self.isSuccess = False
print(ex)
'''
析构函数
This has the effect of fully closing all currently checked in database connections.
'''
def __del__(self):
try:
self.db_engine.dispose()
except Exception as ex:
print(ex)常用格式
# '数据库类型+数据库驱动名称://用户名:口令@机器地址:端口号/数据库名'
engine = create_engine('sqlite:///:memory:', echo=True) # sqlite内存
engine = create_engine('sqlite:///./cnblogblog.db',echo=True) # sqlite文件
engine = create_engine("mysql+pymysql://username:password@hostname:port/dbname",echo=True) # mysql+pymysql
engine = create_engine('mssql+pymssql://username:password@hostname:port/dbname',echo=True) # mssql+pymssql
engine = create_engine('postgresql://scott:tiger@hostname:5432/dbname') # postgresql示例
engine = create_engine('oracle://scott:tiger@hostname:1521/sidname') # oracle
engine = create_engine('oracle+cx_oracle://scott:tiger@tnsname') #pdb就可以用tns连接
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
