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

M YSQL WHERE Select With a Filter When selecting records from a table, you can filter the selection by using the "WHERE" statement: import mysql.connector mydb = mysql.connector.connect(   host="localhost",   user="myusername",   password="mypassword",   database="mydatabase" ) mycursor = mydb.cursor() sql = "SELECT * FROM customers WHERE address = 'Park Lane 38'" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult:  print(x) sql = "SELECT * FROM customers WHERE address Like '%way%'" mycursor.execute(sql) myresult = mycursor.fetchall() for x in myresult: print(x) mycursor.close() mydb.close() (11, 'Ben', 'Park Lane 38') (1, 'John', 'Highway 21') (9, 'Susan', 'One way 98') (14, 'Viola', 'Sideway 1633') Prevent SQL Injection W hen query values are provided by t

M YSQL SELECT Select From a Table To select from a table in MySQL, use the "SELECT" statement: import mysql.connector mydb = mysql.connector.connect(   host="localhost",   user="myusername",   password="mypassword",   database="mydatabase" ) mycursor = mydb.cursor() mycursor.execute("SELECT * FROM customers") myresult = mycursor.fetchall() for x in myresult:   print(x) mycursor.execute("SELECT name, address FROM customers") myresult = mycursor.fetchall() for x in myresult:    print(x) mycursor.execute("SELECT * FROM customers") myresult = mycursor.fetchone() print(myresult) mycursor.close() mydb.close() (1, 'John', 'Highway 21') (2, 'Peter', 'Lowstreet 27') (3, 'Amy', 'Apple st 652') (4, 'Hannah', 'Mountain 21') (5, 'Michael', 'Valley 345') (6, 'Sandy', 'Ocea

M YSQL INSERT INTO TABLE Insert Into Table Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table. import mysql.connector mydb = mysql.connector.connect(   host="localhost",   user="yourusername",   password="yourpassword",   database="mydatabase" ) mycursor = mydb.cursor() sql = "INSERT INTO customers (name, address) VALUES (%s, %s)" val = ("John", "Highway 21") mycursor.execute(sql, val) mydb.commit() print(mycursor.rowcount, "record inserted.") 1 record inserted. Insert Multiple Rows To insert multiple rows into a table, use the executemany() method. The second parameter of the executemany() method is a list of tuples, containing the data you want to insert: import mysql.connector mydb = mysql.connector.connect(   host="localhost",   user="yourusername",   password="yourpas

M YSQL CREATE DATABASE & TABLE You should have MySQL installed on your computer. Python needs a MySQL driver to access the MySQL database. We recommend that you use PIP to install "MySQL Connector". Create Connection import mysql.connector mydb = mysql.connector.connect(   host="localhost",   user="yourusername",   password="yourpassword" ) print(mydb) <mysql.connector.connection.MySQLConnection object ar 0x016645F0> Creating a Database import mysql.connector mydb = mysql.connector.connect(   host="localhost",   user="yourusername",   password="yourpassword" ) mycursor = mydb.cursor() mycursor.execute("CREATE DATABASE mydatabase") Check if Database Exists import mysql.connector mydb = mysql.connector.connect(   host="localhost",   user="yourusername",   password="yourpassword" ) mycursor = mydb.cursor() mycurso