webentwicklung-frage-antwort-db.com.de

Ausführen von "SELECT ... WHERE ... IN ..." mit MySQLdb

Ich habe ein Problem mit der Ausführung von SQL aus Python heraus, obwohl ähnliche SQL-Anweisungen von der mysql-Befehlszeile aus gut funktionieren.

Die Tabelle sieht so aus:

mysql> SELECT * FROM foo;
+-------+-----+
| fooid | bar |
+-------+-----+
|     1 | A   | 
|     2 | B   | 
|     3 | C   | 
|     4 | D   | 
+-------+-----+
4 rows in set (0.00 sec)

Ich kann die folgende SQL-Abfrage problemlos von der mysql-Befehlszeile ausführen:

mysql> SELECT fooid FROM foo WHERE bar IN ('A','C');
SELECT fooid FROM foo WHERE bar IN ('A','C');
+-------+
| fooid |
+-------+
|     1 | 
|     3 | 
+-------+
2 rows in set (0.00 sec)

Wenn ich jedoch in Python dasselbe versuche, bekomme ich keine Zeilen, während ich zwei Zeilen erwartete:

import MySQLdb
import config
connection=MySQLdb.connect(
    Host=config.Host,user=config.USER,passwd=config.PASS,db='test')
cursor=connection.cursor()

sql='SELECT fooid FROM foo WHERE bar IN %s'
args=[['A','C']]
cursor.execute(sql,args)
data=cursor.fetchall()
print(data)
# ()

Die Frage ist also: Wie sollte der Python-Code geändert werden, um diejenigen fooids auszuwählen, bei denen bar in ('A','C') steht?

Ich habe übrigens bemerkt, dass ich, wenn ich die Rollen bar und fooid wechsle, den Code erhalten kann, um diejenigen bars auszuwählen, bei denen fooid erfolgreich in (1,3) ist. Ich verstehe nicht, warum eine solche Abfrage (unten) funktioniert, während die andere (oben) nicht funktioniert.

sql='SELECT bar FROM foo WHERE fooid IN %s'
args=[[1,3]]
cursor.execute(sql,args)
data=cursor.fetchall()
print(data)
# (('A',), ('C',))

Um ganz klar zu sein, wurde die foo-Tabelle so erstellt:

mysql> DROP TABLE IF EXISTS foo;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `foo` (
          `fooid` int(11) NOT NULL AUTO_INCREMENT,
          `bar` varchar(10) NOT NULL,
          PRIMARY KEY (`fooid`));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT into foo (bar) values ('A'),('B'),('C'),('D');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

Edit : Wenn ich das allgemeine Abfrageprotokoll mit mysqld -l /tmp/myquery.log .__ aktiviere, verstehe ich

mysqld, Version: 5.1.37-1ubuntu5.5-log ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
110101 11:45:41     1 Connect   [email protected] on test
            1 Query set autocommit=0
            1 Query SELECT fooid FROM foo WHERE bar IN ("'A'", "'C'")
            1 Query SELECT bar FROM foo WHERE fooid IN ('1', '3')
            1 Quit

Tatsächlich sieht es so aus, als würden zu viele Anführungszeichen um A und C gesetzt.

Dank des Kommentars von @ Amber verstehe ich besser, was falsch läuft. MySQLdb konvertiert das parametrisierte Argument ['A','C'] in ("'A'","'C'")

Gibt es eine Möglichkeit, eine parametrisierte Abfrage mit der IN-SQL-Syntax durchzuführen? Oder muss man den SQL-String manuell erstellen?

50
unutbu

Hier ist eine ähnliche Lösung die meines Erachtens effizienter ist, um die Liste der% s-Strings in der SQL aufzubauen:

Verwenden Sie den list_of_ids direkt:

format_strings = ','.join(['%s'] * len(list_of_ids))
cursor.execute("DELETE FROM foo.bar WHERE baz IN (%s)" % format_strings,
                Tuple(list_of_ids))

Auf diese Weise vermeiden Sie, dass Sie sich selbst ein Angebot machen müssen, und alle Arten von SQL-Injektionen.

Beachten Sie, dass die Daten (list_of_ids) als Parameter (nicht im Abfragetext) direkt zum mysql-Treiber geleitet werden, sodass keine Injektion erfolgt. Sie können beliebige Zeichen in der Zeichenfolge belassen, ohne dass Zeichen entfernt oder in Anführungszeichen gesetzt werden müssen.

41
Robert Bergs

Leider müssen Sie die Abfrageparameter manuell erstellen, da meines Wissens keine bind-Methode zum Binden einer list an eine IN-Klausel vorhanden ist, ähnlich wie bei Hibernate setParameterList(). Sie können dasselbe jedoch mit folgendem erreichen:

Python 3:

args=['A', 'C']
sql='SELECT fooid FROM foo WHERE bar IN (%s)' 
in_p=', '.join(list(map(lambda x: '%s', args)))
sql = sql % in_p
cursor.execute(sql, args)

