Sortie des résultats du curseur pyodbc sous forme de dictionnaire python

Question

Comment puis-je sérialiser la sortie du curseur de pyodbc (à partir de .fetchone , .fetchmany o .fetchall ) comme un dictionnaire Python ?

J'utilise bottlepy et j'ai besoin de retourner un dict pour qu'il puisse le retourner en JSON.

Answer 1

Si vous ne connaissez pas les colonnes à l'avance, utilisez Curseur.description pour construire une liste de noms de colonnes et zip avec chaque ligne pour produire une liste de dictionnaires. L'exemple suppose que la connexion et la requête sont construites :

>>> cursor = connection.cursor().execute(sql)
>>> columns = [column[0] for column in cursor.description]
>>> print(columns)
['name', 'create_date']
>>> results = []
>>> for row in cursor.fetchall():
...     results.append(dict(zip(columns, row)))
...
>>> print(results)
[{'create_date': datetime.datetime(2003, 4, 8, 9, 13, 36, 390000), 'name': u'master'},   
 {'create_date': datetime.datetime(2013, 1, 30, 12, 31, 40, 340000), 'name': u'tempdb'},
 {'create_date': datetime.datetime(2003, 4, 8, 9, 13, 36, 390000), 'name': u'model'},     
 {'create_date': datetime.datetime(2010, 4, 2, 17, 35, 8, 970000), 'name': u'msdb'}]

Answer 2

En utilisant le résultat de @Beargle avec bottlepy, j'ai pu créer cette requête très concise exposant le point final :

@route('/api/query/<query_str>')
def query(query_str):
    cursor.execute(query_str)
    return {'results':
            [dict(zip([column[0] for column in cursor.description], row))
             for row in cursor.fetchall()]}

Answer 3

Voici une version abrégée que vous pourrez peut-être utiliser

>>> cursor.select("<your SQL here>")
>>> single_row = dict(zip(zip(*cursor.description)[0], cursor.fetchone()))
>>> multiple_rows = [dict(zip(zip(*cursor.description)[0], row)) for row in cursor.fetchall()]

Comme vous le savez peut-être, lorsque vous ajoutez * à une liste, vous supprimez la liste, laissant les entrées individuelles de la liste comme paramètres de la fonction que vous appelez. En utilisant zip, nous sélectionnons les entrées 1 à n et les assemblons comme la fermeture éclair de votre pantalon.

donc en utilisant

zip(*[(a,1,2),(b,1,2)])
# interpreted by python as zip((a,1,2),(b,1,2))

vous obtenez

[('a', 'b'), (1, 1), (2, 2)]

Puisque la description est un tuple avec des tuples, où chaque tuple décrit l'en-tête et le type de données pour chaque colonne, vous pouvez extraire le premier de chaque tuple avec

>>> columns = zip(*cursor.description)[0]

équivalent à

>>> columns = [column[0] for column in cursor.description]

Answer 4

Pour les situations où le curseur n'est pas disponible - par exemple, lorsque les lignes ont été renvoyées par un appel de fonction ou une méthode interne, vous pouvez toujours créer une représentation sous forme de dictionnaire en utilisant row.cursor_description.

def row_to_dict(row):
    return dict(zip([t[0] for t in row.cursor_description], row))

Answer 5

En me basant principalement sur la réponse de @Torxed, j'ai créé un ensemble complet de fonctions généralisées pour trouver le schéma et les données dans un dictionnaire :

def schema_dict(cursor):
    cursor.execute("SELECT sys.objects.name, sys.columns.name FROM sys.objects INNER JOIN sys.columns ON sys.objects.object_id = sys.columns. object_id WHERE sys.objects.type = 'U';")
    schema = {}

    for it in cursor.fetchall():
        if it[0] not in schema:
            schema[it[0]]={'scheme':[]}
        else:
            schema[it[0]]['scheme'].append(it[1])

    return schema

def populate_dict(cursor, schema):
    for i in schema.keys():
        cursor.execute("select * from {table};".format(table=i))

        for row in cursor.fetchall():
            colindex = 0

            for col in schema[i]['scheme']:
                if not 'data' in schema[i]:
                    schema[i]['data']=[]

                schema[i]['data'].append(row[colindex])
                colindex += 1

    return schema

def database_to_dict():
    cursor = connect()
    schema = populate_dict(cursor, schema_dict(cursor))

N'hésitez pas à faire du code-golf pour réduire le nombre de lignes, mais en attendant, ça marche !

;)