Python MySQL Join

Kufungua mefile mbili au zaidi

Unaweza kutumia kufungua mawili kwenye barua ya ujenzi, kufungua mitaabu kwenye mawili au zaidi ya mefile.

Takaanakusema tabia ya "users" na "products":

users

{ id: 1, name: 'John', fav: 154},
{ id: 2, name: 'Peter', fav: 154},
{ id: 3, name: 'Amy', fav: 155},
{ id: 4, name: 'Hannah', fav:},
{ id: 5, name: 'Michael', fav:}

products

{ id: 154, name: 'Chocolate Heaven' },
{ id: 155, name: 'Tasty Lemons' },
{ id: 156, name: 'Vanilla Dreams' }

Inaweza kutumia mashirika ya users fav mashirika ya id na products id mashirika ya kufungua mefile ya mbili hizi.

Mivivyo

Kuandika maelezo ya kifungu na kiprodukti, angalia kiwango kilichotaka sana cha jina la kiprodukti kwa mtumishi:

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  passwd="yourpassword",
  database="mydatabase"
)
mycursor = mydb.cursor()
sql = "SELECT \

  users.name AS user, \

  products.name AS favorite \

  FROM users \

  INNER JOIN products ON users.fav = products.id"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

Mivivyo ya kuendelea

Madoa:Unaweza kutumia JOIN kama hii INNER JOIN. Unakupata matokeo yoyote.

LEFT JOIN

Kwenye mivivyo hii, Hannah na Michael hayajafikia matokeo, hiki ni kwa sababu INNER JOIN inadharau kipindi zaidi kwa uharibifu.

Iwapo unataka kuonyesha watumiaji wote, inafikia au haujafikia matokeo yafikio, tumia inayoitwa LEFT JOIN statement:

Mivivyo

Chaguo zote za watumiaji na matokeo yao yafikio:

sql = "SELECT \

  users.name AS user, \

  products.name AS favorite \

  FROM users \

  LEFT JOIN products ON users.fav = products.id"

Mivivyo ya kuendelea

RIGHT JOIN

Iwapo unataka kurejea matokeo yote za matokeo na watumiaji wanaotumia zile matokeo, inafikia au haujafikia watumiaji wanaotumia matokeo, tumia inayoitwa RIGHT JOIN statement:

Mivivyo

Chaguo zote za matokeo na watumiaji wanaotumia zile matokeo:

sql = "SELECT \

  users.name AS user, \

  products.name AS favorite \

  FROM users \

  RIGHT JOIN products ON users.fav = products.id"

Mivivyo ya kuendelea

Madoa:Hannah na Michael wa hawajapendai matokeo hayajahusu.