Skip to content

Instantly share code, notes, and snippets.

@msabramo

msabramo/crisp2.out

Last active Feb 26, 2019
Embed
What would you like to do?
pymssql: Demonstration that with as_dict=True you have to name result columns that come from functions like MIN/MAX
(py26.venv)marca@marca-mac2:~/dev/git-repos/pymssql$ python stuff/crisp2.py
SELECT MAX(x)
FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8)) AS foo(x, y)
rows1 = [{}]
SELECT MAX(x) AS [MAX(x)]
FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8)) AS foo(x, y)
rows2 = [{u'MAX(x)': 7}]
SELECT MAX(x)
FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8)) AS foo(x, y)
rows1 = [(7,)]
from os import getenv
import pymssql
database_host = getenv("PYMSSQL_TEST_SERVER")
database_user = getenv("PYMSSQL_TEST_USERNAME")
user_password = getenv("PYMSSQL_TEST_PASSWORD")
database_name = getenv("PYMSSQL_TEST_DATABASE")
conn = pymssql.connect(
host=database_host,
user=database_user,
password=user_password,
database=database_name,
as_dict=True)
cur = conn.cursor()
sql = """
SELECT MAX(x)
FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8)) AS foo(x, y)
"""
cur.execute(sql)
rows1 = cur.fetchall()
print(sql)
print("rows1 = %r" % rows1)
sql = """
SELECT MAX(x) AS [MAX(x)]
FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8)) AS foo(x, y)
"""
cur.execute(sql)
rows2 = cur.fetchall()
print(sql)
print("rows2 = %r" % rows2)
conn = pymssql.connect(
host=database_host,
user=database_user,
password=user_password,
database=database_name,
as_dict=False)
cur = conn.cursor()
sql = """
SELECT MAX(x)
FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8)) AS foo(x, y)
"""
cur.execute(sql)
rows1 = cur.fetchall()
print(sql)
print("rows1 = %r" % rows1)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment