Details
-
New Feature
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
None
-
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; |
|