通过Python从MSSQL获取数据

问题描述：

请帮助我通过python编程语言的帮助来从MSSQL Server获取数据。我需要简单的实现，如使用select命令获取所有表数据并使用过程来操作数据。还有哪些模块将用于构建python和MSSQL之间的通信。通过Python从MSSQL获取数据

您是否试过使用[pymssql]（http://pymssql.org/en/stable/）？ –

欢迎！把你的代码示例，你想要的。并访问链接** [如何问]（http://stackoverflow.com/help/mcve）** –

答

import pymssql 
conn = pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase') 
cur = conn.cursor() 
cur.execute('CREATE TABLE persons(id INT, name VARCHAR(100))') 
cur.executemany("INSERT INTO persons VALUES(%d, %s)", \ 
    [ (1, 'John Doe'), (2, 'Jane Doe') ]) 
conn.commit() # you must call commit() to persist your data if you don't set autocommit to True 

cur.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe') 
row = cur.fetchone() 
while row: 
    print "ID=%d, Name=%s" % (row[0], row[1]) 
    row = cur.fetchone() 

# if you call execute() with one argument, you can use % sign as usual 
# (it loses its special meaning). 
cur.execute("SELECT * FROM persons WHERE salesrep LIKE 'J%'") 

conn.close()

如果它解决了你的查询然后upvote并接受这个答案 –

答

我用这个代码，它的工作完美。

from os import getenv 
import pymssql 

server = getenv("PYMSSQL_TEST_SERVER") 
user = getenv("PYMSSQL_TEST_USERNAME") 
password = getenv("PYMSSQL_TEST_PASSWORD") 

conn = pymssql.connect(server, user, password, "tempdb") 
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.')]) 
# you must call commit() to persist your data if you don't set autocommit to True 
conn.commit() 

cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe') 
row = cursor.fetchone() 
while row: 
    print("ID=%d, Name=%s" % (row[0], row[1])) 
    row = cursor.fetchone() 

conn.close()

通过Python从MSSQL获取数据

相关推荐