Python连接SQLserver数据库

前进四的状态出发!

---

前言

学习Python 连接 数据库

解决问题:
1:如何连接数据库
2:如何进行简单查询
3:如何将查询中的中文乱码给修复
4:如何进行插入,更新,删除

---

一、如何连接数据库？

#pip install pymssql 先安装

import pymssql #导入模块

connect = pymssql.connect('(local)','sa','123456','School')  
#connect = pymssql.connect('服务器名称', '用户名', '密码', '库名')  # 建立连接
if connect:
    print("连接成功")
#输出
连接成功
1
2
3
4
5
6
7
8
9
10

二、如何进行简单查询

cursor = connect.cursor()#创建一个游标对象python里的sql语句都要通过cursor来执行
sql="select * from Student" #一个简单的sql查询语句
cursor.execute(sql)#执行sql语句
row = cursor.fetchone() #用fetchone 获取游标的数据,如果是首次就定位首行
while row:  # 循环读取所有结果
 	print(row)
    row = cursor.fetchone()#取下一个内容
cursor.close()
connect.close()
#结果出现乱码,下面我们解决
<pymssql._pymssql.Connection object at 0x0000025DBDEE9F00>
连接成功
(2021402075, 'ÏôÑ×', 'ÄÐ', datetime.datetime(1900, 2, 7, 0, 0), 'MA')
(2021402076, 'ÏôÑ×', 'ÄÐ', datetime.datetime(2003, 3, 18, 0, 0), 'CS')
(2021402077, '´ºÒ°Ó£', 'Å®', datetime.datetime(2009, 4, 20, 0, 0), 'MA')
(2021402078, '´ºÈÕÒ°ñ·', 'Å®', datetime.datetime(2003, 6, 20, 0, 0), 'CS')
(2021402079, 'ÈÕÏò³ûÌï', 'Å®', datetime.datetime(2006, 7, 7, 0, 0), 'MA')
(2021402080, 'ÄÈÃÀ', 'Å®', datetime.datetime(2003, 6, 6, 0, 0), 'CS')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

五 如何将查询中的中文乱码给修复

方法一

主要在pymssql.connect 中添加charset = ‘cp936’

原因:因为字符串字段类型为：varchar，造成读取数据乱码。

import pymssql #导入模块
connect = pymssql.connect('(local)','sa','123456','School',charset='cp936')  #connect = pymssql.connect('服务器名称', '用户名', '密码', '库名')  # 建立连接
if connect:
    print(connect)
    print("连接成功")
cursor = connect.cursor()
sql = "select * from Student"
cursor.execute(sql)
row = cursor.fetchone()
while row:
    print(row)
    row = cursor.fetchone()
cursor.close()
connect.close()
#结果
<pymssql._pymssql.Connection object at 0x00000115E49A9FC0>
连接成功
(2021402075, '萧炎', '男', datetime.datetime(1900, 2, 7, 0, 0), 'MA')
(2021402076, '萧炎', '男', datetime.datetime(2003, 3, 18, 0, 0), 'CS')
(2021402077, '春野樱', '女', datetime.datetime(2009, 4, 20, 0, 0), 'MA')
(2021402078, '春日野穹', '女', datetime.datetime(2003, 6, 20, 0, 0), 'CS')
(2021402079, '日向雏田', '女', datetime.datetime(2006, 7, 7, 0, 0), 'MA')
(2021402080, '娜美', '女', datetime.datetime(2003, 6, 6, 0, 0), 'CS')
#成功完美的查询
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

方法一的缺点:
如果SQL语句中含有中文，就会得不到数据，如sql中有：where table_colum = ‘魏本明’；即便将sql语句:sql.encode(‘cp936’)，也不行！！

方法二

更通用全部

直接用sql语句将varchar转为nvarchar类型，不再需要指定charset了，就没问题了（注意转换列名称起别名），直接写sql语句如下

select convert(nvarchar(50),table_colum) 
as 'nvarchar_colum' from mytable where convert(nvarchar(50),table_colum) = '魏本明'
就是将需要转换的列(如,varchar),转换为nvarchar,也可以完成
1
2
3

例如:

import pymssql #导入模块
connect = pymssql.connect('(local)','sa','123456','School')  #connect = pymssql.connect('服务器名称', '用户名', '密码', '库名')  # 建立连接
if connect:
    print(connect)
    print("连接成功")

cursor = connect.cursor()#创建一个游标对象python里的sql语句都要通过cursor来执行
sql="select Ssno,convert(nvarchar(50),Snam) as 'a',convert(nvarchar(50),Ssex) as 'b' from Student"
cursor.execute(sql)#执行语句
row = cursor.fetchone()#用fetchone 获取游标的数据,如果是首次就定位首行
print(row)
while row:  # 循环读取所有结果
    print("id=%s,Name=%s，Sex=%s" % (row[0], row[1], row[2]))  # 输出结果
    row = cursor.fetchone()#取下一个内容
