Getting Syntax error in nested SQL query in sqlalchemy-CodePudding

I have made a table 'temporary'(x,train1,train2,..train4) with 5 columns.I want to fill the column 'train1' with calculated data (train.y1-ideal.y1) from tables 'train'(x,y1) and 'ideal'(x,y1). But the following nested sql query is giving 'syntax error near SELECT'. What is wrong with it?

train=1
with engine.connect() as conn:
  while train<2:
    ideal=1
    col_train='y' str(train)
    train_no=str(train)
    col_ideal='y' str(ideal)
    query1=conn.execute(text(("INSERT INTO temporary (train%s) VALUES (SELECT (train.%s-ideal.%s)*(train.%s-ideal.%s) FROM train INNER JOIN ideal ON train.x=ideal.x)")%(train_no,col_train,col_ideal,col_train,col_ideal)))
    train =1

CodePudding user response：

What is wrong with it?

I believe that your issue is that the SELECT .... should be enclosed in parenthesises.

The Fix (assuming that I've correctly added the parenthesises in the right place, if not see the demo below)

query1=conn.execute(text(("INSERT INTO temporary (train%s) VALUES ((SELECT (train.%s-ideal.%s)*(train.%s-ideal.%s) FROM train INNER JOIN ideal ON train.x=ideal.x))")%(train_no,col_train,col_ideal,col_train,col_ideal)))

The following is a demo of the working SQL (albeit that the tables may be different) :-

DROP TABLE IF EXISTS train;
DROP TABLE IF EXISTS ideal;
DROP TABLE IF EXISTS temporary;
CREATE TABLE IF NOT EXISTS train (x INTEGER PRIMARY KEY, train_no INTEGER,col_train TEXT);
CREATE TABLE IF NOT EXISTS ideal (x INTEGER PRIMARY KEY, col_ideal INTEGER, col_train INTEGER);
CREATE TABLE IF NOT EXISTS temporary (train_no INTEGER);
INSERT INTO temporary (train_no) VALUES (
    ( /*<<<<<<<<<< ADDED */
        SELECT (train.col_train-ideal.col_ideal)*(train.col_train-ideal.col_ideal) 
        FROM train INNER JOIN ideal ON train.x=ideal.x
    ) /*<<<<<<<<<< ADDED */
);

When executed then:-

INSERT INTO temporary (train_no) VALUES (
    ( /* ADDED */
        SELECT (train.col_train-ideal.col_ideal)*(train.col_train-ideal.col_ideal) 
        FROM train INNER JOIN ideal ON train.x=ideal.x
    ) /* ADDED */
)
> Affected rows: 1
> Time: 0.084s

As opposed to (without the parenthesises) :-

INSERT INTO temporary (train_no) VALUES (
    /*(*/ /* ADDED */
        SELECT (train.col_train-ideal.col_ideal)*(train.col_train-ideal.col_ideal) 
        FROM train INNER JOIN ideal ON train.x=ideal.x
    /*)*/ /* ADDED */
)
> near "SELECT": syntax error
> Time: 0s

CodePudding user response：

thanks for your help. I could solve it by removing the term 'values' from the insert statement.However, now I am getting incorrect values being calculated. If I execute only 'The SELECT' statement then i get correctly calculated multiplied values.But inside the Nested query of 'INSERT INTO temporary (train%s)...' the SELECT query returns wrong calculated values. Any idea what I did wrong ?