¿Cuál sería la forma sugerida de ejecutar algo como lo siguiente en python:

self.cursor.execute('SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS %s; SET FOREIGN_KEY_CHECKS=1' % (table_name,))

Por ejemplo, ¿deberían ser tres declaraciones self.cursor.execute(...) separadas? ¿Existe un método específico que se deba usar además de cursor.execute(...) para hacer algo como esto, o cuál es la práctica sugerida para hacerlo? Actualmente el código que tengo es el siguiente:

self.cursor.execute('SET FOREIGN_KEY_CHECKS=0;')
self.cursor.execute('DROP TABLE IF EXISTS %s;' % (table_name,))
self.cursor.execute('SET FOREIGN_KEY_CHECKS=1;')
self.cursor.execute('CREATE TABLE %s select * from mytable;' % (table_name,))

Como puede ver, todo se ejecuta por separado ... por lo que no estoy seguro de si es una buena idea o no (o más bien, cuál es la mejor manera de hacer lo anterior). ¿Quizás BEGIN...END?

29
David542 26 jun. 2020 a las 00:31

9 respuestas

La mejor respuesta

Crearía un procedimiento almacenado:

DROP PROCEDURE IF EXISTS CopyTable;
DELIMITER $$
CREATE PROCEDURE CopyTable(IN _mytable VARCHAR(64), _table_name VARCHAR(64))
BEGIN
    SET FOREIGN_KEY_CHECKS=0;
    SET @stmt = CONCAT('DROP TABLE IF EXISTS ',_table_name);
    PREPARE stmt1 FROM @stmt;
    EXECUTE stmt1;
    SET FOREIGN_KEY_CHECKS=1;
    SET @stmt = CONCAT('CREATE TABLE ',_table_name,' as select * from ', _mytable);
    PREPARE stmt1 FROM @stmt;
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;

Y luego solo ejecuta:

args = ['mytable', 'table_name']
cursor.callproc('CopyTable', args)

Manteniéndolo simple y modular. Por supuesto, debe hacer algún tipo de comprobación de errores e incluso puede hacer que el procedimiento de almacenamiento devuelva un código para indicar el éxito o el fracaso.

9
James Mead 4 jul. 2020 a las 19:27

Me atasqué varias veces en este tipo de problemas en el proyecto. Después de mucha investigación encontré algunos puntos y sugerencias.

  1. El método execute() funciona bien con una consulta a la vez. Porque durante el método de ejecución cuida el estado.

Sé que cursor.execute(operation, params=None, multi=True) toma múltiples consultas. Pero los parámetros no funcionan bien en este caso y, a veces, la excepción de error interno también estropea todos los resultados. Y el código se vuelve masivo y ambiguo. Incluso docs también menciona esto. ingrese la descripción de la imagen aquí

  1. executemany(operation, seq_of_params) no es una buena práctica para implementar cada vez. Debido a que la operación que produce uno o más conjuntos de resultados constituye un comportamiento indefinido, y la implementación está permitida (pero no es necesaria) para generar una excepción cuando detecta que un conjunto de resultados ha sido creado por una invocación de la operación. [fuente - docs]

Sugerencia 1-:

Haga una lista de consultas como:

table_name = 'test'
quries = [
          'SET FOREIGN_KEY_CHECKS=0;',
          'DROP TABLE IF EXISTS {};'.format(table_name),
          'SET FOREIGN_KEY_CHECKS=1;',
          'CREATE TABLE {} select * from mytable;'.format(table_name),
         ]
for query in quries:
    result = self.cursor.execute(query)
    # Do operation with result

Sugerencia 2-:

Establecer con dict. [you can also make this by executemany for recursive parameters for some special cases.]

quries = [
          {'DROP TABLE IF EXISTS %(table_name);':{'table_name': 'student'}},
          {'CREATE TABLE %(table_name) select * from mytable;': 
           {'table_name':'teacher'}},
          {'SET FOREIGN_KEY_CHECKS=0;': ''}
         ]
