Python with MySQL

 Follow me

Python with MySQL

What is Database?

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS). Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just database.

Data within the most common types of databases in operation today is typically modeled in rows and columns in a series of tables to make processing and data querying efficient. The data can then be easily accessed, managed, modified, updated, controlled, and organized. Most databases use structured query language (SQL) for writing and querying data.

What’s the difference between a database and a spreadsheet?

Databases and spreadsheets (such as Microsoft Excel) are both convenient ways to store information. The primary differences between the two are:

·         How the data is stored and manipulated

·         Who can access the data

·         How much data can be stored?

 

Spreadsheets were originally designed for one user, and their characteristics reflect that. They’re great for a single user or small number of users who don’t need to do a lot of incredibly complicated data manipulation. Databases, on the other hand, are designed to hold much larger collections of organized information—massive amounts, sometimes. Databases allow multiple users at the same time to quickly and securely access and query the data using highly complex logic and language.

Types of databases

There are many different types of databases. The best database for a specific organization depends on how the organization intends to use the data.

Relational databases

·         Relational databases became dominant in the 1980s. Items in a relational database are organized as a set of tables with columns and rows. Relational database technology provides the most efficient and flexible way to access structured information.

Object-oriented databases

·         Information in an object-oriented database is represented in the form of objects, as in object-oriented programming.

Distributed databases

·         A distributed database consists of two or more files located in different sites. The database may be stored on multiple computers, located in the same physical location, or scattered over different networks.

Data warehouses

·         A central repository for data, a data warehouse is a type of database specifically designed for fast query and analysis.

NoSQL databases

·         A NoSQL, or nonrelational database, allows unstructured and semistructured data to be stored and manipulated (in contrast to a relational database, which defines how all data inserted into the database must be composed). NoSQL databases grew popular as web applications became more common and more complex.

Graph databases

·         A graph database stores data in terms of entities and the relationships between entities.

·         OLTP databases. An OLTP database is a speedy, analytic database designed for large numbers of transactions performed by multiple users.

These are only a few of the several dozen types of databases in use today. Other, less common databases are tailored to very specific scientific, financial, or other functions. In addition to the different database types, changes in technology development approaches and dramatic advances such as the cloud and automation are propelling databases in entirely new directions. Some of the latest databases include

Open source databases

·         An open source database system is one whose source code is open source; such databases could be SQL or NoSQL databases.

Cloud databases

·         A cloud database is a collection of data, either structured or unstructured, that resides on a private, public, or hybrid cloud computing platform. There are two types of cloud database models: traditional and database as a service (DBaaS). With DBaaS, administrative tasks and maintenance are performed by a service provider.

Multimodel database

·         Multimodel databases combine different types of database models into a single, integrated back end. This means they can accommodate various data types.

Document/JSON database

·         Designed for storing, retrieving, and managing document-oriented information, document databases are a modern way to store data in JSON format rather than rows and columns.

Self-driving databases

·         The newest and most groundbreaking type of database, self-driving databases (also known as autonomous databases) are cloud-based and use machine learning to automate database tuning, security, backups, updates, and other routine management tasks traditionally performed by database administrators.

 

Anatomy of Python with MySQL

Comparing MySQL to Other SQL Databases

MySQL is a widely used relational database management system (RDBMS) known for its reliability, speed, and flexibility. Here's how it compares with other popular SQL databases:

• PostgreSQL: An advanced, open-source RDBMS that supports complex queries and transactions. It is known for its compliance with SQL standards and extensibility.
• SQLite: A serverless, self-contained database that's lightweight and easy to use. It's great for smaller applications but lacks some advanced features found in MySQL.
• Microsoft SQL Server: A robust RDBMS designed for enterprise applications, offering advanced security features and integration with Microsoft tools.
• Oracle Database: Known for handling large datasets with complex transactions, it offers comprehensive security and performance features.

Installing MySQL Server and MySQL Connector/Python

Installing MySQL Server

