J'utilise le package sql dans un notebook Jupyter et je comprends comment utiliser des variables dans ma requête :

client = "Disney"
queryid = %sql SELECT * FROM mytable WHERE name = :client

Ce que je ne comprends pas, c'est comment passer une liste à ma requête, comme:

clients = ["Disney", "Netflix", "Sky"]
queryid = %sql SELECT * FROM mytable WHERE name in (:clients)

Cela soulève une erreur qui indique que mon SQL est faux. Quelle est la manière de gérer les listes dans ce paramètre?

2
user299791 15 juil. 2017 à 01:35

2 réponses

Meilleure réponse

Avec un étui de démonstration pour sqlite3:

In [1]: import sqlite3
In [2]: conn = sqlite3.connect('example.db')
In [3]: c = conn.cursor()
In [4]: c.execute('''CREATE TABLE stocks
   ...:              (date text, trans text, symbol text, qty real, price real)''')
   ...: 
   ...: # Insert a row of data
   ...: c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.1
   ...: 4)")
   ...: 
   ...: # Save (commit) the changes
   ...: conn.commit()
   ...: 
In [5]: # Larger example that inserts many records at a time
   ...: purchases = [('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
   ...:              ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
   ...:              ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
   ...:             ]
   ...: c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)

Je peux récupérer des valeurs qui correspondent à plusieurs chaînes avec:

In [31]: c.execute('SELECT * FROM stocks WHERE symbol IN (?,?)',('IBM','RHAT'))
Out[31]: <sqlite3.Cursor at 0xaf703fa0>
In [32]: c.fetchall()
Out[32]: 
[('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14),
 ('2006-03-28', 'BUY', 'IBM', 1000.0, 45.0),
 ('2006-04-06', 'SELL', 'IBM', 500.0, 53.0)]

Ou avec la solution généralisée de Substitution de paramètre pour une clause SQLite "IN"

In [33]: alist=['RHAT','IBM']
In [34]: c.execute('SELECT * FROM stocks WHERE symbol IN (%s)' %
    ...:                            ','.join('?'*len(alist)), 
    ...:                            alist)
    ...:                            
Out[34]: <sqlite3.Cursor at 0xaf703fa0>
In [35]: c.fetchall()
Out[35]: 
[('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14),
 ('2006-03-28', 'BUY', 'IBM', 1000.0, 45.0),
 ('2006-04-06', 'SELL', 'IBM', 500.0, 53.0)]

c.execute('SELECT * FROM stocks WHERE symbol IN (:1,:2)',alist), et éventuellement d'autres formes.

Voir également:

Clause sqlite3 "IN"

Je suppose que les interfaces MYSQL et %sql se comportent de la même manière; mais je ne les ai pas installés.


Avec des citations correctes, les littéraux fonctionnent également (encore une fois sqlite3)

c.execute('SELECT * FROM stocks WHERE symbol IN ("IBM","RHAT")')

Ou

In [80]: 'SELECT * FROM stocks WHERE symbol IN (%s)'%','.join('"%s"'%x for x in alist)
Out[80]: 'SELECT * FROM stocks WHERE symbol IN ("RHAT","IBM")'
In [81]: c.execute(_)

Donc je suppose que:

%sql SELECT * FROM stocks WHERE symbol IN ("IBM","RHAT")

Fonctionnerait même si une forme de (:....) ne fonctionne pas.


J'ai installé %sql

In [5]: %%sql
   ...: sqlite:///example.db
   ...: 

Out[5]: 'Connected: None@example.db'
In [7]: %sql SELECT * from stocks
Done.
Out[7]: 
[('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14),
 ('2006-03-28', 'BUY', 'IBM', 1000.0, 45.0),
 ('2006-04-05', 'BUY', 'MSFT', 1000.0, 72.0),
 ('2006-04-06', 'SELL', 'IBM', 500.0, 53.0)]

In [9]: %sql SELECT * from stocks where symbol in ('IBM')
Done.
Out[9]: 
[('2006-03-28', 'BUY', 'IBM', 1000.0, 45.0),
 ('2006-04-06', 'SELL', 'IBM', 500.0, 53.0)]

In [10]: %sql SELECT * from stocks where symbol in ('IBM','RHAT')
Done.
Out[10]: 
[('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14),
 ('2006-03-28', 'BUY', 'IBM', 1000.0, 45.0),
 ('2006-04-06', 'SELL', 'IBM', 500.0, 53.0)]

L'approche de formatage de chaîne fonctionne:

In [11]: alist=['RHAT','IBM']
In [12]: cmd='SELECT * FROM stocks WHERE symbol IN (%s)'%','.join('"%s"'%x for x
    ...:  in alist)
In [13]: cmd
Out[13]: 'SELECT * FROM stocks WHERE symbol IN ("RHAT","IBM")'
In [14]: %sql $cmd
Done.
Out[14]: 
[('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14),
 ('2006-03-28', 'BUY', 'IBM', 1000.0, 45.0),
 ('2006-04-06', 'SELL', 'IBM', 500.0, 53.0)]

La syntaxe : n'est pas bien documentée. On ne sait pas qui le met en œuvre. ($ est la substitution de variable Ipython standard).

In [18]: sym='IBM'
In [19]: %sql SELECT * from stocks where symbol in (:sym)
Done.
Out[19]: 
[('2006-03-28', 'BUY', 'IBM', 1000.0, 45.0),
 ('2006-04-06', 'SELL', 'IBM', 500.0, 53.0)]

symbol in (:sym1,:sym2) fonctionne

Jusqu'à présent, je ne vois aucune preuve que %sql fonctionne avec la syntaxe d'espace réservé SQL classique.


Il semble que vous (?) Avez soumis et résolu un problème sur github, https: // github. com / catherinedevlin / ipython-sql / issues / 92

Adapter cette solution aux chaînes de guillemets:

In [74]: mystring = '({})'.format(','.join('"{}"'.format(e) for e in alist))
In [75]: mystring
Out[75]: '("RHAT","IBM")'
In [76]: %sql SELECT * from stocks where symbol in $mystring
Done.

En d'autres termes, utilisez l'injection ipython $ par opposition à la forme :.


En regardant le code source ipython-sql:

ipython-sql/blob/master/src/sql/run.py
def run(conn, sql, config, user_namespace):
    ...
    txt = sqlalchemy.sql.text(statement)
    result = conn.session.execute(txt, user_namespace)

Il semble que la syntaxe :name est un paramètre de liaison sqlalchemy, et est gérée avec sqlalchemy.sql.text et sqlalchemy.sql.bindparam

(http://docs.sqlalchemy.org /en/latest/orm/tutorial.html#orm-tutorial-literal-sql)

Cette erreur indique que chaque paramètre de liaison est traduit en un espace réservé ?, plus une entrée parameters correspondante:

In [96]: %sql SELECT * from stocks where symbol in :mystring
(sqlite3.OperationalError) near "?": syntax error [SQL: 'SELECT * from stocks where symbol in ?'] [parameters: ('("RHAT","IBM")',)]

Donc ma solution originale de générer IN (?,?,...) pour correspondre à la longueur de la liste est le bon SQL, même si cela ne fonctionne pas avec sqlalchemy et %sql.

1
hpaulj 19 juil. 2017 à 04:36

Note anecdotique sur l'application des tuples $ with python dans une requête SQL:

Il doit être utilisé dans une ligne% sql, il ne peut pas * être utilisé dans un bloc de code %% sql. Utilisez des barres obliques inverses de continuation de ligne pour la lisibilité de la requête.

* pour autant que je sache

0
Spencer 28 août 2018 à 16:18