Python连接SQLSEVER数据库全流程

谭良

背景介绍在数据分析领域，经常需要从数据库中获取数据进行分析和处理。而SQLServer是一种常用的关系型数据库管理系统，因此学习如何使用Python连接SQLServer数据库并获取数据是非常有用的。以下是Python使用pymssql连接SQLServer数据库的全流程：安装pymssql库本地账号设置脚本连接数据导入函数实现一、安装pymssqlpymssql是Python连接SQLServer数据库的一个库，可以通过pip命令进行安装。在命令行中输入以下命令即可安装：pipinstallpymssql1二、本地账号设置1、设置sa账户的登录密码在SQLServerManagementStudio中，展开“安全性”文件夹，右键单击“登录名”并选择“新建登录名”。在弹出的对话框中，输入登录名“sa”，选择“SQLServer身份验证”，设置密码并确保“登录”选项卡下“默认数据库”为目标数据库。最后，在“状态”选项卡下选择“启用”并确保“允许连接”复选框被选中。2、开启双重验证在SQLServerManagementStudio中，右键单击SQLServer实例名称并选择“属性”。在弹出的对话框中，选择“安全性”选项卡，将“身份验证”设置为“SQLServer和Windows身份验证模式”，并选择“应用”以保存更改。3、开启TCP/IP本地服务在SQLServerConfigurationManager中，展开“SQLServer网络配置”文件夹，右键单击“协议”文件夹并选择“新建协议”。选择“TCP/IP”协议，右键单击“TCP/IP”协议并选择“属性”。在弹出的对话框中，将“启用”设置为“是”，并确保“IP地址”选项卡下的“TCP动态端口”为空。然后，切换到“IPALL”选项卡，并将“TCP端口”设置为1433（或其他您想要的端口号）。保存更改并重启SQLServer服务。完成以上步骤后，即可使用Python连接本地SQLServer数据库。三、脚本连接做好以上的准备工作，用测试脚本连接（如果连接出错——b’DB-Liberrormessage20002，试下在connect函数参数最后面加上tds_version=“7.0”或者其他值）。importpymssqlserver='(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()12345678910111213四、数据导入函数我们成功连接了数据库以后，可以很方便的导出导入数据，以下是一个将数据导入数据库的函数实现#coding=utf-8importtimeimportpymssqlimportpandasaspddefinsert_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.dtypesifmode=='Overlay':try:#覆盖模式cursor.execute("DROPTABLE{}".format(table_name))except:print('表不存在，直接创建……')#构造创建表的SQL语句create_table_sql=f'CREATETABLE[{table_name}]('forcol_name,col_typeinzip(columns,dtypes):ifcol_type=='int64':create_table_sql+=f'[{col_name}]BIGINT,'elifcol_type=='float64':create_table_sql+=f'[{col_name}]FLOAT,'elifcol_type=='datetime64[ns]'or'时间'incol_nameor'日期'incol_name:create_table_sql+=f'[{col_name}]DATETIME2(0),'elifcol_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)forrowindf.fillna(0).values.tolist()]#构造插入数据的SQL语句insert_sql=f'INSERTINTO[{table_name}]('insert_sql+=','.join([f'[{col_name}]'forcol_nameincolumns])+')VALUES('insert_sql+=','.join(['%s']*len(columns))+');'#将数据插入到数据库cursor.executemany(insert_sql,data)#提交事务conn.commit()#关闭数据库连接conn.close()#数据库连接参数12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849以上是python连接SQLSEVER数据库的全部流程，如果你遇到任何问题，欢迎评论区留言~