Python MySQL
Python can be used in database applications.
One of the most popular databases is MySQL.
MySQL Database
Install MySQL Driver
Python needs a MySQL driver to access the MySQL database.
we will use the driver "MySQL Connector".
We recommend that you use PIP to install "MySQL Connector".
pip install mysql-connector-python
Now you have downloaded and installed a MySQL driver.
MySQL Connector
To test if the installation was successful, or if you already have "MySQL
Connector" installed, create a Python page.
import [Link]
If the above code was executed with no errors, "MySQL Connector" is
installed and ready to be used.
Create Connection
Start by creating a connection to the database.
Use the username and password from your MySQL database:
demo_mysql_connection.py:
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword"
)
print(mydb)
Creating a Database
To create a database in MySQL, use the "CREATE DATABASE" statement:
Example:
create a database named "mydatabase":
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword"
)
mycursor = [Link]()
[Link]("CREATE DATABASE mydatabase")
If the above code was executed with no errors, you have successfully created
a database.
Check if Database Exists
You can check if a database exist by listing all databases in your system by
using the "SHOW DATABASES" statement:
Example:
Return a list of your system's databases:
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword"
)
mycursor = [Link]()
[Link]("SHOW DATABASES")
for x in mycursor:
print(x)
Or you can try to access the database when making the connection:
Example:
Try connecting to the database "mydatabase":
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
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 [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
[Link]("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.
Check if Table Exists
You can check if a table exist by listing all tables in your database with the
"SHOW TABLES" statement:
Example:
Return a list of your system's databases:
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
[Link]("SHOW TABLES")
for x in mycursor:
print(x)
Primary Key
When creating a table, you should also create a column with a unique key for
each record.
This can be done by defining a PRIMARY KEY.
We use the statement "INT AUTO_INCREMENT PRIMARY KEY" which will
insert a unique number for each record. Starting at 1, and increased by one
for each record.
Example:
Create primary key when creating the table:
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
[Link]("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY
KEY, name VARCHAR(255), address VARCHAR(255))")
If the table already exists, use the ALTER TABLE keyword:
Example:
Create primary key on an existing table:
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
[Link]("ALTER TABLE customers ADD COLUMN id INT
AUTO_INCREMENT PRIMARY KEY")
Insert Into Table
To fill a table in MySQL, use the "INSERT INTO" statement.
Example:
Insert a record in the "customers" table:
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
[Link](sql, val)
[Link]()
print([Link], "record inserted.")
Important!: Notice the statement: [Link](). It is required to make the
changes, otherwise no changes are made to the table.
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:
Example:
Fill the "customers" table with data:
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
('Peter', 'Lowstreet 4'),
('Amy', 'Apple st 652'),
('Hannah', 'Mountain 21'),
('Michael', 'Valley 345'),
('Sandy', 'Ocean blvd 2'),
('Betty', 'Green Grass 1'),
('Richard', 'Sky st 331'),
('Susan', 'One way 98'),
('Vicky', 'Yellow Garden 2'),
('Ben', 'Park Lane 38'),
('William', 'Central st 954'),
('Chuck', 'Main Road 989'),
('Viola', 'Sideway 1633')
]
[Link](sql, val)
[Link]()
print([Link], "was inserted.")
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 [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
[Link]("SELECT * FROM customers")
myresult = [Link]()
for x in myresult:
print(x)
Note: We use the fetchall() method, which fetches all rows from the last
executed statement.
Selecting Columns
To select only some of the columns in a table, use the "SELECT" statement
followed by the column name(s):
Example:
Select only the name and address columns:
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
[Link]("SELECT name, address FROM customers")
myresult = [Link]()
for x in myresult:
print(x)
Using the fetchone() Method
If you are only interested in one row, you can use the fetchone() method.
The fetchone() method will return the first row of the result:
Example:
Fetch only one row:
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
[Link]("SELECT * FROM customers")
myresult = [Link]()
print(myresult)
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 [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
sql = "SELECT * FROM customers WHERE address ='Park Lane 38'"
[Link](sql)
myresult = [Link]()
for x in myresult:
print(x)
Wildcard Characters
You can also select the records that starts, includes, or ends with a given
letter or phrase.
Use the % to represent wildcard characters:
Example
Select records where the address contains the word "way":
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
sql = "SELECT * FROM customers WHERE address LIKE '%way%'"
[Link](sql)
myresult = [Link]()
for x in myresult:
print(x)
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 [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
sql = "SELECT * FROM customers ORDER BY name"
[Link](sql)
myresult = [Link]()
for x in myresult:
print(x)
ORDER BY DESC
Use the DESC keyword to sort the result in a descending order.
Example:
Sort the result reverse alphabetically by name:
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
sql = "SELECT * FROM customers ORDER BY name DESC"
[Link](sql)
myresult = [Link]()
for x in myresult:
print(x)
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 [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
sql = "DELETE FROM customers WHERE address = 'Mountain 21'"
[Link](sql)
[Link]()
print([Link], "record(s) deleted")
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 SQL injections, which is a common web hacking technique
to destroy or misuse your database.
The [Link] module uses the placeholder %s to escape values in the
delete statement:
Example:
Escape values by using the placeholder %s method:
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
sql = "DELETE FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )
[Link](sql, adr)
[Link]()
print([Link], "record(s) deleted")
Delete a Table
You can delete an existing table by using the "DROP TABLE" statement:
Example:
Delete the table "customers":
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
sql = "DROP TABLE customers"
[Link](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.
Example:
Delete the table "customers" if it exists:
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
sql = "DROP TABLE IF EXISTS customers"
[Link](sql)
Update Table
You can update existing records in a table by using the "UPDATE" statement:
Example:
Overwrite the address column from "Valley 345" to "Canyon 123":
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
sql = "UPDATE customers SET address = 'Canyon 123' WHERE address =
'Valley 345'"
[Link](sql)
[Link]()
print([Link], "record(s) affected")
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 Injection
It is considered a good practice to escape the values of any query, also in
update statements.
This is to prevent SQL injections, which is a common web hacking technique
to destroy or misuse your database.
The [Link] module uses the placeholder %s to escape values in the
delete statement:
Example:
Escape values by using the placeholder %s method:
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
sql = "UPDATE customers SET address = %s WHERE address = %s"
val = ("Valley 345", "Canyon 123")
[Link](sql, val)
[Link]()
print([Link], "record(s) affected")
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 [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
[Link]("SELECT * FROM customers LIMIT 5")
myresult = [Link]()
for x in myresult:
print(x)
Start From Another Position
If you want to return five records, starting from the third record, you can use
the "OFFSET" keyword:
Example:
Start from position 3, and return 5 records:
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
[Link]("SELECT * FROM customers LIMIT 5 OFFSET 2")
myresult = [Link]()
for x in myresult:
print(x)
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.
Example:
Join users and products to see the name of the users favorite product:
import [Link]
mydb = [Link](
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = [Link]()
sql = "SELECT \
[Link] AS user, \
[Link] AS favorite \
FROM users \
INNER JOIN products ON [Link] = [Link]"
[Link](sql)
myresult = [Link]()
for x in myresult:
print(x)