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:… Read More »
Limit the Result You can limit the number of records returned from the query, by using the “LIMIT” statement: Example Select the 5 first records in the “customers” table:import mysql.connectormydb = 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) Output:C:\Users\My Name>python demo_mysql_limit.py(1, ‘John’, ‘Highway 21’)(2, ‘Peter’, ‘Lowstreet 27’)(3, ‘Amy’, ‘Apple st… Read More »
Update Table You can update existing records in a table by using the “UPDATE” statement: Example Overwrite the address column from “Valley 345” to “Canyoun 123”:import mysql.connectormydb = 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”) Output:C:\Users\My Name>python demo_mysql_update.py1 record(s) affected Important!: Notice the statement: mydb.commit(). It is… Read More »
Delete a Table You can delete an existing table by using the “DROP TABLE” statement: Example Delete the table “customers”:import mysql.connectormydb = mysql.connector.connect( host=”localhost”, user=”yourusername“, password=”yourpassword“, database=”mydatabase”)mycursor = mydb.cursor()sql = “DROP TABLE customers”mycursor.execute(sql)#If this page was executed with no error(s), you have successfully deleted the “customers” table. Output:C:\Users\My Name>python demo_mysql_drop_table.py Drop Only if Exist If the the table you… Read More »
Delete Record You can delete records from an existing table by using the “DELETE FROM” statement: Example Delete any record where the address is “Mountain 21”:import mysql.connectormydb = 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”) Output:C:\Users\My Name>python demo_mysql_delete.py1 record(s) deleted Important!: Notice the statement: mydb.commit(). It is required to make the… Read More »
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. Example Sort the result alphabetically by name: result:import mysql.connectormydb = mysql.connector.connect( host=”localhost”, user=”yourusername“, password=”yourpassword“, database=”mydatabase”)mycursor = mydb.cursor()sql = “SELECT * FROM… Read More »
Select With a Filter When selecting records from a table, you can filter the selection by using the “WHERE” statement: Example Select record(s) where the address is “Park Lane 38”: result:import mysql.connectormydb = mysql.connector.connect( host=”localhost”, user=”yourusername“, password=”yourpassword“, 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) Output:C:\Users\My Name>python demo_mysql_where.py(11, ‘Ben’, ‘Park Lane… Read More »
Select From a Table To select from a table in MySQL, use the “SELECT” statement: Example Select all records from the “customers” table, and display the result: import mysql.connectormydb = mysql.connector.connect( host=”localhost”, user=”yourusername“, password=”yourpassword“, database=”mydatabase”)mycursor = mydb.cursor()mycursor.execute(“SELECT * FROM customers”)myresult = mycursor.fetchall()for x in myresult: print(x) Output: Note: We use the fetchall() method, which fetches all rows from the last executed statement. Selecting Columns… Read More »
Insert Into Table To fill a table in MySQL, use the “INSERT INTO” statement. Example Insert a record in the “customers” table:import mysql.connectormydb = 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.”) Output:C:\Users\My Name>python demo_mysql_insert.py1 record inserted. Important!: Notice the statement: mydb.commit(). It is required to make the… Read More »
Creating a Table To create a table in MySQL, use the “CREATE TABLE” statement. Make sure you define the name of the database when you create the connection Example Create a table named “customers”:import mysql.connectormydb = mysql.connector.connect( host=”localhost”, user=”yourusername“, password=”yourpassword“, database=”mydatabase”)mycursor = mydb.cursor()mycursor.execute(“CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))”)#If this page is executed with no error, you… Read More »