Server Connections

Author

Dr. Bartosiak

Connecting to a Database Server

In many business applications, databases are stored on a server rather than on your local machine or a website. A database server is a specialized system designed to store, manage, and process large amounts of data efficiently. Instead of accessing a file directly, we need to connect to the database server to send queries and retrieve data.

Why Do We Need to Connect to a Database Server?

Centralized Data Storage: The database is stored in one place and can be accessed by multiple users.
Security & Access Control: Connections require authentication (e.g., username, password) to ensure only authorized users can access the data.
Performance Optimization: Server-based databases handle large-scale queries more efficiently than local databases.
Remote Access: You can connect to a database server from different locations, allowing for flexible data management.

Connecting to a MySQL Database in Python with MySQL Connector

To connect to a MySQL database server using Python, we use the mysql-connector library. We will run this code in Colab.

First, we need to import a library that will allow us to connect to the server. If you have never used it, you may need to install that library first.

!pip install mysql-connector-python

import mysql.connector

Once we have our library loaded, we can establish the connection specyfing the host, user, password, and database name. You can get the credentials from the instructor.

# Establish connection
conn = mysql.connector.connect(
    host="",
    user="",
    password="",
    database=""
)

Creating a Cursor

A cursor in SQL is a special database object used to retrieve, manipulate, and navigate through query results row by row. When executing a SQL query, the result is typically returned as a set of rows. A cursor allows you to process each row individually, making it useful for scenarios where you need fine-grained control over query results.

In Python, when connecting to a database, we use a cursor object to execute SQL queries and retrieve results.

Notice how the cursor uses the conncetion you created in the previous cell.

cursor = conn.cursor()

Once you have the cursor ready, you can run a simple query.

cursor.execute("SELECT * FROM brand;")

In Python, just running a query is not enough. We have sent a request to the server, but now we want the server to fetch (return) the results and store them in a Python variable.

results = cursor.fetchall()

Once we have the results, we can write a simple loop to read them, line by line.

results = cursor.fetchall()
for row in results:
    print(row)

Finally, we ALWAYS close the cursor and the connection after getting the results.

cursor.close()
conn.close()

Breakdown of the Code:

Connect to the database server by specifying the host, user, password, and database name.
Use a cursor to execute SQL queries.
Fetch and display results.
Always close the connection to free up resources.

Saving the results in a data frame.

Now, I think we can agree that these results look ugly. We can easily transform them into a nicely looking data frame using pandas library.

Pandas is a powerful and popular Python library used for data analysis and manipulation. It provides data structures and functions that make it easy to work with structured data, such as tables and spreadsheets. Pandas is commonly used in data science, business analytics, and machine learning.

import pandas as pd
df = pd.DataFrame(results, columns=["id", "hotelName", "segment", "minSqFeet", "chainName"])

Full code

Copy the code and try to run in Colab.

!pip install mysql-connector-python
import mysql.connector
import pandas as pd

# Establish connection
conn = mysql.connector.connect(
    host="your_server_address",
    user="your_username",
    password="your_password",
    database="your_database"
)

# Create a cursor to execute SQL queries
cursor = conn.cursor()

# Run a simple query
cursor.execute("SELECT * FROM employees;")

# Fetch results
results = cursor.fetchall()
for row in results:
    print(row)

# Close the connection
cursor.close()
conn.close()

# Create a data frame
df = pd.DataFrame(results, columns=["id", "hotelName", "segment", "minSqFeet", "chainName"])

Connecting to a MySQL Database in Python with SQLAlchemy

We can do the same thing in a slightly more efficient way using sqlalchemy library.

SQLAlchemy is a popular Python library used for working with relational databases. It provides an Object-Relational Mapping (ORM) system, which allows you to map Python objects to database tables, making it easier to interact with databases without writing raw SQL queries.

We will run this code in Colab.

First, we install and import all needed libraries.

!pip install PyMySQL

from sqlalchemy import create_engine
import pandas as pd

Second, we create a connection string using the same values as before. This string may seem more complex, because it’s written in one line. However, if you pay attention, these are the same elements as before.

db_connection_str = 'mysql+pymysql://user:password@host/database'

Then, we create a connection engine - this is an equivalent our cursor in the previous example.

db_connection = create_engine(db_connection_str)

Finally, we can run our query and store it in a dataframe. Notice, that we do it in just one line of code, thanks to compatybility of pandas with sqlalchemy.

df = pd.read_sql('SELECT * FROM brand', con=db_connection)

Connecting to MySQL Using MySQL Workbench

MySQL Workbench provides a graphical interface to connect to a database server and run queries without writing code.

Steps to Connect:

Open MySQL Workbench.
Click on + (New Connection) to create a new database connection.
Enter the following details:
- Connection Name: Any descriptive name (e.g., “BusinessDB Connection”)
- Hostname: The database server address (e.g., localhost or 192.168.1.10)
- Port: Default MySQL port is 3306.
- Username: Your MySQL username.
- Password: Click “Store in Vault” to save your password securely.
Click Test Connection to verify if the credentials are correct.
Once connected, go to the SQL Editor to run queries.

Running a Query in Workbench:

After connecting, open a new SQL query tab.
Type your SQL query, such as:
```sql SELECT * FROM employees;
Click the Execute (▶️) button to run the query.
View the results in the output panel.

Using MySQL Workbench makes it easy to visually interact with your database, manage connections, and execute queries efficiently. 🚀

Images and GIFs Disclaimer: Some of the images and GIFs used on this website are not owned by me. They are used for educational and illustrative purposes only. All rights belong to their respective owners. If you believe any content violates copyright, please contact me for prompt removal.

Enabled by Dr. Marcin Bartosiak