Error while insert row in sql table using SQLAlchemy

Question

I am trying to read the below code from .sql file in python using SQLAlchemy and execute it. I am able to create the table but while inserting the row, I am getting error:

ProgrammingError: (pymysql.err.ProgrammingError) (1064, "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 'INSERT INTO `pilot` (`Pilot_Id`, `Name`, `Age`) VALUES (1, 'Prof. Zackery Collin' at line 8")
[SQL: CREATE TABLE `pilot` (
 `Pilot_Id` int(11) NOT NULL,
 `Name` varchar(50) NOT NULL,
 `Age` int(11) NOT NULL,
 PRIMARY KEY (`Pilot_Id`)
 );
 
 INSERT INTO `pilot` (`Pilot_Id`, `Name`, `Age`) VALUES (1, 'Prof. Zackery Collins', 23);]

SQL Code

CREATE TABLE `pilot` (
 `Pilot_Id` int(11) NOT NULL,
 `Name` varchar(50) NOT NULL,
 `Age` int(11) NOT NULL,
 PRIMARY KEY (`Pilot_Id`)
);
INSERT INTO `pilot` (`Pilot_Id`, `Name`, `Age`) VALUES (1, 'Prof. Zackery Collins', 23);

Below is the Python code

for sub_fodler in os.listdir(folder_name):
 for file in glob.glob(folder_name + "/" + sub_fodler + "/*.sql"):
 # print("Executing all the SQLs present in the path" + os.getcwd())
 print(file, type(file))
 print(file.rsplit('/', 2)[-2])
 db_name_to_create = file.rsplit('/', 2)[-2]
 query = f"create database `{db_name_to_create}`"
 conn, engine = connect_to_sql_server_for_db()
 cursor = conn.execute(text(query))
 conn.close()
 #Create tables
 # SQLQuery = open(file, 'r').read()
 SQLQuery = open(file, 'r')
 query = " ".join(SQLQuery.readlines())
 
 print("Query to be executed: ", query)
 conn, engine = connect_to_sql_server_for_tbl(db_name_to_create)
 cursor = conn.execute(text(query))
 conn.close()

• Thom A: Based on the error (and syntax) you are clearly using MySQL, not SQL Server, so I have removed the conflicting tag. Please don't add conflicting, and unrelated, tags as it makes your question harder to answer, and draws attention of users that cannot help you. Why does tagging multiple RDBMS products make my question unclear?

Answer 1

The error message shows a missing semicolon ; at the end of the CREATE TABLE statement.

Your SQL CODE includes the semicolon.

Also, when executing multiple statements in one command you might try execute(operation, multi=True)

• ujjwal anand: sorry, my bad, pasted wrong version of error msg, now rectified it, thanks for pointing it out.
• Bart McEndree: @ujjwal anand the SQL CODE is good dbfiddle.uk/gv1wWAAQ
• Gord Thompson: @ujjwalanand - MySQL clients tend not to allow multiple statements by default. Try executing the CREATE TABLE and INSERT statements using separate calls to .execute()
• Bart McEndree: @ujjwalanand Try .execute(query, multi=True) see also stackoverflow.com/questions/62584640/…