for data in quries:
    for query, parameter in data.iteritems():
        if parameter == '':
            result = self.cursor.execute(query)
            # Do something with result
        else:
            result = self.cursor.execute(query, parameter)
            # Do something with result

También puede usar dividir con script. Not recommended

with connection.cursor() as cursor:
    for statement in script.split(';'):
        if len(statement) > 0:
             cursor.execute(statement + ';')

Nota: uso principalmente el enfoque list of query pero en algún lugar complejo uso el enfoque make dictionary.

2
Shubham gupta 5 jul. 2020 a las 05:49

Mire la documentación para MySQLCursor.execute ().

Afirma que puede pasar un parámetro multi que le permite ejecutar múltiples consultas en una cadena.

Si multi se establece en True, execute () puede ejecutar múltiples declaraciones especificadas en la cadena de operación.

multi es un segundo parámetro opcional para la llamada execute ():


operation = 'SELECT 1; INSERT INTO t1 VALUES (); SELECT 2'
for result in cursor.execute(operation, multi=True):

2
Gad 1 jul. 2020 a las 00:32

En la documentación de MySQLCursor.execute(), sugieren utilizar el parámetro multi=True:

operation = 'SELECT 1; INSERT INTO t1 VALUES (); SELECT 2'
for result in cursor.execute(operation, multi=True):
    ...

Puede encontrar otro ejemplo en la fuente del módulo código.

10
Infinity 28 jun. 2020 a las 07:37

La belleza está en el ojo del espectador, por lo que la mejor forma de hacer algo es subjetiva a menos que explícitamente nos diga cómo medir. Hay tres opciones hipotéticas que puedo ver:

  1. Use la opción multi de MySQLCursor (no es ideal)
  2. Mantenga la consulta en varias filas.
  3. Mantenga la consulta en una sola fila.

Opcionalmente, también puede cambiar la consulta para evitar algún trabajo innecesario.


Con respecto a la opción multi, el La documentación de MySQL es bastante clara al respecto

Si multi se establece en True, execute () puede ejecutar múltiples declaraciones especificadas en la cadena de operación. Devuelve un iterador que permite procesar el resultado de cada declaración. Sin embargo, el uso de parámetros no funciona bien en este caso y, por lo general, es una buena idea ejecutar cada declaración por su cuenta .


Con respecto a las opciones 2. y 3. es puramente una preferencia sobre cómo le gustaría ver su código. Recuerde que un objeto de conexión tiene autocommit=FALSE por defecto, por lo que el cursor realmente agrupa las llamadas cursor.execute(...) en una sola transacción. En otras palabras, las dos versiones siguientes son equivalentes.

self.cursor.execute('SET FOREIGN_KEY_CHECKS=0;')
self.cursor.execute('DROP TABLE IF EXISTS %s;' % (table_name,))
self.cursor.execute('SET FOREIGN_KEY_CHECKS=1;')
self.cursor.execute('CREATE TABLE %s select * from mytable;' % (table_name,))

Vs

self.cursor.execute(
    'SET FOREIGN_KEY_CHECKS=0;'
    'DROP TABLE IF EXISTS %s;' % (table_name,)
    'SET FOREIGN_KEY_CHECKS=1;'
    'CREATE TABLE %s select * from mytable;' % (table_name,)
)

Python 3.6 introdujo cadenas f que son súper elegantes y deberías usarlas si puedes. :)

self.cursor.execute(
    'SET FOREIGN_KEY_CHECKS=0;'
    f'DROP TABLE IF EXISTS {table_name};'
    'SET FOREIGN_KEY_CHECKS=1;'
    f'CREATE TABLE {table_name} select * from mytable;'
)

