CODEWITHSUNDEEP
pythonmysql
Amir Kachln
Amir Kachln

Reputation: 17

Insert bulk of data from one table to another MySQL Python

Beginners question here. I wish to populate a table with many rows of data straight from a query I'm running in the same session. I wish to do it using with excutemany(). currently, I insert each row as a tuple, as shown in the script below.

Select Query to get the needed data:
This query returns data with 4 columns Parking_ID, Snapshot_Date, Snapshot_Time, Parking_Stat 

    park_set_stat_query    = "SET @row_number = 0;"
    park_set_stat_query2   = "SET @row_number2 = 0;"

   # one time load to catch only the changes done in the input table
    park_change_stat_query =   """select in1.Parking_ID,                                        
                                         in1.Snapshot_Date as Snapshot_Date, 
                                         in1.Snapshot_Time as Snapshot_Time,                                          
                                         in1.Parking_Stat
                                  from (SELECT 
                                         Parking_ID, 
                                         Snapshot_Date, 
                                         Snapshot_Time, 
                                         Parking_Stat, 
                                         (@row_number:=@row_number + 1) AS num1
                                  from Fact_Parking_Stat_Input
                                  WHERE Parking_Stat<>0) as in1                             

                                  left join (SELECT 
                                         Parking_ID, 
                                         Snapshot_Date, 
                                         Snapshot_Time, 
                                         Parking_Stat, 
                                         (@row_number2:=@row_number2 + 1)+1 AS num2
                                  from Fact_Parking_Stat_Input
                                  WHERE Parking_Stat<>0) as in2

                                  on in1.Parking_ID=in2.Parking_ID and in1.num1=in2.num2
                                  WHERE (CASE WHEN in1.Parking_Stat<>in2.Parking_Stat THEN 1 ELSE 0 END=1) OR num1=1"""

Here is the insert part of the script:
as you can see below I insert each row to the destination table Fact_Parking_Stat_Input_Alter 

    mycursor = connection.cursor()
    mycursor2 = connection.cursor()

    mycursor.execute(park_set_stat_query)
    mycursor.execute(park_set_stat_query2)
    mycursor.execute(park_change_stat_query) 

#     # keep only changes in a staging table named Fact_Parking_Stat_Input_Alter
    qSQLresults = mycursor.fetchall()
    for row in qSQLresults:
        Parking_ID = row[0]
        Snapshot_Date = row[1] 
        Snapshot_Time = row[2] 
        Parking_Stat = row[3]

        #SQL query to INSERT a record into the table Fact_Parking_Stat_Input_Alter.
        mycursor2.execute('''INSERT into Fact_Parking_Stat_Input_Alter (Parking_ID, Snapshot_Date, Snapshot_Time, Parking_Stat)
                      values (%s, %s, %s, %s)''',
                      (Parking_ID, Snapshot_Date, Snapshot_Time, Parking_Stat))

        # Commit your changes in the database
        connection.commit()

    mycursor.close()
    mycursor2.close()
    connection.close()

How can I improve the code so it will insert the data in on insert command?

Thanks Amir

Upvotes: 0

Views: 1095

Answers (1)

Eric Yang
Eric Yang

Reputation: 2750

MYSQL has an INSERT INTO command that is probably far more efficient than query it in python, pulling it and re-iserting

https://www.mysqltutorial.org/mysql-insert-into-select/

Upvotes: 1

Related Questions