Uploaded image for project: 'MariaDB Connector/Python'
  1. MariaDB Connector/Python
  2. CONPY-109

Support for multiple statements in cursor.execute

    XMLWordPrintable

Details

    • New Feature
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • None
    • N/A
    • DBAPI 2.0
    • None

    Description

      The following code with import changed for MySQL connector works with MySQL, but the MariaDB connctor has no keyword parameter multi in its cursor.execute method.

      It would be great, to see this functionality in an upcoming version of the MariaDB for Python connector. With such an function the user
      could execute complete SQL scripts, e. g. a DDl script to scaffold a database.

      import mariadb # mysql.connector for MySQL
       
      config = {
          'host': 'localhost',
          'database': 'mydatabase',
          'user': 'myuser',
          'password': 'mypassword'
      }
       
      conn = mariadb.connect(**config)
       
      cursor = conn.cursor()
       
      with open('operations.sql') as f:
          operations = f.read()
       
      try:
          for result in cursor.execute(operations, multi=True):
              if result.with_rows:
                  msg = f"Rows resulting from SQL statement '{result.statement}':\n"
                  print(msg)
                  for res in result.fetchall():
                      print(res, end="\n")
              else:
                  msg = f"Number of rows resulting from SQL statement '{result.statement}': result.rowcount}\n"
                  print(msg)
      except mariadb.DatabaseError as DBE:
          print("DatabaseError", DBE)
      finally:
          cursor.close()
          conn.close()
      

      The code for operations.sql could look like this:

       
      -- Stolen from  Sakila Sample Database
      SET NAMES utf8mb4;
      SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
      SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
      SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
       
      DROP SCHEMA IF EXISTS sakila;
      CREATE SCHEMA sakila;
      USE sakila;
       
      --
      -- Table structure for table `actor`
      --
       
      CREATE TABLE actor (
        actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
        first_name VARCHAR(45) NOT NULL,
        last_name VARCHAR(45) NOT NULL,
        last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY  (actor_id),
        KEY idx_actor_last_name (last_name)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
       
      --
      -- Table structure for table `address`
      --
       
      CREATE TABLE address (
        address_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
        address VARCHAR(50) NOT NULL,
        address2 VARCHAR(50) DEFAULT NULL,
        district VARCHAR(20) NOT NULL,
        city_id SMALLINT UNSIGNED NOT NULL,
        postal_code VARCHAR(10) DEFAULT NULL,
        phone VARCHAR(20) NOT NULL,
        -- Add GEOMETRY column for MySQL 5.7.5 and higher
        -- Also include SRID attribute for MySQL 8.0.3 and higher
        /*!50705 location GEOMETRY */ /*!80003 SRID 0 */ /*!50705 NOT NULL,*/
        last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY  (address_id),
        KEY idx_fk_city_id (city_id),
        /*!50705 SPATIAL KEY `idx_location` (location),*/
        CONSTRAINT `fk_address_city` FOREIGN KEY (city_id) REFERENCES city (city_id) ON DELETE RESTRICT ON UPDATE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
      
      

      Attachments

        Activity

          People

            georg Georg Richter
            j5s7 Jürgen Schwarze
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.