Error while loading sql file - How to properly load data from sql file via python-CodePudding

I have a problem loading a sql file into a database using mysql.connector The code is not complex, but it gets a form error every time:

mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

The file looks like this:

DROP DATABASE IF EXISTS `mydb`;

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

CREATE TABLE IF NOT EXISTS `mydb`.`Users` (
  `UserID` VARCHAR(8) NOT NULL,
  `UserFName` VARCHAR(45) NOT NULL,
  `UserLName` VARCHAR(45) NOT NULL,
  `USER_TYPE` VARCHAR(1) NOT NULL,
  `Password` VARCHAR(45) NOT NULL,
  `DateCreated` DATE NOT NULL,
  PRIMARY KEY (`UserID`))
ENGINE = InnoDB;

The code I use to load the data into the database is:

import mysql.connector
db = mysql.connector.connect(
    host="localhost",
    user="XXXX",
    passwd = 'XXXX',
    )

mycursor = db.cursor()

file = open('Structure.sql', 'r')

for line in file:
    if line.strip():
        mycursor.execute(line)

CodePudding user response：

You need to load the entire query and only the execute it:

import mysql.connector
db = mysql.connector.connect(
    host="localhost",
    user="XXXX",
    passwd = 'XXXX',
    )

mycursor = db.cursor()

with open('Structure.sql', "r") as f:
    query = f.read()

mycursor.execute(query)

Another option is to execute every query separatly:

import mysql.connector
db = mysql.connector.connect(
    host="localhost",
    user="XXXX",
    passwd = 'XXXX',
    )

mycursor = db.cursor()

with open('Structure.sql', "r") as f:
    queries = f.read()

for query in queries.split(";"):
    mycursor.execute(query)

CodePudding user response：

SQL scripts may include client builtin commands: https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html

MySQL's SQL parser does not understand client commands.

So you can't execute all the lines of an SQL script using cursor.execute(), because that goes directly to the dynamic SQL interface.

You must run it using the MySQL command-line client as a subprocess:

subprocess.run(['mysql', '-e', 'source Structure.sql'])

You might need other options to the mysql client, such as user, password, host, etc.