pymssql操作SqlServer数据库——CONVERT()的妙用

在使用python操作SqlServer数据库时，遇到了一些小问题，在对SqlServer不熟的情况下，通过各种查资料解决了，本文就convert()做一些总结

1、CONVERT的妙用 之 解决SqlServer表中字段为varchar类型导致中文乱码的问题

例如我们现在有一张用户表user_info，字段设计如下 

user_info表设计

名	类型	大小	比例	不是null	键	注释
user_id	varchar	38		√	1	用户唯一标识
user_role_name	varchar	50				用户角色名称(存在中文)
create_time	datetime					创建时间
update_time	datetime					更新时间
is_del	int					是否已删除

然后通

 

在python脚本中，我们使用常见的普通sql语句获取此表中的信息

sql如下（非常普通，非常常见）

SELECT
    *
FROM
    user_info 
WHERE
    user_id = 'abcdefgh'   # 示例

 将sql补充进我们的脚本中：

# sql语句
SQL = """
    SELECT
        *
    FROM
        user_info 
    WHERE
        user_id = 'abcdefgh'   # 示例
"""

# 连接数据库
con = pymssql.connect(
        host="127.0.0.1",  # 示例
        port=8888,  # 示例
        user="username",  # 示例
        password="111111",  # 示例
        database="user",  # 示例
        charset="utf8"
    )
cur = con.cursor()
cur.execute(SQL)
result = cur.fetchall()

# 打印结果
print("result: ", result)

那么此时我们得到的结果是这样的：

 因为SqlServer中这个user_role_name字段类型为varchar，所以在应用时如果包含中文，便会乱码——那么如何解决这个问题呢

① 一劳永逸法：将表设计中此字段改为nvarchar类型，兼容中英文——但是在企业里，表结构可不是随便就能改的，所以此法只适用于初期开发时&&有权限的情况下。

② 查后编码法：将字段查询出来之后，使用代码操作将字段重新编码：

# sql语句
SQL = """
    SELECT
        *
    FROM
        user_info 
    WHERE
        user_id = 'abcdefgh'   # 示例
"""

# 连接数据库
con = pymssql.connect(
        host="127.0.0.1",  # 示例
        port=8888,  # 示例
        user="username",  # 示例
        password="111111",  # 示例
        database="user",  # 示例
        charset="utf8"
    )
cur = con.cursor()
cur.execute(SQL)
result = cur.fetchone()  # 只有一条数据，使用fetchone即可
user_role_name = result[1]
user_role_name = user_role_name.encode('latin-1').decode('gbk')  # 将目标字段进行编码处理

# 打印结果
print("result: ", result)
print("user_role_name: ", user_role_name)

结果如下图，编码处理前 此字段是为乱码；编码处理后，正常显示中文：

③ 即查即转法：即我们想主要介绍的方法——使用CONVERT()函数转换我们的字段user_role_name，修改代码中的sql语句如下：

SELECT  
    user_id, 
    CONVERT (VARCHAR(50), user_role_name),  # 将user_role_name转换为varchar类型查询
    create_time,
    update_time,
    is_del
FROM
    user_info 
WHERE
    user_id = 'abcdefgh'   # 示例

完整代码：

# sql语句
SQL = """
    SELECT  
        user_id, 
        CONVERT (VARCHAR(50), user_role_name),  # 将user_role_name转换为varchar类型查询
        create_time,
        update_time,
        is_del
    FROM
        user_info 
    WHERE
        user_id = 'abcdefgh'   # 示例
"""

# 连接数据库
con = pymssql.connect(
        host="127.0.0.1",  # 示例
        port=8888,  # 示例
        user="username",  # 示例
        password="111111",  # 示例
        database="user",  # 示例
        charset="utf8"
    )
cur = con.cursor()
cur.execute(SQL)
result = cur.fetchall()

# 打印结果
print("result: ", result)

结果显而易见：

此法与②均为代码层处理，相较之下，此法在sql语句中直接处理，代码更加整洁，个人比较喜欢这个方法

2、CONVERT的妙用 之 时间类型显示

仔细观察上一小节中的各种输出结果(截图)，可以看到，我们的时间类型字段，打印出来的结果是这样的：

datetime.datetime(2021, 9, 26, 22, 52, 50, 760000)

一点也不好看，直接拿这种结果去insert的话，又容易报错

所以我们的convert又派上了用场

当然一般CONVERT()就是用在日期类型上：

语法： 

CONVERT(data_type(length),data_to_be_converted,style)

 示例(style代表日期格式，可参考下表)：

CONVERT(varchar(50), create_time, 120)  

此处将create_time字段类型转换为varchar，style选择为120，即yyyy-mm-dd hh:mi:ss(24h)格式

Style ID	Style 格式
100 或者 0	mon dd yyyy hh:miAM （或者 PM）
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mm:ss
109 或者 9	mon dd yyyy hh:mi:ss:mmmAM（或者 PM）
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
113 或者 13	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
120 或者 20	yyyy-mm-dd hh:mi:ss(24h)
121 或者 21	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	yyyy-mm-ddThh:mm:ss.mmm（没有空格）
130	dd mon yyyy hh:mi:ss:mmmAM
131	dd/mm/yy hh:mi:ss:mmmAM

注：上边这个表格，随处可见

修改我们的脚本中的sql语句：

# sql语句
SQL = """
    SELECT  
        user_id, 
        CONVERT (VARCHAR(50), user_role_name),  # 将user_role_name转换为varchar类型查询
        CONVERT (VARCHAR (50), create_time, 120),
        CONVERT (VARCHAR (50), update_time, 21),
        is_del
    FROM
        user_info 
    WHERE
        user_id = 'abcdefgh'   # 示例
"""

# 连接数据库
con = pymssql.connect(
        host="127.0.0.1",  # 示例
        port=8888,  # 示例
        user="username",  # 示例
        password="111111",  # 示例
        database="user",  # 示例
        charset="utf8"
    )
cur = con.cursor()
cur.execute(SQL)
result = cur.fetchall()

# 打印结果
print("result: ", result)

运行结果如下：

 可见，输出的时间已经按照style进行了格式化