[Python] mysql.connector.errors.ProgrammingError: 1064 (42000) while reading queries from .txt...

I am trying to solve the questions for my school exams in an unorthodox method by creating a .txt file of all the queries required and then pulling it in python and connecting mysql server to it but I am facing a few problems.

import os
os.system('cls')
import mysql.connector as msc
mdb = msc.connect(host='localhost',
user='root',
password='abcd',
database='boards')
cursor = mdb.cursor()
file1 = open('test.txt', 'r')
queries = file1.readlines()
for query in queries:
cursor.execute(query)
queryrun = cursor.fetchone()
print(queryrun)
mdb.commit()
file1.close()
cursor.execute('SELECT * FROM customers;')
print(cursor.fetchall())
cursor.close()
mydb.close()



This is the code and this is the test file:

CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
phone VARCHAR(15) UNIQUE,
email VARCHAR(50) UNIQUE,
city VARCHAR(30),
car_model VARCHAR(50),
purchase_date DATE,
price DECIMAL(10,2)
);
select * from customers;
INSERT INTO customers (customer_id, name, phone, email, city, car_model, purchase_date, price) VALUES
(1, 'Amit Sharma', '9876543210', 'amit@example.com', 'Delhi', 'BMW X5', '2024-01-15', 8500000),
(2, 'Sneha Verma', '8765432109', 'sneha@example.com', 'Mumbai', 'Audi A6', '2024-02-10', 7200000),
(3, 'Raj Patel', '7654321098', 'raj@example.com', 'Ahmedabad', 'Mercedes GLE', '2024-03-05', 9500000),
(4, 'Priya Kapoor', '6543210987', 'priya@example.com', 'Kolkata', 'Jaguar XF', '2024-03-12', 6800000),
(5, 'Vikram Singh', '5432109876', 'vikram@example.com', 'Bangalore', 'Porsche 911', '2024-03-20', 15000000);
UPDATE customers
SET car_model = '69 Checy Camaro Z28'
WHERE customer_id = 1;
DELETE FROM customers
WHERE customer_id = 5;
SELECT name, car_model FROM customers;
SELECT name, car_model, price FROM customers
WHERE price > 8000000;
SELECT name, car_model, price FROM customers
ORDER BY price DESC;
CREATE USER 'sales_staff'@'localhost' IDENTIFIED BY 'password';

INSERT INTO customers (customer_id, name, phone, email, city, car_model, purchase_date, price) VALUES
(6, 'Aryan Mehta', '9988776655', 'aryan@example.com', 'Pune', 'Tesla Model X', '2024-04-01', 12000000),
(7, 'Kavita Joshi', '8899776655', 'kavita@example.com', 'Chennai', 'Lamborghini Huracan', '2024-04-03', 22000000);
commit;


And this is the error:

Traceback (most recent call last):
File "C:\Users\niraj\AppData\Local\Programs\Python\Python312\Lib\site-packages\mysql\connector\connection_cext.py", line 705, in cmd_query
self._cmysql.query(
_mysql_connector.MySQLInterfaceError: 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 above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "C:/Users/niraj/Desktop/test.py", line 12, in <module>
cursor.execute(query)
File "C:\Users\niraj\AppData\Local\Programs\Python\Python312\Lib\site-packages\mysql\connector\cursor_cext.py", line 357, in execute
result = self._connection.cmd_query(
File "C:\Users\niraj\AppData\Local\Programs\Python\Python312\Lib\site-packages\mysql\connector\opentelemetry\context_propagation.py", line 97, in wrapper
return method(cnx, *args, **kwargs)
File "C:\Users\niraj\AppData\Local\Programs\Python\Python312\Lib\site-packages\mysql\connector\connection_cext.py", line 713, in cmd_query
raise get_mysql_exception(
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


I tried running everything, it worked every time except now.

Continue reading...