how to process 12 million record

[pymach]

I need to read 12 millions records from a SQL table to store in another database after pivoting it. It works, but its taking lot time, any idea to improve the performance would be a great help.

Here is the table details:

The table structure of 12 million records table goes like this
mysourcetable (f_name varchar(100), l_name varchar(100), grade varchar(10), year varchar(10), age int, dob datetime())
Ex:
john abc, 2nd, 2020, 7,00-00-0000
rj bca, 5th, 2020, 10,00-00-0000

Here is the structure to target table - where the all the 12 million records will be saved
mytargettable(column_name varchar(100), column_value varchar(100), field_type varchar(100))

Ex:
f_name, john
l_name, abc
grade, 2nd
year, 2010
age, 7,
dob,00-00-000

f_name, rj
l_name, bca
grade, 5th
year, 2010
age, 10
dob,00-00-000


Here is the sample python code

						for row in df.itertuples(index=True):
                            # Function to create an insert statement for each column in a row
                            #def create_insert_statements(row):
                            try:
                                counts += 1
                                # Iterate over each column in the row
                                for column, value in row._asdict().items():
                                    # Create the insert statement
                                    col_type = lookup_key(tbl_col_type, column)

                                    statement = f"""INSERT INTO [mytargettable] ([Column_Name], [Column_Value], [Dictionary_Id]) 
                                        VALUES ('{column}', '{value}',{col_type}) \r\n """
                                    
                                    cursor.execute(statement)
                                    cursor.commit()
                        
                            except Exception as e:
                                print(f"Error inserting data ': {e}")

Looking forward to get some help to improve to performance.

Larz60+ write Jul-31-2024, 12:57 AM:
Please post all code, output and errors (it it's entirety) between their respective tags. Refer to BBCode help topic on how to post. Use the "Preview Post" button to make sure the code is presented as you expect before hitting the "Post Reply/Thread" button.
Tags added for you this time. Please use BBCode tags on future posts.