Python使用pymssql連接SQL?SEVER數(shù)據(jù)庫全流程

更新時間：2023年12月29日 12:50:55 作者：幻影123！

SQL Server是微軟推出的重量級的數(shù)據(jù)庫,目前有多個版本,如2000、2008、2012等,下面這篇文章主要給大家介紹了關于Python使用pymssql連接SQL?SEVER數(shù)據(jù)庫的相關資料,需要的朋友可以參考下

背景介紹

在數(shù)據(jù)分析領域，經(jīng)常需要從數(shù)據(jù)庫中獲取數(shù)據(jù)進行分析和處理。而SQL Server是一種常用的關系型數(shù)據(jù)庫管理系統(tǒng)，因此學習如何使用Python連接SQL Server數(shù)據(jù)庫并獲取數(shù)據(jù)是非常有用的。

以下是Python使用pymssql連接SQL Server數(shù)據(jù)庫的全流程：

安裝pymssql庫
本地賬號設置
腳本連接
數(shù)據(jù)導入函數(shù)實現(xiàn)

一、安裝pymssql

pymssql是Python連接SQL Server數(shù)據(jù)庫的一個庫，可以通過pip命令進行安裝。在命令行中輸入以下命令即可安裝：

pip install pymssql

二、本地賬號設置

1、設置sa賬戶的登錄密碼

在SQL Server Management Studio中，展開“安全性”文件夾，右鍵單擊“登錄名”并選擇“新建登錄名”。在彈出的對話框中，輸入登錄名“sa”，選擇“SQL Server身份驗證”，設置密碼并確保“登錄”選項卡下“默認數(shù)據(jù)庫”為目標數(shù)據(jù)庫。最后，在“狀態(tài)”選項卡下選擇“啟用”并確保“允許連接”復選框被選中。

2、開啟雙重驗證

在SQL Server Management Studio中，右鍵單擊SQL Server實例名稱并選擇“屬性”。在彈出的對話框中，選擇“安全性”選項卡，將“身份驗證”設置為“SQL Server和Windows身份驗證模式”，并選擇“應用”以保存更改。

3、開啟TCP/IP本地服務

在SQL Server Configuration Manager中，展開“SQL Server網(wǎng)絡配置”文件夾，右鍵單擊“協(xié)議”文件夾并選擇“新建協(xié)議”。選擇“TCP/IP”協(xié)議，右鍵單擊“TCP/IP”協(xié)議并選擇“屬性”。在彈出的對話框中，將“啟用”設置為“是”，并確保“IP地址”選項卡下的“TCP動態(tài)端口”為空。然后，切換到“IPALL”選項卡，并將“TCP端口”設置為1433（或其他您想要的端口號）。保存更改并重啟SQL Server服務。

完成以上步驟后，即可使用Python連接本地SQL Server數(shù)據(jù)庫。

三、腳本連接

做好以上的準備工作，用測試腳本連接（如果連接出錯——b’DB-Lib error message 20002，試下在connect函數(shù)參數(shù)最后面加上tds_version=“7.0” 或者其他值）。

import pymssql

server = '(local)'
database = '訂單信息'
username = 'sa'
password = '123456'
# 連接到數(shù)據(jù)庫
conn = pymssql.connect(server=server, user=username, password=password, database=database,tds_version="7.0")#最后一個參數(shù)不是必須的
# 創(chuàng)建游標對象
cursor = conn.cursor()
print('連接成功！')
# 關閉數(shù)據(jù)庫連接
conn.close()

四、數(shù)據(jù)導入函數(shù)

我們成功連接了數(shù)據(jù)庫以后，可以很方便的導出導入數(shù)據(jù)，以下是一個將數(shù)據(jù)導入數(shù)據(jù)庫的函數(shù)實現(xiàn)

#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'):
    # 連接到數(shù)據(jù)庫
    conn = pymssql.connect(server=server, user=username, password=password, database=database)
    # 創(chuàng)建游標對象
    cursor = conn.cursor()
    # 獲取 DataFrame 對象的字段名和數(shù)據(jù)類型
    columns = list(df.columns)
    dtypes = df.dtypes
    if mode=='Overlay':
        try:
        #覆蓋模式
            cursor.execute("DROP TABLE {}".format(table_name))
        except:
            print('表不存在，直接創(chuàng)建……')
        # 構(gòu)造創(chuàng)建表的 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 轉(zhuǎn)換成元組列表
    data = [tuple(row) for row in df.fillna(0).values.tolist()]
    # 構(gòu)造插入數(shù)據(jù)的 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)) + ');'

    # 將數(shù)據(jù)插入到數(shù)據(jù)庫
    cursor.executemany(insert_sql, data)
    # 提交事務
    conn.commit()
    # 關閉數(shù)據(jù)庫連接
    conn.close()
   # 數(shù)據(jù)庫連接參數(shù)