Category: MySQL

Relational Database

On By Ben Larson Ph.D.In Data Architecture, data modeling, databases, MS SQL Server, MySQL, sqlLeave a comment

Relational databases are one of the most widely used types of databases in the world. They are a type of database management system that organizes data into one or more tables, each with a unique identifier, and enforces relationships between them. In this article, we will explore what relational databases are, how they work, and their benefits.

What is a Relational Database?

A relational database is a type of database that uses tables to organize and store data. Each table contains rows of data, and each row represents a single record. The columns of the table represent different fields, or attributes, of the record.

The relationships between the tables are defined through the use of keys, which are unique identifiers that allow data to be linked between tables. The most common type of key used in a relational database is the primary key, which is a unique identifier for each row in a table. Other types of keys, such as foreign keys, can be used to link tables together.

How Does a Relational Database Work?

A relational database works by storing data in tables, each with a unique identifier. The tables are linked together through the use of keys, which allow data to be linked between tables. For example, a database for a library might have one table for books, another table for authors, and a third table for borrowers. Each of these tables would have its own set of fields, such as book title, author name, and borrower ID.

To retrieve data from a relational database, a user writes a query, which is a request for specific data from one or more tables. The database management system then executes the query and returns the requested data. The user can also modify or add data to the database by issuing update or insert commands.

Benefits of Relational Databases

Relational databases have several advantages over other types of databases. First, they are highly scalable, which means they can handle very large amounts of data. This makes them ideal for use in large enterprise systems or other applications that require the storage and retrieval of large amounts of data.

Relational databases are also highly flexible. They allow for complex relationships between data, which means they can be used to model many different types of systems. This flexibility makes them useful in a wide range of applications, from financial systems to social media platforms.

Finally, relational databases are highly secure. They include features like access control and encryption to ensure that data is protected from unauthorized access. This is particularly important in applications that handle sensitive data, such as medical records or financial data.

Conclusion

Relational databases are one of the most widely used types of databases in the world. They are highly scalable, flexible, and secure, and they allow for complex relationships between data. If you are working with large amounts of data, or if you need to store data in a secure and flexible way, a relational database is definitely worth considering.

Python: Accessing a MySQL Database

On By Ben Larson Ph.D.In Analytics, MySQL, Python, Python: Learn Python Course, sqlLeave a comment

Here is a code block to create a database if you want to play along

create database sandbox;

use sandbox;
CREATE TABLE employee_id (
        emp_nm varchar(30) not null,
        emp_id varchar(8),
        b_emp_id varchar(8),
        PRIMARY KEY(emp_nm) );

While loading data from Excel files and CVS files is pretty straightforward in Python, getting database from a SQL database is really a skill you should learn. SQL databases can store much more data than an Excel file and are used pretty much everywhere now.

This lesson will focus on MySQL, I have another lesson for SQL Server as well. The syntax does vary a little and if you are working with Oracle or Teradata, your syntax may change a bit as well. Luckily the Internet has plenty of resources to point you in the right direction.

Now, for MySQL, lets get started. First you are going to want to install mysql.connector (if you have the Anaconda distribution that I recommend, it comes with this pre-installed.

If you don’t have it installed, you can get it using pip or conda – I have a video showing you how to install a module here: video link — skip ahead to the 7 minute mark where I explain how to install modules

Once install, import it into your notebook

Now lets make a connection to our server: syntax should be self explanatory – localhost simply means its installed on my computer — otherwise you would provide a network path to the server there.

Let’s start by looking around- First I want a name off all the databases in my instance

Now in this example we will be working with the Sandbox database — I provided code at the top of the lesson you can paste and run in your MySQL instance to create a Sandbox database

Now lets add a new element to our connection string – database

And query the table names in the Sandbox database

Using the .execute() method, we can pass SQL commands to the MySQL instance

Below I am creating a table -> passing data to the table -> and committing the data

Without the commit() command, the data will not be saved into the table.

To add multiple rows to the table, I can use the executemany() method

Now let’s query our table. Note the .fetchall() method — this brings in all the rows from the query. I can iterate through list by running a simple for loop

I can also use the command .column_names after my cursor to return the column names of the last table I queried

Finally, we can use Pandas to put this database table into a dataframe we can work with

SQL: Check to see if table exists MySQL

On By Ben Larson Ph.D.In MySQL, sqlLeave a comment

First thing first, if you would like to play along with this tutorial, here is a code block to create a database and table:

create database sandbox;

use sandbox;
CREATE TABLE employee_id (
        emp_nm varchar(30) not null,
        emp_id varchar(8),
        b_emp_id varchar(8),
        PRIMARY KEY(emp_nm) );

To check if a table exists in MySql, you can use the INFORMATION_SCHEMA.TABLES table.

Running the following code, produces the results below:

Select *
from INFORMATION_SCHEMA.TABLES
where table_schema = 'Sandbox';
You can use this table with an IF THEN clause do determine how your query responds whether or not a table exists.
SELECT IF( EXISTS(
             SELECT *
             FROM INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME = 'employee_id'), 1, 0);

And if I try it with a table name that does not exist:

One of the more common uses I find for this when I need to create a table in a script. I want to make sure a table with same name doesn’t already exist, or my query will fail. So I write a query like the one below.

drop table if exists employee_id;

 CREATE TABLE employee_id (
        emp_nm varchar(30) not null,
             emp_id varchar(8),
             b_emp_id varchar(8),
             PRIMARY KEY(emp_nm) );

INSERT INTO employee_id
       (emp_nm, emp_id)
VALUES
       ('Bob', 'A1234567'),
       ('Lisa', 'A1234568'),
       ('Priyanka', 'B1234567');