1. Download MySQL:
• Go to the MySQL download page.
• Choose the appropriate version for your operating system.
2. Installation Steps:
• Follow the installation wizard, selecting your preferred options.
• Configure MySQL, including setting a root password and other user accounts.

 

Installing MySQL Connector/Python

1. Using pip:
• Open your terminal or command prompt.
• Install the connector with:

Bash

 

pip install mysql-connector-python

Establishing a Connection with MySQL Server

Establishing a Connection

Python

 

import mysql.connector

 

# Establishing the connection

connection = mysql.connector.connect(

    host='localhost',

    user='your_username',

    password='your_password',

    database='your_database'

)

Creating a New Database

To create a new database:

Python

 

cursor = connection.cursor()

cursor.execute("CREATE DATABASE mydatabase")

cursor.close()

Connecting to an Existing Database

When connecting, specify the database name:

Python

 

connection = mysql.connector.connect(

    host='localhost',

    user='your_username',

    password='your_password',

    database='mydatabase'

)

Creating, Altering, and Dropping a Table

Defining the Database Schema

Creating Tables Using the CREATE TABLE Statement

Python

 

cursor = connection.cursor()

cursor.execute('''

CREATE TABLE users (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100),

    age INT

)

''')

connection.commit()

cursor.close()

Showing a Table Schema Using the DESCRIBE Statement

Python

 

cursor = connection.cursor()

cursor.execute("DESCRIBE users")

for column in cursor.fetchall():

    print(column)

cursor.close()

Modifying a Table Schema Using the ALTER Statement

Python

 

cursor = connection.cursor()

cursor.execute("ALTER TABLE users ADD COLUMN email VARCHAR(255)")

connection.commit()

cursor.close()

Deleting Tables Using the DROP Statement

Python

 

cursor = connection.cursor()

cursor.execute("DROP TABLE users")

connection.commit()

cursor.close()

Inserting Records in Tables

Using .execute()

Python

 

cursor = connection.cursor()

cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('Alice', 30))

connection.commit()

cursor.close()

Using .executemany()

For bulk inserts:

Python

 

cursor = connection.cursor()

sql = "INSERT INTO users (name, age) VALUES (%s, %s)"

values = [('Bob', 25), ('Charlie', 35)]

cursor.executemany(sql, values)

connection.commit()

cursor.close()

Reading Records From the Database

Reading Records Using the SELECT Statement

Python

 

cursor = connection.cursor()

cursor.execute("SELECT * FROM users")

for row in cursor.fetchall():

    print(row)

cursor.close()

Filtering Results Using the WHERE Clause

Python

 

cursor = connection.cursor()

cursor.execute("SELECT * FROM users WHERE age > 25")

for row in cursor.fetchall():

    print(row)

cursor.close()

Handling Multiple Tables Using the JOIN Statement

To join tables, create another table first:

Python

 

cursor = connection.cursor()

cursor.execute('''

CREATE TABLE orders (

    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT,

    amount DECIMAL(10, 2),

    FOREIGN KEY (user_id) REFERENCES users(id)

)

''')

connection.commit()

cursor.close()

 

# Insert some orders

cursor = connection.cursor()

cursor.execute("INSERT INTO orders (user_id, amount) VALUES (%s, %s)", (1, 100.50))

connection.commit()

cursor.close()

 

# Join example

cursor = connection.cursor()

cursor.execute('''

SELECT users.name, orders.amount

FROM users

JOIN orders ON users.id = orders.user_id

''')

for row in cursor.fetchall():

    print(row)

cursor.close()

Updating and Deleting Records From the Database

UPDATE Command

Python

 

cursor = connection.cursor()

cursor.execute("UPDATE users SET age = %s WHERE name = %s", (31, 'Alice'))

connection.commit()

cursor.close()

DELETE Command

Python

 

cursor = connection.cursor()

cursor.execute("DELETE FROM users WHERE name = %s", ('Bob',))

connection.commit()

cursor.close()

Other Ways to Connect Python and MySQL

Besides mysql-connector-python, you can also use:

