Please can you help me to fetch data from MSSQL Server by help of python programming language. I need simple implementation like using select command to get all table data and use procedure to manipulate data. Also which module will be use for build communication between python and MSSQL.
2
-
Have you tried to use pymssql?Laur Ivan– Laur Ivan2017-04-26 05:16:48 +00:00Commented Apr 26, 2017 at 5:16
-
Welcome! put your code example that what you try for. and visit link How to AskZaheer Ul Hassan– Zaheer Ul Hassan2017-04-26 05:27:26 +00:00Commented Apr 26, 2017 at 5:27
Add a comment
|
2 Answers
I used this code and it worked perfectly.
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()
Comments
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()
1 Comment
nishant kumar
if it solved your query then upvote and accept this answer