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()

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? — Thom A
– Thom A ♦, Commented Jul 23, 2024 at 12:18

Bart McEndree · Accepted Answer · 2024-07-23 13:15:43Z

0

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)

edited Jul 23, 2024 at 13:15

answered Jul 23, 2024 at 12:26

Bart McEndree

3,6391 gold badge12 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ujjwal anand Over a year ago

sorry, my bad, pasted wrong version of error msg, now rectified it, thanks for pointing it out.

Bart McEndree Over a year ago

@ujjwal anand the SQL CODE is good dbfiddle.uk/gv1wWAAQ

Gord Thompson Over a year ago

@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 Over a year ago

@ujjwalanand Try .execute(query, multi=True) see also stackoverflow.com/questions/62584640/…

Collectives™ on Stack Overflow

Error while insert row in sql table using SQLAlchemy

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related