Utilisation d'un dictateur Python pour une instruction SQL INSERT

Question

J'essaie d'utiliser un dict pour faire un SQL INSERT . La logique serait essentiellement la suivante :

INSERT INTO table (dict.keys()) VALUES dict.values()

Cependant, j'ai du mal à trouver la syntaxe/le flux correct pour le faire. Voici ce que j'ai actuellement :

# data = {...}
sorted_column_headers_list = []
sorted_column_values_list = []
for k, v in data.items():
    sorted_column_headers_list.append(k)
    sorted_column_values_list.append(v)
sorted_column_headers_string = ', '.join(sorted_column_headers_list)
sorted_column_values_string = ', '.join(sorted_column_values_list)

cursor.execute("""INSERT INTO title (%s) 
            VALUES (%s)""", 
            (sorted_column_headers_string, sorted_column_values_string))

J'obtiens alors une exception SQL (je pense qu'elle est liée au fait que des virgules sont également incluses dans certaines de mes valeurs). Quelle serait la manière correcte de procéder ?

Answer 1

Je pense que le commentaire sur l'utilisation avec MySQL n'est pas tout à fait complet. MySQLdb ne fait pas de substitution de paramètres dans les colonnes, seulement les valeurs (IIUC) - donc peut-être plus comme

placeholders = ', '.join(['%s'] * len(myDict))
columns = ', '.join(myDict.keys())
sql = "INSERT INTO %s ( %s ) VALUES ( %s )" % (table, columns, placeholders)
# valid in Python 2
cursor.execute(sql, myDict.values())
# valid in Python 3
cursor.execute(sql, list(myDict.values()))

Vous n'obtenez pas d'échappement sur les colonnes cependant, donc vous pourriez vouloir les vérifier d'abord.....

Voir http://mail.python.org/pipermail/tutor/2010-December/080701.html pour une solution plus complète

Answer 2

Vous voulez ajouter des paramètres placeholders à la requête. Ceci pourrait vous donner ce dont vous avez besoin :

qmarks = ', '.join('?' * len(myDict))
qry = "Insert Into Table (%s) Values (%s)" % (qmarks, qmarks)
cursor.execute(qry, myDict.keys() + myDict.values())

Answer 3

Toujours de bonnes réponses ici, mais en Python 3, vous devriez écrire ce qui suit :

placeholder = ", ".join(["%s"] * len(dict))
stmt = "insert into `{table}` ({columns}) values ({values});".format(table=table_name, columns=",".join(dict.keys()), values=placeholder)
cur.execute(stmt, list(dict.values()))

N'oubliez pas de convertir dict.values() à une liste car dans Python 3, dict.values() renvoie une vue, pas une liste.

Aussi, ne PAS verser le dict.values() en stmt parce qu'il arrache une citation d'une chaîne de caractères en join ce qui a provoqué une erreur MySQL lors de son insertion. Vous devez donc toujours le mettre dans cur.execute() de manière dynamique.

Answer 4

J'arrive un peu tard dans la soirée, mais il existe une autre méthode que je préfère puisque mes données sont généralement déjà sous forme de dictée. Si vous listez les variables à lier sous la forme de %(columnName)s, vous pouvez utiliser un dictionnaire pour les lier à l'exécution. Cela résout partiellement le problème de l'ordre des colonnes puisque les variables sont liées par leur nom. Je dis partiellement parce que vous devez toujours vous assurer que la partie colonnes et valeurs de l'insertion est correctement mappée ; mais le dictionnaire lui-même peut être dans n'importe quel ordre (puisque les dicts sont en quelque sorte non ordonnés de toute façon).

Il existe probablement un moyen plus pythique de réaliser tout cela, mais le fait de rassembler les noms des colonnes dans une liste et de travailler à partir de celle-ci nous permet d'avoir un ordre statique pour construire les clauses columns & values.

data_dict = {'col1': 'value 1', 'col2': 'value 2', 'col3': 'value 3'}
columns = data_dict.keys()
cols_comma_separated = ', '.join(columns)
binds_comma_separated = ', '.join(['%(' + item + ')s' for item in columns])

sql = f'INSERT INTO yourtable ({cols_comma_separated}) VALUES ({binds_comma_separated})'

cur.execute(sql, data_dict)

La question de savoir si c'est une bonne idée ou non de construire dynamiquement votre clause columns & values de cette manière est un sujet pour un fil de discussion sur les injections SQL.

Answer 5

J'ai essayé la solution de @furicle mais tout est toujours entré sous forme de chaîne de caractères - si votre dict est mixte, cela peut ne pas fonctionner comme vous le souhaitez. J'ai eu un problème similaire et voici ce que j'ai trouvé - ce n'est qu'un constructeur de requêtes et vous pourriez l'utiliser (avec des modifications) pour travailler avec n'importe quelle base de données de votre choix. Jetez un coup d'oeil !

def ins_query_maker(tablename, rowdict):
    keys = tuple(rowdict)
    dictsize = len(rowdict)
    sql = ''
    for i in range(dictsize) :
        if(type(rowdict[keys[i]]).__name__ == 'str'):
            sql += '\'' + str(rowdict[keys[i]]) + '\''
        else:
            sql += str(rowdict[keys[i]])
        if(i< dictsize-1):
            sql += ', '
    query = "insert into " + str(tablename) + " " + str(keys) + " values (" + sql + ")"
    print(query) # for demo purposes we do this
    return(query) #in real code we do this

Ce système est rudimentaire et doit encore faire l'objet de contrôles d'intégrité, etc. mais il fonctionne comme prévu. pour un dict :

tab = {'idnumber': 1, 'fname': 'some', 'lname': 'dude', 'dob': '15/08/1947', 'mobile': 5550000914, 'age' : 70.4}

En exécutant la requête, j'obtiens le résultat suivant

résultats de la requête générée par la suite