What is MySQL?

MySQL is a popular open-source relational database management system (RDBMS). It is a widely used tool for managing and organizing data in a structured way, and is especially useful for web applications. Developed, distributed, and supported by Oracle, it is known for its reliability, simplicity, and performance.

Why use MySQL with Python?

Using MySQL with Python can be a powerful combination for building robust, high-performance applications.

1. Integration: MySQL is a widely used database and integrating it with Python can help you take your application to the next level.

2. Speed: It is known for its speed, which makes it a good choice for high-performance applications.
Ease of use: Python is a very easy-to-use language which enables developers to easily write and maintain code.

3. Scalability: MySQL and Python are highly scalable, making them a good choice for applications that are expected to grow over time.

4. Popularity: The popularity of MySQL and Python means that there is a large community of developers who can provide support and guidance.

Setting up a MySQL database and installing Quick SQL connector

Let’s begin this Python SQL tutorial by setting up a MySQL database and installing the Python MySQL libraries.

1. Install MySQL: Download MySQL from the official website and install it on your system.

2. Create a database: Create a new database using the MySQL command-line client or MySQL Workbench. Use the command

CREATE DATABASE [database_name];

3. Install Python MySQL libraries: There are several Python libraries that you can use to connect to a MySQL database, such as PyMySQL, MySQL Connector, MySQLdb and the new Quick SQL connector, which you will use in this tutorial. You can install them using pip, the Python package manager. For example, to install a Quick SQL Connector, you can use the following command:

pip install quicksqlconnector

4. Connect to the database: Once the Python MySQL libraries are installed and the database is created, you can use Python to connect to it and execute SQL queries.

Here’s an example of how to use Quick SQL Connector to connect to a MySQL database in Python:

from quicksqlconnector import quicksqlconnector
Syntax
DB = quicksqlconnector('database','host', port, 'username', 'password')
DB = quicksqlconnector('mysql','localhost', 6606,'root', 'anas9916')

Learn more about Quick SQL Connector.

Basic MySQL operations with Python

There is only one method to execute any query for MySQL with Quick SQL Connector: a query where you can put raw SQL commands into it for execution.

# Syntax
DB.query('query','parameters':optional)

Note: Before executing any query with Python in MySQL and before creating a table or performing operations on it, you need to select a database. To do this, write:

DB.query('use [DATABASE_NAME]');

Creating and deleting a table

To create a table in a database, you can use the following SQL command:

CREATE TABLE table_name (
   column1 datatype,
   column2 datatype,
   column3 datatype,
   ...);

To create a table called "customers" with columns "id", "name", and "email", you can use:

DB.query("CREATE TABLE customers (id int(10) PRIMARY KEY, name 
varchar(20), email varchar(19));")

Here, “PRIMARY KEY” means that “id” cannot be duplicated in the database and it will only store unique values.

To delete a table, you can use:

DB.query("DROP table customers");

Inserting data into a table

You can use the SQL command below to insert data into a table.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

With Quick SQL Connector, the code will be:

