珠海市住房和建设局网站,月嫂网站源码,游戏分销代理平台,acca少女网课视频简述
python连接微软的sql server数据库用的第三方模块叫做pymssql#xff08;document#xff1a;http://www.pymssql.org/en/stable/index.html#xff09;。在官方文档可以看到#xff0c;pymssql是基于_mssql模块做的封装#xff0c;是为了遵守python的DBAPI规范接口.…简述
python连接微软的sql server数据库用的第三方模块叫做pymssqldocumenthttp://www.pymssql.org/en/stable/index.html。在官方文档可以看到pymssql是基于_mssql模块做的封装是为了遵守python的DBAPI规范接口. 两者之间的关系如下图1.使用pymssql连接sql server数据库并实现数据库基本操作(官方api http://www.pymssql.org/en/stable/ref/pymssql.html )
1基本语法
importpymssql
server 187.32.43.13 # 连接服务器地址user root # 连接帐号password 1234 # 连接密码conn pymssql.connect(server, user, password, 连接默认数据库名称) #获取连接
cursorconn.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.)])#你必须调用 commit() 来保持你数据的提交如果你没有将自动提交设置为true
conn.commit()
# 查询数据
cursor.execute(SELECT * FROM persons WHERE salesrep%s, John Doe)
# 遍历数据存放到元组中 方式1
rowcursor.fetchone()whilerow:print(ID%d, Name%s % (row[0], row[1]))
rowcursor.fetchone()
# 遍历数据存放到元组中 方式2
for row in cursor:
print(row %r % (row,))
# 遍历数据存放到字典中
# cursor conn.cursor(as_dictTrue)
#
# cursor.execute(SELECT * FROM persons WHERE salesrep%s, John Doe)
# for row in cursor:
# print(ID%d, Name%s % (row[id], row[name]))
#
# conn.close()
# 关闭连接
conn.close()
# 注在任何时候在一个连接下一次正在执行的数据库操作只会出现一个cursor对象
2同时如果你可以使用另一种语法with 来避免手动关闭cursors和connection连接
importpymssql
server 187.32.43.13 #连接服务器地址
user root #连接帐号
password 1234 #连接密码
with pymssql.connect(server, user, password,你的连接默认数据库名称) as conn:
with conn.cursor(as_dictTrue) as cursor: #数据存放到字典中
cursor.execute(SELECT * FROM persons WHERE salesrep%s, John Doe)for row incursor:print(ID%d, Name%s % (row[id], row[name]))
3调用存储过程
with pymssql.connect(server, user, password, tempdb) as conn:
with conn.cursor(as_dictTrue) as cursor:
cursor.execute(CREATE PROCEDURE FindPerson
name VARCHAR(100)
AS BEGIN
SELECT * FROM persons WHERE name name
END)
cursor.callproc(FindPerson, (Jane Doe,))for row incursor:print(ID%d, Name%s % (row[id], row[name]))
2.使用_mssql连接sql server数据库并实现操作官方api http://www.pymssql.org/en/stable/ref/_mssql.html
1基本语法
import_mssql#创建连接
conn _mssql.connect(serverSQL01, useruser, passwordpassword, \
databasemydatabase)
print(conn.timeout)
print(conn.login_timeout)#创建table
conn.execute_non_query(CREATE TABLE persons(id INT, name VARCHAR(100)))#insert数据
conn.execute_non_query(INSERT INTO persons VALUES(1, John Doe))
conn.execute_non_query(INSERT INTO persons VALUES(2, Jane Doe))#查询操作
conn.execute_query(SELECT * FROM persons WHERE salesrep%s, John Doe)for row inconn:print ID%d, Name%s % (row[id], row[name])#查询数量count()
numemployees conn.execute_scalar(SELECT COUNT(*) FROM employees)#查询一条数据
employeedata conn.execute_row(SELECT * FROM employees WHERE id%d, 13)#带参数查询的几个例子
conn.execute_query(SELECT * FROM empl WHERE id%d, 13)
conn.execute_query(SELECT * FROM empl WHERE name%s, John Doe)
conn.execute_query(SELECT * FROM empl WHERE id IN (%s), ((5, 6),))
conn.execute_query(SELECT * FROM empl WHERE name LIKE %s, J%)
conn.execute_query(SELECT * FROM empl WHERE name%(name)s AND city%(city)s, \
{name: John Doe, city: Nowhere} )
conn.execute_query(SELECT * FROM cust WHERE salesrep%s AND id IN (%s), \
(John Doe, (1, 2, 3)))
conn.execute_query(SELECT * FROM empl WHERE id IN (%s), (tuple(xrange(4)),))
conn.execute_query(SELECT * FROM empl WHERE id IN (%s), \
(tuple([3, 5, 7, 11]),))#关闭连接
conn.close()
pymssql托管在Github上https://github.com/pymssql