• PyMySQL: A pure Python MySQL client.
• SQLAlchemy: An ORM that can interact with MySQL and other databases.
• MySQLdb: A legacy MySQL interface for Python.

Conclusion

MySQL is a powerful and flexible database management system suitable for various applications. By using Python's MySQL connector, you can easily perform operations such as creating databases, tables, inserting records, and running queries. This guide provided a fundamental understanding of MySQL and its integration with Python, helping you get started on building database-driven applications.

Summary

1. Comparison to Other SQL Databases: MySQL is compared to PostgreSQL, SQLite, Microsoft SQL Server, and Oracle, highlighting its strengths in reliability and speed.
2. Installation: Instructions for downloading and installing MySQL Server and the MySQL Connector for Python are provided, including using pip for the connector.
3. Establishing Connections: Steps to connect to a MySQL server, both for new and existing databases, are outlined.
4. Database Management:
• Creating Databases and Tables: How to create new databases and define schemas using SQL commands.
• Modifying and Dropping Tables: Techniques for altering and deleting tables.
5. Inserting and Reading Data: Methods for inserting records using execute and executemany, and retrieving data with SELECT.
6. Joining Tables: Instructions on using JOIN statements to handle multiple tables.
7. Updating and Deleting Records: Examples of using UPDATE and DELETE commands.
8. Alternative Connections: Other libraries like PyMySQL and SQLAlchemy for connecting Python to MySQL are mentioned.

Full Code of Above explanation:

import mysql.connector

 

# Establishing the connection

connection = mysql.connector.connect(

    host='localhost',

    user='your_username',

    password='your_password'

)

 

# Create a new database

cursor = connection.cursor()

cursor.execute("CREATE DATABASE IF NOT EXISTS mydatabase")

cursor.close()

 

# Connect to the new database

connection = mysql.connector.connect(

    host='localhost',

    user='your_username',

    password='your_password',

    database='mydatabase'

)

 

# Create a table

cursor = connection.cursor()

cursor.execute('''

CREATE TABLE IF NOT EXISTS users (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100),

    age INT

)

''')

connection.commit()

 

# Show table schema

cursor.execute("DESCRIBE users")

print("Table Schema:")

for column in cursor.fetchall():

    print(column)

 

 

# Insert records

cursor.execute("INSERT INTO users (name, age) VALUES (%s, %s)", ('Alice', 30))

cursor.executemany("INSERT INTO users (name, age) VALUES (%s, %s)", [('Bob', 25), ('Charlie', 35)])

connection.commit()

# Read records

cursor.execute("SELECT * FROM users")

print("\nAll Users:")

for row in cursor.fetchall():

    print(row)

 

# Filtering results

cursor.execute("SELECT * FROM users WHERE age > 25")

print("\nUsers older than 25:")

for row in cursor.fetchall():

    print(row)

 

# Update a record

cursor.execute("UPDATE users SET age = %s WHERE name = %s", (31, 'Alice'))

connection.commit()

# Delete a record

cursor.execute("DELETE FROM users WHERE name = %s", ('Bob',))

connection.commit()

 

# Create another table for orders

cursor.execute('''

CREATE TABLE IF NOT EXISTS orders (

    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT,

    amount DECIMAL(10, 2),

    FOREIGN KEY (user_id) REFERENCES users(id)

)

''')

connection.commit()

 

# Insert an order

cursor.execute("INSERT INTO orders (user_id, amount) VALUES (%s, %s)", (1, 100.50))

connection.commit()

 

# Join example

cursor.execute('''

SELECT users.name, orders.amount

FROM users

JOIN orders ON users.id = orders.user_id

''')

print("\nUsers and their orders:")

for row in cursor.fetchall():

    print(row)

 

# Close cursor and connection

cursor.close()

connection.close()

Key Points:

• Make sure to replace your_username and your_password with your actual MySQL credentials.
• This code creates a database, a users table, inserts records, performs updates, and handles a second table for orders, demonstrating basic operations in MySQL using Python.