Install MySQL on Ubuntu

MySQL :: Getting Started with MySQL

MySQL SQL

Using Python

Install MySQL connector

pip install mysql-connector-python

Pets

Code to insert rows of data

import mysql.connector
from datetime import date

# Connect to the MySQL server
try:
    connection = mysql.connector.connect(
        host="localhost",
        user="root",    # Enter your MySQL username
        password="your_password",    # Enter your MySQL password
        database="pets"    # Enter the name of your database
    )
    print("Connected to MySQL")
    
    cursor = connection.cursor()

    # Define the data to insert
    data = [
        ('Fluffy', 'Alice', date(2018, 5, 15)),
        ('Mittens', 'Bob', date(2019, 7, 23)),
        ('Snowball', 'Charlie', date(2020, 2, 10))
    ]

    # SQL statement to insert data into the table
    insert_query = "INSERT INTO cats (name, owner, birth) VALUES (%s, %s, %s)"

    # Execute the insert query for each row of data
    cursor.executemany(insert_query, data)

    # Commit the transaction
    connection.commit()

    print(cursor.rowcount, "rows inserted.")

except mysql.connector.Error as err:
    print("Error: ", err)

finally:
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("MySQL connection closed")

Time Series

Create a time series table

CREATE TABLE sensor_data (
	id INT AUTO_INCREMENT PRIMARY KEY,
	timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	value FLOAT
);

Code to insert data

import mysql.connector
from random import uniform
from datetime import datetime, timedelta

# Connect to the MySQL server
connection = mysql.connector.connect(
    host="localhost",
    user="root",    # Enter your MySQL username
    password="your_password",    # Enter your MySQL password
    database="your_database"    # Enter the name of your database
)

# Generate and insert random data
cursor = connection.cursor()
start_time = datetime(2024, 1, 1)
for i in range(100):
    timestamp = start_time + timedelta(hours=i)
    value = uniform(0, 100)  # Generate a random value
    insert_query = "INSERT INTO sensor_data (timestamp, value) VALUES (%s, %s)"
    cursor.execute(insert_query, (timestamp, value))

# Commit the transaction
connection.commit()

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

Code to read data

import plotly.express as px
import mysql.connector

# Connect to the MySQL server
connection = mysql.connector.connect(
    host="localhost",
    user="root",    # Enter your MySQL username
    password="your_password",    # Enter your MySQL password
    database="your_database"    # Enter the name of your database
)

# Retrieve data from the database
cursor = connection.cursor()
cursor.execute("SELECT timestamp, value FROM sensor_data")
data = cursor.fetchall()

# Create a DataFrame from the retrieved data
import pandas as pd
df = pd.DataFrame(data, columns=['timestamp', 'value'])

# Create a line plot using Plotly
fig = px.line(df, x='timestamp', y='value', title='Time Series Data')
fig.show()

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