用pymssql 实现 python 连接 sql server，local host连接失败问题，及常见的前置数据处理操作

本文链接：https://blog.csdn.net/SummerStoneS/article/details/91972239

建立连接

    import pymssql
    
    connect = pymssql.connect('(local)', '', '', 'db_name')
    if connect:
        print("successfully connect to db")
    else:
        print("fail to connect to db")
    cursor = connect.cursor(as_dict=True)

as_dict=True 存放到字典中，不然select取回的数没有列名查询

cursor.execute("select * from dbo.table_name")

cursor.fetchone()

cursor.fetchmany()

cursor.fetchall()

local host 本地连接失败问题：进入sql server 配置 enable TCP/IP

也可以直接用pandas连接

data = pd.read_sql("select * from dbo.table_name", connect)

数据插入

INSERT INTO dbo.table_name (col1_name, col2_name, col3_name) VALUES ("1", "a", "1.5")

格式化插入

sql_string  = "SELECT INTO dbo.table_name (col1_name, col2_name, col3_name) VALUES ("%d", "'%s'", "%f")"

record = [1, "a", 1.5]
sql = sql_string % tuple(tuple)
cursor.execute(sql)

格式化的内容参考 https://www.cnblogs.com/fat39/p/7159881.html

pandas的数据插入sql server

def insert_into_db(df, item_type_dict, db_table_name):
    item_names = list(item_type_dict.keys())
    item_types = list(item_type_dict.values())
    df_for_sql_pre = df[item_names]
    df_for_sql = change_quotation_in_string(df_for_sql_pre, [col for col in item_names if col in CONTAIN_QUOTATION_COLS])
    sql_string = "INSERT INTO " + db_table_name + "(" + ",".join(item_names) + ") VALUES (" + ",".join(item_types) + ")"

    for record in df_for_sql.values.tolist():
        try:
            sql = sql_string % tuple(record)
            sql = sql.replace("nan", "NULL")
            cursor.execute(sql)
        except Exception as e:
            print(sql_string % tuple(record))
            print(e)
    connect.commit()

type_people=OrderedDict({"department": "'%s'",
                              "year_month":"'%s'",
                              "total_fd": "%d",
                              "total_fi": "%d",
                              "last_update_time": "'%s'",
                              "stat_dt": "'%s'"
                              })

insert_into_db(people_df, type_people, db_table_name)

数据清空

cursor.execute("delete from dbo.table_name")

常见插入问题：

1. int,decimal格式的字段如果数据里有空值，格式化后会变成"nan"，无法插入到数据库中，可以在对格式化后的sql语句做replace,

sql = sql.replace("nan", "NULL")

2. 字符中出现单引号

如果要插入的字符串中有单引号，例如“Plant's people”

生成的sql 语句 VALUES（1，'Plant's people'），在插入时就会报错，需要转义

def change_quotation_in_string(df1, cols):
    df = df1.copy()
    for col in cols:
        if col in df.columns:
            df.loc[:, col] = df[col].apply(lambda x: x.replace("'","''"))
    return df

3.sql server数据库datetime和date类型

from datetime import datetime

datetime(2019,6,14) 用'%s'格式化后插入到datetime类型的字段

datetime(2019,6,14) .date() 用'%s'格式化后插入到date类型的字段

4. date字段的缺失值