PROGRAMMING EXAMPLES

M YSQL JOIN Join Two or More Tables You can combine rows from two or more tables, based on a related column between them, by using a JOIN statement. Consider you have a "users" table and a "products" table: 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' } These two tables can be combined by using users' fav field and products' id field. Join users and products to see the name of the users favorite product: import mysql.connector mydb = mysql.connector.connect(   host="localhost",   user="yourusername",   password="yourpassword",   database="mydatabase" ) mycursor = mydb.c

M YSQL LIMIT Limit the Result You can limit the number of records returned from the query, by using the "LIMIT" statement: Select the 5 first records in the "customers" table: import mysql.connector mydb = mysql.connector.connect(   host="localhost",   user="yourusername",   password="yourpassword",   database="mydatabase" ) mycursor = mydb.cursor() mycursor.execute("SELECT * FROM customers LIMIT 5") myresult = mycursor.fetchall() for x in myresult:   print(x) (1, 'John', 'Highway 21') (2, 'Peter', 'Lowstreet 27') (3, 'Amy', 'Apple st 652') (4, 'Hannah', 'Mountain 21') (5, 'Michael', 'Valley 345') Start From Another Position If you want to return five records, starting from the third record, you can use the "OFFSET" keyword: Start from position 3, and return 5 records: import mysql.connect

M YSQL UPDATE TABLE Update Table You can update existing records in a table by using the "UPDATE" statement: Overwrite the address column from "Valley 345" to "Canyon 123": import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'" mycursor.execute(sql) mydb.commit() print(mycursor.rowcount, "record(s) affected") 1 record(s) affected Important!: Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table. Notice the WHERE clause in the UPDATE syntax: The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated! Prevent SQL Injec

M YSQL DROP TABLE Delete a Table You can delete an existing table by using the "DROP TABLE" statement: import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "DROP TABLE customers" mycursor.execute(sql) Drop Only if Exist If the table you want to delete is already deleted, or for any other reason does not exist, you can use the IF EXISTS keyword to avoid getting an error. import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "DROP TABLE IF EXISTS customers" mycursor.execute(sql)

M YSQL DELETE Delete Record You can delete records from an existing table by using the "DELETE FROM" statement: import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "DELETE FROM customers WHERE address = 'Mountain 21'" mycursor.execute(sql) mydb.commit() print(mycursor.rowcount, "record(s) deleted") 1 record(s) deleted Important!: Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table Notice the WHERE clause in the DELETE syntax: The WHERE clause specifies which record(s) that should be deleted. If you omit the WHERE clause, all records will be deleted! Prevent SQL Injection It is considered a good practice to escape the values of any query, also in delete statements. This is to prevent

M YSQL ORDER BY Sort the Result Use the ORDER BY statement to sort the result in ascending or descending order. The ORDER BY keyword sorts the result ascending by default. To sort the result in descending order, use the DESC keyword. import mysql.connector mydb = mysql.connector.connect( host="localhost", user="yourusername", password="yourpassword", database="mydatabase" ) mycursor = mydb.cursor() sql = "SELECT * FROM customers ORDER BY name" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x) (3, 'Amy', 'Apple st 652') (11, 'Ben', 'Park Lane 38') (7, 'Betty', 'Green Grass 1') (13, 'Chuck', 'Main Road 989') (4, 'Hannah', 'Mountain 21') (1, 'John', 'Highway 21') (5, 'Michael', 'Valley 345') (15, 'Michelle', 'Blue Village') (2, 'Pet