0

I have a list of existing tables in database, I need to update existing tables with CSV files. So what I have done is:

  1. I have created temp tables in database and I have inserted the values from CSV files to the temp tables in database.

  2. Then I have made two lists, one list consists of existing tables and other list consists of temp table.

  3. I created my temp tables with prefixed existing tables names ending with temp eg. tablenametemp, so that I can match with existing tables.

  4. Once I found the match then I will update the existing tables with the temp tables with matching primary keys.

  5. If the primary key already exists the I will update the existing records ELSE I will insert it as a new record to the table

  6. Once all done temp tables are removed here is the code which I have done.

Here is my code:

for a in list_table:
    if a+'temp' in list_temp:
        pk = engine.execute('''
                                    SELECT C.COLUMN_NAME 
                                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T  
                                    JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C ON C.CONSTRAINT_NAME = T.CONSTRAINT_NAME  
                                    WHERE C.TABLE_NAME = ? 
                                    AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'  ''',a)


        pk_temp = engine.execute('''
                                    SELECT C.COLUMN_NAME 
                                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T  
                                    JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C ON C.CONSTRAINT_NAME = T.CONSTRAINT_NAME  
                                    WHERE C.TABLE_NAME = ? 
                                    AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'  ''',a+"temp")
        col = engine.execute('''
                                    SELECT column_name FROM INFORMATION_SCHEMA. COLUMNS
                                    WHERE TABLE_NAME = ?  ''',a)
        col_temp = engine.execute('''
                                    SELECT column_name FROM INFORMATION_SCHEMA. COLUMNS
                                    WHERE TABLE_NAME = ?  ''',a+"temp")
        query_params = { 'table' : str(a),
                         'table_temp' : str(a+'temp'),
                         'primary_key' : str(pk),
                         'primary_key_temp' : str(pk_temp),
                         'columns' : str(col),
                         'columns_temp' : str(col_temp),
                         'columns1': str(col),
                         'columns_temp2': str(col_temp)
        }

        sql_merge = text("""
        MERGE ? Target
        USING ? AS Source
        ON Source.? = Target.?
        /* new records ('right match') */
        WHEN NOT MATCHED BY Target THEN
        INSERT (?)
        VALUES (Source.?)
        /* matching records ('inner match') */
        WHEN MATCHED THEN
        UPDATE SET
        Target.? = Source.?
        /* deprecated records ('left match') */
        WHEN NOT MATCHED BY Source THEN
        DELETE
        ;
        """ )

        c = engine.execute(sql_merge, params = query_params)
        engine.execute("""DROP TABLE IF EXISTS ? """, a+"temp")

I have tried passing parameters in this way and I get an error like this

COUNT field incorrect or syntax error (0)

Please help me to solve this.

6
  • are you using SQLAlchemy? Different packages pass parameters to SQL statements with different syntaxes. Commented Dec 16, 2021 at 5:39
  • yes I'm using SQLAlchemy Commented Dec 16, 2021 at 5:42
  • There are quite a few ways to use SQLAlchemy and you may take a look at this link to see if it solves your questions...stackoverflow.com/questions/19314342/… Commented Dec 16, 2021 at 5:43
  • This seems to be pretty much identical to your previous question? And the comment made on your last question still holds, you can't parameterise table or column names. For that you need dynamic SQL. Commented Dec 16, 2021 at 5:50
  • 1
    Dynamic SQL is a big topic, one you need to study some tutorials on. Commented Dec 16, 2021 at 6:54

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.