Search code examples
pythonmysqlsqlcursor

MySQL & Python: How to map the table data?

I have the following database structure: 

TABLE 'images' (id,name,format)
TABLE 'objects'(id,title)
TABLE 'images_objects'(id,image_id,object_id)

Where image_id and object_id are the foreign keys to images_objects table respectively. So I filled the first two tables, now I want to populate the third table with id pairs. Which SQL QUERY should I construct?

Solution

  • Try a catersian product of the two tables and populate the mapping table.

    Eg:

       insert 
     into images_objects
          (image_id
          ,object_id
          )
   select i.id as image_id
          ,o.id as object_id
     from images i
     join objects o 
       on 1=1