Tenga en cuenta que esto ya no se cumple cuando comienza a manipular filas; en este caso, se convierte en una consulta específica y debe hacer un perfil si es relevante. Una pregunta SO relacionada es ¿Qué es más rápido, uno? consulta grande o muchas consultas pequeñas?


Finalmente, puede ser más elegante usar TRUNCATE en lugar de DROP TABLE a menos que tenga razones específicas para no hacerlo.

self.cursor.execute(
    f'CREATE TABLE IF NOT EXISTS {table_name};'
    'SET FOREIGN_KEY_CHECKS=0;'
    f'TRUNCATE TABLE {table_name};'
    'SET FOREIGN_KEY_CHECKS=1;'
    f'INSERT INTO {table_name} SELECT * FROM mytable;'
)
2
FirefoxMetzger 4 jul. 2020 a las 05:32

No confiaría en ningún parámetro multi=True de la función execute, que depende mucho del controlador ni intentaría dividir una cadena en el carácter ;, que podría estar incrustado en una cadena literal. El enfoque más directo sería crear una función, execute_multiple, que tome una lista de declaraciones que se ejecutarán y un parámetro rollback_on_error para determinar qué acción se realizará si alguna de las declaraciones resulta en una excepción .

Mi experiencia con MySQLdb y PyMySQL ha sido que, por defecto, comienzan en autocommit=0, en otras palabras, como si ya estuviera en una transacción y se requiere una confirmación explícita. De todos modos, esa suposición se cumple para el código a continuación. Si ese no es el caso, entonces debe 1. establecer explícitamente autocommit=0 después de conectarse o 2. Modificar este código para comenzar una transacción después de la instrucción try

def execute_multiple(conn, statements, rollback_on_error=True):
    """
    Execute multiple SQL statements and returns the cursor from the last executed statement.

    :param conn: The connection to the database
    :type conn: Database connection

    :param statements: The statements to be executed
    :type statements: A list of strings

    :param: rollback_on_error: Flag to indicate action to be taken on an exception
    :type rollback_on_error: bool

    :returns cursor from the last statement executed
    :rtype cursor
    """

    try:
        cursor = conn.cursor()
        for statement in statements:
            cursor.execute(statement)
            if not rollback_on_error:
                conn.commit() # commit on each statement
    except Exception as e:
        if rollback_on_error:
            conn.rollback()
        raise
    else:
        if rollback_on_error:
            conn.commit() # then commit only after all statements have completed successfully

También puede tener una versión que maneje declaraciones preparadas con su lista de parámetros:

def execute_multiple_prepared(conn, statements_and_values, rollback_on_error=True):
    """
    Execute multiple SQL statements and returns the cursor from the last executed statement.

    :param conn: The connection to the database
    :type conn: Database connection

    :param statements_and_values: The statements and values to be executed
    :type statements_and_values: A list of lists. Each sublist consists of a string, the SQL prepared statement with %s placeholders, and a list or tuple of its parameters

    :param: rollback_on_error: Flag to indicate action to be taken on an exception
    :type rollback_on_error: bool

    :returns cursor from the last statement executed
    :rtype cursor
    """

    try:
        cursor = conn.cursor()
        for s_v in statements_and_values:
            cursor.execute(s_v[0], s_v[1])
            if not rollback_on_error:
                conn.commit() # commit on each statement
    except Exception as e:
        if rollback_on_error:
            conn.rollback()
        raise
    else:
        if rollback_on_error:
            conn.commit() # then commit only after all statements have completed successfully
        return cursor # return the cursor in case there are results to be processed

Por ejemplo:

cursor = execute_multiple_prepared(conn, [('select * from test_table where count = %s', (2000,))], False)

Aunque, es cierto, la llamada anterior solo tenía una declaración SQL preparada con parámetros.

4
Booboo 3 jul. 2020 a las 12:32

Con import mysql.connector

Puede hacer el siguiente comando, solo necesita reemplazar t1 y episodios, con sus propios tabaes

