跳转至

MSSQL

一、概述

AILab-PDBC (Python DataBase Connectivity)是数智教育发展(山东)有限公司 AI Lab 100 团队开发的高效、灵活的数据接口(API)

ailab100.pdbc.sql类用于用于关系型数据库管理系统（RDBMS）的读写,支持MySQL 、微软SQL SERVER、SQLite、ORACLE、MariaDB 、PostgreSQL等主流结构化数据库。

本章介绍如何使用ailab100.pdbc.sql.Mysql 连接 MSSQL 数据库进行读写和下载，以及数据库类型和Pandas类型之间的转换。

Microsoft SQL Server (MSSQL)是由微软开发的数据库管理系统，是Web上最流行的用于存储数据的数据库，它已广泛用于电子商务、银行、保险、电力等与数据库有关的行业。

二、API 接口说明

(一) DBConnector 基类

(二) MSSQL 类

三、应用案例及技术性能

(一) MSSQL应用案例

(1) 从MSSQL数据库中读取public_rent_price_forecast_data表中的数据，将符合houseFloor="低" and totalFloor=2条件的记录输出至polars.DataFrame。

from ailab100.alcedo_pdbc.sql import MSSQL

mssql_config = {'USERNAME':'sa',
                'PASSWORD':'sa',
                'HOST':'192.168.110.52',
                'PORT':'1433',
                "DATABASE":''
                }

db_mssql = MSSQL(mssql_config)

# 读取数据
df = db_mssql.read_as_datafra(table_name="public_rent_price_forecast_data",
                              params={"houseFloor":"低","totalFloor":2},
                              return_type='polars'
                              )

print(df)

(2) 从MSSQL数据库中读取public_rent_price_forecast_data表中的数据，将符合houseFloor="低" and totalFloor=2条件的记录输出并下载为test.csv文件。

# 读取数据下载文件
db_mssql.download_as_file(filename="tests_yield_curve.csv",
                          table_name="public_rent_price_forecast_data",
                          params={"houseFloor":"低","totalFloor":2},)

(3) 将pandas.DataFrame的数据写入到MSSQL的 ailab数据库中的examples数据表。

# 写入数据
import pandas as pd

data = {'Column1':[1,2,3,4],'Column2':['A','B','C','D']}
data_df = pd.DataFrame(data)

db_mssql.write_dataframe(df=data_df,table_name='examples',database='ailab')

(二) 数据类型映射

SQL Server - Pandas 数据类型映射

SQLServer Type Pandas Type Comment
TINYINT int64, Int64(nullable)
SMALLINT int64, Int64(nullable)
INT int64, Int64(nullable)
BIGINT int64, Int64(nullable)
FLOAT float64
NUMERIC float64
DECIMAL float64 cannot support precision larger than 28
BIT bool, boolean(nullable)
VARCHAR object
CHAR object
TEXT object
NVARCHAR object
NCHAR object
NTEXT object
VARBINARY object
BINARY object
IMAGE object
DATETIME datetime64[ns]
DATETIME2 datetime64[ns]
SMALLDATETIME datetime64[ns]
DATE datetime64[ns]
DATETIMEOFFSET datetime64[ns]
TIME object
UNIQUEIDENTIFIER object

(三) 性能测试

实验室通过比较 Python 中的不同解决方案，采用 4 个线程并行处理，读取 SQL Server 中一个 10,981,106 行的数据表 （1,092,616,192 字节，1.02 GB） 加载到 DataFrame 中，实验结果如下：

(1) 响应时间 (越短越好)

pg-time.png

(2) 内存消耗 (越低越好)

pg-time.png

总之，AILab PDBC 使用的内存减少了 1/3 ，与 Pandas 相比响应时间减少了近 1 倍（与 Polars 相比，响应时间相差无几 ）。