pymssql_sql_server_connect_create_table_insert_query.py

1import pymssql
2
3# Replace these with your own server details
4conn = pymssql.connect(server='localhost', user='user', password='password', database='tempdb')
5cursor = conn.cursor()
6
7# Create a table
8cursor.execute("""
9IF OBJECT_ID('persons', 'U') IS NOT NULL
10    DROP TABLE persons
11CREATE TABLE persons (
12    id INT NOT NULL,
13    name VARCHAR(100),
14    salesrep VARCHAR(100),
15    PRIMARY KEY(id)
16)
17""")
18
19# Insert some data
20cursor.executemany(
21    "INSERT INTO persons VALUES (%d, %s, %s)",
22    [(1, 'John Doe', 'John Doe'),
23     (2, 'Jane Doe', 'Joe Dog'),
24     (3, 'Mike Black', 'John Doe')])
25
26# You must call commit() to persist your data if the connection is not in autocommit mode
27conn.commit()
28
29# Run a query
30cursor.execute('SELECT * FROM persons WHERE salesrep=%s', 'John Doe')
31row = cursor.fetchone()
32while row:
33    print("ID=%d, Name=%s" % (row[0], row[1]))
34    row = cursor.fetchone()
35
36conn.close()