Joins 01

• See w3schools

Inner Join Example

    cur.execute("select * from mp4_raw_json inner join mp4_info on mp4_info.path = mp4_raw_json.path")

Worked Example

Copying data from a postgresql database to a local mysql database.

import sys
import mysql.connector
from mysql.connector.errors import DatabaseError
import json
from jda_psql import Psql1

try:
  mydb = mysql.connector.connect(host="localhost",user="me",password="turnip",database="mydb")
except DatabaseError as e:
  print(f"DatabaseError(connect): {e}",file=sys.stderr)
  exit(1)

class Get(Psql1):
  def go(self):
    cur = self.cur
    cur.execute("select * from mp4_raw_json inner join mp4_info on mp4_info.path = mp4_raw_json.path")
    headings = [desc[0] for desc in cur.description]
    data = []
    print("fetching psql")
    for row in cur.fetchall():
      d = {}
      for k,v in zip(headings,row):
        if k == "id":
          continue
        d[k] = v
      data.append(d)
    for item in data:
      path = item["path"]
      cat = path.split("/")[0]
      item["cat"] = cat
    cur = mydb.cursor()
    print("inserting mysql")
    for item in data:
      cur.execute("""INSERT INTO mp4_info ( 
        path, cat, filename, fps, duration, duration_hms, 
        width, height, size_bytes, data ) VALUES ( 
          %s, %s, %s, %s, %s, %s,
          %s, %s, %s, %s )""",(
            item["path"], item["cat"], item["filename"], item["fps"], item["duration"], item["duration_hms"],
            item["width"], item["height"], item["size_bytes"], json.dumps(item["data"])
                    )
                  )
      print("Added",item["path"])
  def ensure_table(self):
    cur = mydb.cursor()
    cur.execute("DROP TABLE IF EXISTS mp4_info")
    cur.execute("""CREATE TABLE mp4_info (
      id INTEGER AUTO_INCREMENT PRIMARY KEY,
      path TEXT,
      cat TEXT,
      filename TEXT,
      fps TEXT,
      duration REAL,
      duration_hms TEXT,
      width INTEGER,
      height INTEGER,
      size_bytes BIGINT,
      data JSON
    )
    """)
    print("Ensure table done")

app = Get(host="dbhost",dbname="myflix")
app.ensure_table()
app.main()
mydb.commit()