python连接sqlserver_PYTHON连接SQLSERVER实现增删改查等基本操作

import pymssql

import config

server = server1

user = user1

password = password1

conn = pymssql.connect(server, user, password, database1) #获取连接

cursor = conn.cursor() # 获取光标

####创建表

cursor.execute("""

IF OBJECT_ID('persons', 'U') IS NOT NULL

DROP TABLE persons

CREATE TABLE persons (

id INT NOT NULL,

name VARCHAR(100),

salesrep VARCHAR(100),

PRIMARY KEY(id)

)

""")

####批量插入数据

cursor.executemany(

"INSERT INTO persons VALUES (%d, %s, %s)",

[(1, 'John Smith', 'John Doe'),

(2, 'Jane Doe', 'Joe Dog'),

(3, 'Mike T.', 'Sarah H.')])

conn.commit()

####删除数据

cursor.execute(

"delete from persons where id=2"

)

conn.commit()

# 查询数据并遍历，结果默认存放到元组之中，要存到字典，需修改cursor = conn.cursor(as_dict=True)

cursor.execute('SELECT * FROM persons WHERE id={0}'.format(3))

row = cursor.fetchone()

while row:

print(row)

row = cursor.fetchone()

####更改数据

cursor.execute(

"update persons set name ='haha' where id=1"

)

conn.commit()

####创建存储过程

cursor.execute("""

CREATE PROCEDURE FindPerson

@name VARCHAR(100)

AS BEGIN

SELECT * FROM persons WHERE name = @name

END

""")

#####调用存储过程

cursor.callproc('FindPerson', ('haha',))

for row in cursor:

print(row)

####执行含聚合函数的语句

cursor.execute('SELECT COUNT(*) FROM persons')

for row in cursor:

print(row[0])

conn.close()#手动关闭连接

#####用with实现自动关闭conn

with pymssql.connect("", "", "", "") as conn:

with conn.cursor(as_dict=True) as cursor: # 数据存放到字典中

cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')

for row in cursor:

print(row)