cursor.close()
connect.close()
结果:
<pymssql._pymssql.Connection object at 0x0000019C35BBC0C0>
连接成功
(2021402075, '萧炎', '男', datetime.datetime(1900, 2, 7, 0, 0), 'MA')
(2021402076, '萧炎', '男', datetime.datetime(2003, 3, 18, 0, 0), 'CS')
(2021402077, '春野樱', '女', datetime.datetime(2009, 4, 20, 0, 0), 'MA')
(2021402078, '春日野穹', '女', datetime.datetime(2003, 6, 20, 0, 0), 'CS')
(2021402079, '日向雏田', '女', datetime.datetime(2006, 7, 7, 0, 0), 'MA')
(2021402080, '娜美', '女', datetime.datetime(2003, 6, 6, 0, 0), 'CS')
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

四 如何进行插入,更新,删除

进行更新数据的操作都要用到connect.commit()函数
update,delete,insert等修改表中数据的需要commit

commit字面意思就是 投入
然后更改表的结构就不用了,因为他们内置了commit
create,drop,alter等修改表结构的,就不需要commit,因为内部隐藏了commit

不行的时候加上conn.autocommit(True)
推荐先在SQL中查看语句有没有语法错误在放回来运行,能解决你50%以上的报错

总结

我下面进行一个小项目全部都在python中完成
目标:
1:创建一个数据库
2:创建一个表
3:为这个表插入数据
4:更新插入的数据
5:查询前三行数据
6:插入一列属性

import  pymssql
# 1:创建一个数据库
conn = pymssql.connect('.','sa','123456')#单纯连接数据库
if conn:
    print("连接成功")
cursor = conn.cursor() #创建执行语句
conn.autocommit(True) #创建库的核心!!!
sql_DATA ="""
CREATE DATABASE PY_DATA
ON   PRIMARY
 (NAME = 'PY_DATA',
FILENAME = 'D:\DATA\PY_DATA.MDF' ,
SIZE = 5MB,
MAXSIZE = 20MB,
FILEGROWTH = 20%)
LOG ON
(NAME ='PY_DATA_LOG',
FILENAME = 'D:\DATA\PY_DATA_LOG. LDF',
SIZE = 5MB,
MAXSIZE = 10MB,
FILEGROWTH = 2MB)
"""
cursor.execute(sql_DATA)
cursor.close()
conn.autocommit(False)
conn.close()
----------------------------------------------------------------------------
#2:创建一个表
import pymssql
##连接
conn = pymssql.connect('.','sa','123456','School')
if conn:
    print("连接成功")

##操作
cursor = conn.cursor()
conn.autocommit(True)#是修改表的结构都要有吗?
sql_TABLE = """
Create Table Student_3(
ID int primary key,
Name varchar(50),
Age int)
"""
cursor.execute(sql_TABLE)
conn.autocommit(False)
conn.close()
----------------------------------------------------------------------------

3:为这个表插入数据
import  pymssql
conn = pymssql.connect('.','sa','123456','PY_DATA')
if conn:
    print("True")

cursor = conn.cursor()
a = "松仁、秉峰、泳纪海奉、威剑、颂和、祥益、腾恩、柏铄、孟深、忠庄、轩哲、铠鑫、仕伦、儒亿、积进信钦、贤元、程基、安泉、树昌、祝斌、一科、游湖、普济、中坚"
a = a.split("、")
for i in range(len(a)):
    sql_insert = f"insert into Student Values({i},'{a[i]}',18)"
    print(sql_insert)
    cursor.execute(sql_insert)
    conn.commit()
cursor.close()
conn.close()
----------------------------------------------------------------------------

4:更新插入的数据
import pymssql
conn = pymssql.connect('.','sa','123456','PY_DATA')
if conn:
    print("True")

cursor = conn.cursor()
sql_updata = "update student set Name='消息' where ID = 12"
cursor.execute(sql_updata)
conn.commit()
cursor.close()
conn.close()


----------------------------------------------------------------------------
5:查询前三行数据
import pymssql
conn = pymssql.connect('.','sa','123456','PY_DATA',charset='cp936')
if conn:
    print("True")
cursor = conn.cursor()
sql_select = "Select Top 3 * From Student"
cursor.execute(sql_select)
row =1
while row:
    row = cursor.fetchone()
    print(row)
cursor.close()
conn.close()
----------------------------------------------------------------------------
6:插入一列属性
import pymssql
conn = pymssql.connect('.','sa','123456','School')
cursor = conn.cursor()
conn.autocommit(True)
sql_insertbase = "Alter table Student_3 add idname int Null"
cursor.execute(sql_insertbase)
cursor.close()
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
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106