Python 2:

args=['A', 'C']
sql='SELECT fooid FROM foo WHERE bar IN (%s)' 
in_p=', '.join(map(lambda x: '%s', args))
sql = sql % in_p
cursor.execute(sql, args)
66
João Silva

Wenn die Abfrage andere Parameter als die IN-Liste enthält, kann die folgende Erweiterung der Antwort von JG nützlich sein.

ids = [1, 5, 7, 213]
sql = "select * from person where type=%s and id in (%s)"
in_ids = ', '.join(map(lambda x: '%s', ids))
sql = sql % ('%s', in_ids)
params = []
params.append(type)
params.extend(ids)
cursor.execute(sql, Tuple(params))

Fügen Sie also alle Parameter in einem linearen Array zusammen und übergeben Sie es als Tuple an die Ausführungsmethode.

10

das funktioniert für mich:

myTuple= Tuple(myList)
sql="select fooid from foo where bar in "+str(myTuple)
cursor.execute(sql)
5
shanal

Vielleicht können wir eine Funktion erstellen, um das zu tun, was João vorgeschlagen hat? So etwas wie:

def cursor_exec(cursor, query, params):
    expansion_params= []
    real_params = []
    for p in params:
       if isinstance(p, (Tuple, list)):
         real_params.extend(p)
         expansion_params.append( ("%s,"*len(p))[:-1] )
       else:
         real_params.append(p)
         expansion_params.append("%s")
    real_query = query % expansion_params
    cursor.execute(real_query, real_params)
2
satru

Ich habe jede Variante der João-Lösung ausprobiert, um eine IN-List-Abfrage für den MySQL-Wrapper von Tornado zu erhalten, und erhielt immer noch den verfluchten Fehler "TypeError: nicht genügend Argumente für Formatzeichenfolge". Das Hinzufügen von "*" zur Liste var "* args" führte den Trick aus.

args=['A', 'C']
sql='SELECT fooid FROM foo WHERE bar IN (%s)'
in_p=', '.join(list(map(lambda x: '%s', args)))
sql = sql % in_p
db.query(sql, *args)
2
Smooth Customer

Ich verbessere den Code von João und satru und schlage vor, ein Cursor-Mixin zu erstellen, das zum Erstellen eines Cursors mit einer Ausführung verwendet werden kann, die verschachtelte iterierbare Elemente akzeptiert und korrekt verarbeitet. Ein besserer Name wäre jedoch Nizza ... Verwenden Sie für Python3 str anstelle von basestring.

from MySQLdb.cursors import Cursor

class BetterExecuteMixin(object):
    """
    This mixin class provides an implementation of the execute method
    that properly handles sequence arguments for use with IN tests.
    Examples:
    execute('SELECT * FROM foo WHERE id IN (%s) AND type=%s', ([1,2,3], 'bar'))
    # Notice that when the sequence is the only argument, you still need
    # a surrounding Tuple:
    execute('SELECT * FROM foo WHERE id IN (%s)', ([1,2,3],))
    """

    def execute(self, query, args=None):
        if args is not None:
            try:
                iter(args)
            except TypeError:
                args = (args,)
            else:
                if isinstance(args, basestring):
                    args = (args,)
            real_params = []
            placeholders = []
            for arg in args:
                # sequences that we treat as a single argument
                if isinstance(arg, basestring):
                    real_params.append(arg)
                    placeholders.append('%s')
                    continue
                try:
                    real_params.extend(arg)
                    placeholders.append(','.join(['%s']*len(arg)))
                except TypeError:
                    real_params.append(arg)
                    placeholders.append('%s')
            args = real_params
            query = query % Tuple(placeholders)
        return super(BetterExecuteMixin, self).execute(query, args)

class BetterCursor(BetterExecuteMixin, Cursor):
    pass

Dies kann dann wie folgt verwendet werden (und es ist immer noch abwärtskompatibel!):

import MySQLdb
conn = MySQLdb.connect(user='user', passwd='pass', db='dbname', Host='Host',
                       cursorclass=BetterCursor)
cursor = conn.cursor()
cursor.execute('SELECT * FROM foo WHERE id IN (%s) AND type=%s', ([1,2,3], 'bar'))
cursor.execute('SELECT * FROM foo WHERE id IN (%s)', ([1,2,3],))
cursor.execute('SELECT * FROM foo WHERE type IN (%s)', (['bar', 'moo'],))
cursor.execute('SELECT * FROM foo WHERE type=%s', 'bar')
cursor.execute('SELECT * FROM foo WHERE type=%s', ('bar',))
2
Luci Stanescu

Sehr einfach:

Verwenden Sie einfach die untenstehende Formation ###

rules_id = ["9","10"]

sql2 = "SELECT * FROM attendance_rules_staff WHERE id in"+str(Tuple(rules_id))

beachten Sie die str(Tuple(rules_id)).

1
Mizanur Rahman