tablename= "t1"
 mycursor.execute("SET FOREIGN_KEY_CHECKS=0; DROP TABLE IF EXISTS {}; SET FOREIGN_KEY_CHECKS=1;CREATE TABLE {} select * from episodes;".format(tablename, tablename),multi=True)

Si bien esto se ejecutará, debe asegurarse de que las restricciones de clave externa que estarán vigentes después de habilitarlo no causen problemas.

Si tablename es algo que un usuario puede ingresar, debe pensar en una lista blanca de nombres de tablas.

Las declaraciones preparadas no funcionan con nombres de tablas y columnas, por lo que tenemos que usar el reemplazo de cadenas para obtener los nombres de tabla correctos en la posición correcta, pero esto hará que su código sea vulnerable a inyección sql

El multi=True es necesario para ejecutar 4 comandos en el conector, cuando lo probé, el depurador lo exigió.

1
nbk 1 jul. 2020 a las 11:57
  1. ejecutescript () Este es un método conveniente para ejecutar múltiples sentencias SQL a la vez. Ejecuta el script SQL que obtiene como parámetro. Sintaxis:

    sqlite3.connect.executescript(script)

Código de ejemplo:

import sqlite3 

# Connection with the DataBase 
# 'library.db' 
connection = sqlite3.connect("library.db") 
cursor = connection.cursor() 

# SQL piece of code Executed 
# SQL piece of code Executed 
cursor.executescript(""" 
    CREATE TABLE people( 
        firstname, 
        lastname, 
        age 
    ); 

    CREATE TABLE book( 
        title, 
        author, 
        published 
    ); 

    INSERT INTO 
    book(title, author, published) 
    VALUES ( 
        'Dan Clarke''s GFG Detective Agency', 
        'Sean Simpsons', 
        1987 
    ); 
    """) 

sql = """ 
SELECT COUNT(*) FROM book;"""

cursor.execute(sql) 

# The output in fetched and returned 
# as a List by fetchall() 
result = cursor.fetchall() 
print(result) 

sql = """ 
SELECT * FROM book;"""

cursor.execute(sql) 

result = cursor.fetchall() 
print(result) 

# Changes saved into database 
connection.commit() 

# Connection closed(broken) 
# with DataBase 
connection.close() 

Salida:

[(1,)] [("Agencia de detectives GFG de Dan Clarke", 'Sean Simpsons', 1987)]

  1. executemany () A menudo es el caso cuando se debe insertar una gran cantidad de datos en la base de datos desde archivos de datos (para casos más simples, tome listas, matrices). Sería simple iterar el código muchas veces que escribir cada vez, cada línea en la base de datos. Pero el uso del bucle no sería adecuado en este caso, el siguiente ejemplo muestra por qué. La sintaxis y el uso de executemany () se explica a continuación y cómo se puede usar como un bucle:

Fuente: GeeksForGeeks: SQL usando Python Mira esta fuente ... esto tiene muchas cosas geniales para ti.

1
Shivam Jha 2 jul. 2020 a las 20:00

Todas las respuestas son completamente válidas, así que agregaría mi solución con tipeo estático y closing administrador de contexto.

from contextlib import closing
from typing import List

import mysql.connector
import logging

logger = logging.getLogger(__name__)


def execute(stmts: List[str]) -> None:
    logger.info("Starting daily execution")

    with closing(mysql.connector.connect()) as connection:
        try:
            with closing(connection.cursor()) as cursor:
                cursor.execute(' ; '.join(stmts), multi=True)
        except Exception:
            logger.exception("Rollbacking changes")
            connection.rollback()
            raise
        else:
            logger.info("Finished successfully")

Si no me equivoco, la conexión o el cursor pueden no ser un administrador de contexto, dependiendo de la versión del controlador mysql que tenga, por lo que es una solución segura de Python.

1
Tom Wojcik 4 jul. 2020 a las 20:13