pymsql和mysqlClient 哪个常用 pymysql和sqlalchemy

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_list

python连接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连接