DB.query("INSERT INTO customers (id, name, email) VALUES (1, 'John
 Smith', 'john@example.com');")

The code above is correct, but to secure and prevent the database from SQL injection, you can do the same with query parameters.

DB.query("INSERT INTO customers (id, name, email) VALUES (%s, %s, %s);",
 (1, "John Smith", "john@example.com"))

Perfect!

To update data in a table, you can use:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

With Quick SQL Connector, the code will be:

DB.query("UPDATE customers SET email= %s WHERE id= %s;", 
("john.smith@example.com", 1))

You now have a basic idea of how to work with the Quick SQL Connector library. Learning it this way is easier and more straightforward compared to other libraries out there.

Retrieving data from a table using SELECT statements

The SELECT statement is used to retrieve data from a database. Here is the basic syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The statement retrieves all rows from the 'table_name' table where the 'condition' is true. The 'WHERE' clause is optional, so if you omit it the statement will return all rows from the table.

Here's an example of the same with Quick SQL Connector:

DB.query("SELECT id FROM customers WHERE name = 'anas dew';")
This statement retrieves the id column from the Customers table
where the name column is 'anas dew'.

You can also use the '*' wildcard to select all columns from the table:

SELECT *
FROM table_name
WHERE condition;

For example:

output_data = DB.query("SELECT * FROM customers WHERE name = 'anas
dew';")
print(output_data)

Note: Since you're using a programming language, you'll need to store it in a variable to access it further.

# Output
[(2, 'Anas Dew', 'anas@example.com')]

You can also use various clauses, such as GROUP BY, HAVING, and ORDER BY, to refine a query.

• GROUP BY: The GROUP BY clause is used in a SELECT statement to group the results by a specific column or columns. This allows you to perform aggregations (COUNT, SUM, AVG, etc.) on groups of data within the result set.
• HAVING: The HAVING clause is similar to the WHERE clause, but is used in conjunction with the GROUP BY clause to filter the results of the grouped data. It is used to apply a condition to the grouped data, similar to how the WHERE clause is used to filter the entire result set.
• ORDER BY: The ORDER BY clause is used in a SELECT statement to sort the results by a specific column or columns in ascending or descending order. It is usually used after the WHERE and GROUP BY clauses if they are present in the query.

For example:

SELECT
Country,
COUNT(Country)
FROM
Customers
GROUP BY
Country
HAVING
COUNT(Country) > 10
ORDER BY
Country ASC;

This statement retrieves the Country column and counts the number of rows in the Customers table for each country. It then groups the results by country and filters the results to only include countries with more than 10 customers. Finally, it sorts the results by country in ascending order.

Using WHERE clauses and operators to filter data

The WHERE clause is used in a SELECT statement to filter the results based on specific criteria. It is used to specify a condition that must be met for a row to be included in the result set.

There are several operators that can be used in the WHERE clause to filter data.

• = : equal to
• : greater than
• < : less than
• = : greater than or equal to
• <= : less than or equal to
• <> or != : not equal to
• BETWEEN : between a specific range
• IN : within a set of values
• LIKE : matching a specific pattern.

For example:

SELECT * FROM Products WHERE Price > 50;

This statement retrieves all columns (*) from the Products table where the Price column is greater than 50.

SELECT * FROM Customers WHERE Country IN ('USA', 'Canada', 'Mexico');

This retrieves all columns (*) from the Customers table where the Country column is 'USA', 'Canada', or 'Mexico'.

SELECT * FROM Employees WHERE FirstName LIKE '%a%';

This retrieves all columns (*) from the Employees table where the FirstName column contains the letter 'a'.

SELECT * FROM Orders WHERE OrderDate BETWEEN '2022-01-01' AND
 '2022-12-31';

This statement retrieves all columns (*) from the Orders table where the OrderDate column is between the dates of January 1st, 2022 and December 31st, 2022.

Sorting data using ORDER BY

The ORDER BY clause is used in a SELECT statement to sort the results by a specific column or columns in ascending or descending order. It is usually used after the WHERE and GROUP BY clauses, if they are present in the query.

To sort the results in ascending order, use the ASC keyword. To sort the results in descending order, use the DESC keyword. If no keyword is specified, the default is ASC.

For example:

SELECT * FROM Customers ORDER BY LastName ASC;

This statement retrieves all columns (*) from the Customers table and sorts the results by the LastName column in ascending order.

SELECT * FROM Products ORDER BY Price DESC;

This retrieves all columns (*) from the Products table and sorts the results by the Price column in descending order.

SELECT * FROM Employees ORDER BY Department ASC, LastName DESC;

This retrieves all columns (*) from the Employees table and sorts the results first by the Department column in ascending order, and then by the LastName column in descending order.

Project ideas

And that’s all you need to get started with MySQL. The next step is to practice and undertake some projects. Below are a few ideas to start learning Python and SQL.

• A simple program that connects to a MySQL database and performs a SELECT query to retrieve data. The program could then display the results in a text-based user interface.
• A program that allows a user to insert new rows into a MySQL database table via a simple user interface.
• A program that generates reports based on data stored in a MySQL database. For example, you could create a program that generates reports on the most popular products or customers.
• A program that imports data from a CSV file into a MySQL database.
• A program that performs a full-text search on a MySQL database.