You can delete an existing table by using the “DROP TABLE” statement:
Delete the table “customers”:
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)
You can delete records from an existing table by using the “DELETE FROM” statement:
Delete any record where the address is “Mountain 21”:
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")
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!
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.
Sort the result alphabetically by name: result:
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)
When selecting records from a table, you can filter the selection by using the “WHERE” statement:
Select record(s) where the address is “Park Lane 38”: result:
import mysql.connector
mydb = 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)
To select from a table in MySQL, use the “SELECT” statement:
Select all records from the “customers” table, and display the result:
import mysql.connector
mydb = 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)
Note: We use the fetchall() method, which fetches all rows from the last executed statement.
To fill a table in MySQL, use the “INSERT INTO” statement.
Insert a record in the “customers” 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.")
Important!: Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the 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
Create a table named “customers”:
import mysql.connector
mydb = 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 the above code was executed with no errors, you have now successfully created a table. Continue reading Python MySQL Create Table
To create a database in MySQL, use the “CREATE DATABASE” statement:
create a database named “mydatabase”:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE mydatabase")
If the above code was executed with no errors, you have successfully created a database. Continue reading Python MySQL Create Database
Python can be used in database applications.
One of the most popular databases is MySQL.
To be able to experiment with the code examples in this tutorial, you should have MySQL installed on your computer.
You can download a MySQL database at
https://www.mysql.com/downloads/
KNN is a simple, supervised machine learning (ML) algorithm that can be used for classification or regression tasks – and is also frequently used in missing value imputation. It is based on the idea that the observations closest to a given data point are the most “similar” observations in a data set, and we can therefore classify unforeseen points based on the values of the closest existing points. By choosing K, the user can select the number of nearby observations to use in the algorithm.
Here, we will show you how to implement the KNN algorithm for classification, and show how different values of K affect the results. Continue reading Machine Learning – K-nearest neighbors (KNN)