Python连接SQL SEVER数据库全流程

背景介绍

在数据分析领域，经常需要从数据库中获取数据进行分析和处理。而SQL Server是一种常用的关系型数据库管理系统，因此学习如何使用Python连接SQL Server数据库并获取数据是非常有用的。

以下是Python使用pymssql连接SQL Server数据库的全流程：

• 安装pymssql库
• 本地账号设置
• 脚本连接
• 数据导入函数实现

一、安装pymssql

pymssql是Python连接SQL Server数据库的一个库，可以通过pip命令进行安装。在命令行中输入以下命令即可安装：

pip install pymssql

• 1

二、本地账号设置

1、设置sa账户的登录密码

在SQL Server Management Studio中，展开“安全性”文件夹，右键单击“登录名”并选择“新建登录名”。在弹出的对话框中，输入登录名“sa”，选择“SQL Server身份验证”，设置密码并确保“登录”选项卡下“默认数据库”为目标数据库。最后，在“状态”选项卡下选择“启用”并确保“允许连接”复选框被选中。

2、开启双重验证

在SQL Server Management Studio中，右键单击SQL Server实例名称并选择“属性”。在弹出的对话框中，选择“安全性”选项卡，将“身份验证”设置为“SQL Server和Windows身份验证模式”，并选择“应用”以保存更改。

3、开启TCP/IP本地服务

在SQL Server Configuration Manager中，展开“SQL Server网络配置”文件夹，右键单击“协议”文件夹并选择“新建协议”。选择“TCP/IP”协议，右键单击“TCP/IP”协议并选择“属性”。在弹出的对话框中，将“启用”设置为“是”，并确保“IP地址”选项卡下的“TCP动态端口”为空。然后，切换到“IPALL”选项卡，并将“TCP端口”设置为1433（或其他您想要的端口号）。保存更改并重启SQL Server服务。

完成以上步骤后，即可使用Python连接本地SQL Server数据库。

三、脚本连接

做好以上的准备工作，用测试脚本连接（如果连接出错——b’DB-Lib error message 20002，试下在connect函数参数最后面加上tds_version=“7.0” 或者其他值）。

import pymssql server = '(local)' database = '订单信息' username = 'sa' password = '123456' # 连接到数据库 conn = pymssql.connect(server=server, user=username, password=password, database=database,tds_version="7.0")#最后一个参数不是必须的 # 创建游标对象 cursor = conn.cursor() print('连接成功！') # 关闭数据库连接 conn.close()

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13

四、数据导入函数

我们成功连接了数据库以后，可以很方便的导出导入数据，以下是一个将数据导入数据库的函数实现

#coding=utf-8 import time import pymssql import pandas as pd def insert_data_to_sql(df, server, database, username, password, table_name,mode='Overlay'): # 连接到数据库 conn = pymssql.connect(server=server, user=username, password=password, database=database) # 创建游标对象 cursor = conn.cursor() # 获取 DataFrame 对象的字段名和数据类型 columns = list(df.columns) dtypes = df.dtypes if mode=='Overlay': try: #覆盖模式 cursor.execute("DROP TABLE {}".format(table_name)) except: print('表不存在，直接创建……') # 构造创建表的 SQL 语句 create_table_sql = f'CREATE TABLE [{table_name}] (' for col_name, col_type in zip(columns, dtypes): if col_type == 'int64': create_table_sql += f'[{col_name}] BIGINT,' elif col_type == 'float64': create_table_sql += f'[{col_name}] FLOAT,' elif col_type== 'datetime64[ns]' or '时间' in col_name or '日期' in col_name: create_table_sql += f'[{col_name}] DATETIME2(0),' elif col_type == 'date': create_table_sql += f'[{col_name}] DATE,' else: create_table_sql += f'[{col_name}] NVARCHAR(max),' create_table_sql = create_table_sql.rstrip(',') + ');' cursor.execute(create_table_sql) # 将 DataFrame 转换成元组列表 data = [tuple(row) for row in df.fillna(0).values.tolist()] # 构造插入数据的 SQL 语句 insert_sql = f'INSERT INTO [{table_name}] (' insert_sql += ','.join([f'[{col_name}]' for col_name in columns]) + ') VALUES (' insert_sql += ','.join(['%s'] * len(columns)) + ');' # 将数据插入到数据库 cursor.executemany(insert_sql, data) # 提交事务 conn.commit() # 关闭数据库连接 conn.close() # 数据库连接参数

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49

以上是python连接SQL SEVER数据库的全部流程，如果你遇到任何问题，欢迎评论区留言~