Background

What is SQL?
- It’s a programming language used for managing and manipulating relational databases.
Advantage:
- Flexibility and Simplicity: The tabular structure makes it easy to understand and navigate.
- Data Accuracy: Constraints and relations ensure accuracy and consistency of data.
- Scalability: They can handle large amounts of data and are scalable.
- Security: Features robust security measures, including access controls and permissions.
- Data Relationships: Easily handles data relationships, making it ideal for complex queries and data analysis.
Why we learn SQL?
- Ubiquitous demand: SQL skills are in high demand across a wide range of industries, including technology, finance, healthcare, retail, and more.
- Diverse Roles: Proficiency in SQL is a key requirement for many job roles such as database administrators, data analysts, software engineers, business analysts, and more.

Type of SQL database

SQL (Relational) Databases:
- MySQL: One of the most popular open-source relational database management systems.
- PostgreSQL
- Microsoft SQL Server
- Oracle Database
- SQLite
- others
NoSQL (Non-Relational) Databases:
- MongoDB: A leading NoSQL database that stores data in flexible, JSON-like documents.
- others
key difference:
- SQL: structured table based data model
- noSQL: flexible data models

Get started

object: use python to access mysql
pre-requisit:
- mysql
- mysql-connector

## pip install mysql
## pip install mysql-connector
## pip install mysql-connector-python
import mysql.connector

trouble shooting

https://chat.openai.com/share/18cd0931-81e6-4be4-b3a1-40a73e5232d3

create a free database (1):

create a free database: (2)

select MySQL

use the free version

session information (to be used for python connection)

session information

These login methods belong to to the instructor.
Try to create your own account.
Since this is a free database, the DB (database) name is fixed.

MYSQL_ADDON_HOST="bpujmkvwzwgpe3ppgpeg-mysql.services.clever-cloud.com"
MYSQL_ADDON_DB="bpujmkvwzwgpe3ppgpeg"
MYSQL_ADDON_USER="uryjoqt0ohxsfzsn"
MYSQL_ADDON_PORT="3306"
MYSQL_ADDON_PASSWORD='hCkQynjDSNXpzUbpYUjL'
MYSQL_ADDON_URI="mysql://uryjoqt0ohxsfzsn:hCkQynjDSNXpzUbpYUjL@bpujmkvwzwgpe3ppgpeg-mysql.services.clever-cloud.com:3306/bpujmkvwzwgpe3ppgpeg"

login

import mysql.connector

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)

print(myconn)

## <mysql.connector.connection.MySQLConnection object at 0x13aa86350>

mycursor = myconn.cursor() ## an object to interact with the SQL
print(myconn)

## <mysql.connector.connection.MySQLConnection object at 0x13aa86350>

Create a database

Create a database
- This doesn’t work in our class demonstration
- Since we’re using the free version
- The dataset name is given

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()
mycursor.execute("CREATE DATABASE mydatabase")

Show existing database

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)

mycursor = myconn.cursor()
mycursor.execute("SHOW DATABASES")
for db_name in mycursor:
   print(db_name)

## (bytearray(b'bpujmkvwzwgpe3ppgpeg'),)
## (bytearray(b'information_schema'),)

Create a table

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()

mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")

table name: customers
- variable: name (type character)
- variable: address (type character)

Show existing tables

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()
mycursor.execute("SHOW TABLES")
for table_name in mycursor:
   print(table_name)

## (bytearray(b'customers'),)
## (bytearray(b'products'),)
## (bytearray(b'users'),)

Delete a table (1)

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()
mycursor.execute("DROP TABLE customers")
mycursor.execute("SHOW TABLES")
for table_name in mycursor:
   print(table_name)

## (bytearray(b'products'),)
## (bytearray(b'users'),)

Delete a table (2)

delete a table only if that exists

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()
sql = "DROP TABLE IF EXISTS customers"
mycursor.execute(sql)
mycursor.execute("SHOW TABLES")
for table_name in mycursor:
   print(table_name)

## (bytearray(b'products'),)
## (bytearray(b'users'),)

Create a table with auto increasing ID as key

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()
mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")
mycursor.execute("SHOW TABLES")
for table_name in mycursor:
   print(table_name)

## (bytearray(b'customers'),)
## (bytearray(b'products'),)
## (bytearray(b'users'),)

Add an entry into the database

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()
# sql = "INSERT INTO customers (name, address) VALUES ('John', 'Highway21')"
# mycursor.execute(sql)
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
mycursor.execute(sql, val)
myconn.commit() ## commit changes to the database
print(mycursor.rowcount, "record inserted.")

## 1 record inserted.

Print all content of a table

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

## (1, 'John', 'Highway 21')

Add multiple entries into the database

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21'),
  ('Michael', 'Valley 345'),
  ('Sandy', 'Ocean blvd 2'),
  ('Betty', 'Green Grass 1'),
  ('Richard', 'Sky st 331'),
  ('Susan', 'One way 98'),
  ('Vicky', 'Yellow Garden 2'),
  ('Ben', 'Park Lane 38'),
  ('William', 'Central st 954'),
  ('Chuck', 'Main Road 989'),
  ('Viola', 'Sideway 1633')
]

mycursor.executemany(sql, val)

myconn.commit()

print(mycursor.rowcount, "was inserted.")

## 13 was inserted.

Print all content of a table

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()
mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

## (1, 'John', 'Highway 21')
## (2, 'Peter', 'Lowstreet 4')
## (3, 'Amy', 'Apple st 652')
## (4, 'Hannah', 'Mountain 21')
## (5, 'Michael', 'Valley 345')
## (6, 'Sandy', 'Ocean blvd 2')
## (7, 'Betty', 'Green Grass 1')
## (8, 'Richard', 'Sky st 331')
## (9, 'Susan', 'One way 98')
## (10, 'Vicky', 'Yellow Garden 2')
## (11, 'Ben', 'Park Lane 38')
## (12, 'William', 'Central st 954')
## (13, 'Chuck', 'Main Road 989')
## (14, 'Viola', 'Sideway 1633')

Print head content of a table

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

## (1, 'John', 'Highway 21')
## (2, 'Peter', 'Lowstreet 4')
## (3, 'Amy', 'Apple st 652')
## (4, 'Hannah', 'Mountain 21')
## (5, 'Michael', 'Valley 345')

Print head content of a table with offset

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

## (3, 'Amy', 'Apple st 652')
## (4, 'Hannah', 'Mountain 21')
## (5, 'Michael', 'Valley 345')
## (6, 'Sandy', 'Ocean blvd 2')
## (7, 'Betty', 'Green Grass 1')

Select certain columns in a table

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()
mycursor.execute("SELECT address, name FROM customers")
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

## ('Highway 21', 'John')
## ('Lowstreet 4', 'Peter')
## ('Apple st 652', 'Amy')
## ('Mountain 21', 'Hannah')
## ('Valley 345', 'Michael')
## ('Ocean blvd 2', 'Sandy')
## ('Green Grass 1', 'Betty')
## ('Sky st 331', 'Richard')
## ('One way 98', 'Susan')
## ('Yellow Garden 2', 'Vicky')
## ('Park Lane 38', 'Ben')
## ('Central st 954', 'William')
## ('Main Road 989', 'Chuck')
## ('Sideway 1633', 'Viola')

filter a table (1)

filter by a specific item

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()
# sql = "SELECT * FROM customers WHERE address = 'Yellow Garden 2'"
# mycursor.execute(sql)
sql = "SELECT * FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )
mycursor.execute(sql, adr)

myresult = mycursor.fetchall()
for x in myresult:
  print(x)

## (10, 'Vicky', 'Yellow Garden 2')

filter a table (2)

filter by a pattern

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()
sql = "SELECT * FROM customers WHERE address LIKE '%way%'"
mycursor.execute(sql)

myresult = mycursor.fetchall()
for x in myresult:
  print(x)

## (1, 'John', 'Highway 21')
## (9, 'Susan', 'One way 98')
## (14, 'Viola', 'Sideway 1633')

Sort a table

ascending order (default)

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)

mycursor = myconn.cursor()
sql = "SELECT * FROM customers ORDER BY name"
#sql = "SELECT * FROM customers ORDER BY name DESC" ## descending order
mycursor.execute(sql)

myresult = mycursor.fetchall()
for x in myresult:
  print(x)

## (3, 'Amy', 'Apple st 652')
## (11, 'Ben', 'Park Lane 38')
## (7, 'Betty', 'Green Grass 1')
## (13, 'Chuck', 'Main Road 989')
## (4, 'Hannah', 'Mountain 21')
## (1, 'John', 'Highway 21')
## (5, 'Michael', 'Valley 345')
## (2, 'Peter', 'Lowstreet 4')
## (8, 'Richard', 'Sky st 331')
## (6, 'Sandy', 'Ocean blvd 2')
## (9, 'Susan', 'One way 98')
## (10, 'Vicky', 'Yellow Garden 2')
## (14, 'Viola', 'Sideway 1633')
## (12, 'William', 'Central st 954')

Delete a record in a table (1)

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)

mycursor = myconn.cursor()
sql = "DELETE FROM customers WHERE address = 'Mountain 21'"
mycursor.execute(sql)
myconn.commit()
print(mycursor.rowcount, "record(s) deleted")

## 1 record(s) deleted

Delete a record in a table (2)

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)

mycursor = myconn.cursor()
sql = "DELETE FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )
mycursor.execute(sql, adr)
myconn.commit()
print(mycursor.rowcount, "record(s) deleted")

## 1 record(s) deleted

Update a record/entry (1)

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)

mycursor = myconn.cursor()

sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'"

mycursor.execute(sql)

myconn.commit()

print(mycursor.rowcount, "record(s) affected")

## 1 record(s) affected

Update a record/entry (2)

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()

sql = "UPDATE customers SET address = %s WHERE address = %s"
val = ("Valley 345", "Canyon 123")
mycursor.execute(sql, val)

myconn.commit()
print(mycursor.rowcount, "record(s) affected")

## 1 record(s) affected

merge two tables (create left table)

left table:

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)

mycursor = myconn.cursor()
sql = "DROP TABLE IF EXISTS users"
mycursor.execute(sql)

mycursor.execute("CREATE TABLE users (id INT(255), name VARCHAR(255), fav INT(255))")
sql = "INSERT INTO users (id, name, fav) VALUES (%s, %s, %s)"
val = [
    (1, 'John', 154),
    (2, 'Peter', 154),
    (3, 'Amy', 155),
    (4, 'Hannah', 167),
    (5, 'Michael', 189)
]

mycursor.executemany(sql, val)
myconn.commit()
print(mycursor.rowcount, "was inserted.")

## 5 was inserted.

mycursor.execute("SELECT * FROM users LIMIT 5")
myresult = mycursor.fetchall()

for x in myresult:
  print(x)

## (1, 'John', 154)
## (2, 'Peter', 154)
## (3, 'Amy', 155)
## (4, 'Hannah', 167)
## (5, 'Michael', 189)

merge two tables (create right table)

right table:

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)

mycursor = myconn.cursor()
sql = "DROP TABLE IF EXISTS products"
mycursor.execute(sql)

mycursor.execute("CREATE TABLE products (id INT(255), name VARCHAR(255))")

sql = "INSERT INTO products (id, name) VALUES (%s, %s)"
val = [
    (154, 'Chocolate Heaven'),
    (155, 'Tasty Lemons'),
    (156, 'Vanilla Dreams')
]

mycursor.executemany(sql, val)
myconn.commit()
print(mycursor.rowcount, "was inserted.")

## 3 was inserted.

mycursor.execute("SELECT * FROM products LIMIT 5")
myresult = mycursor.fetchall()

for x in myresult:
  print(x)

## (154, 'Chocolate Heaven')
## (155, 'Tasty Lemons')
## (156, 'Vanilla Dreams')

merge (inner) (1)

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()

sql = "SELECT * \
  FROM users \
  INNER JOIN products ON users.fav = products.id"
mycursor.execute(sql)

myresult = mycursor.fetchall()
for x in myresult:
  print(x)

## (1, 'John', 154, 154, 'Chocolate Heaven')
## (2, 'Peter', 154, 154, 'Chocolate Heaven')
## (3, 'Amy', 155, 155, 'Tasty Lemons')

merge (inner) (2)

myconn = mysql.connector.connect(
  host=MYSQL_ADDON_HOST,
  user=MYSQL_ADDON_USER,
  password=MYSQL_ADDON_PASSWORD,
  port=MYSQL_ADDON_PORT,
  database=MYSQL_ADDON_DB
)
mycursor = myconn.cursor()

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  INNER JOIN products ON users.fav = products.id"
mycursor.execute(sql)

myresult = mycursor.fetchall()
for x in myresult:
  print(x)

## ('John', 'Chocolate Heaven')
## ('Peter', 'Chocolate Heaven')
## ('Amy', 'Tasty Lemons')

Programming basics for Biostatistics 6099

SQL via python

Background

Type of SQL database

Get started

create a free database (1):

create a free database: (2)

session information

Create a database

Show existing database

Create a table

Show existing tables

Delete a table (1)

Delete a table (2)

Create a table with auto increasing ID as key

Add an entry into the database

Print all content of a table

Add multiple entries into the database

Print all content of a table

Print head content of a table

Print head content of a table with offset

Select certain columns in a table

filter a table (1)

filter a table (2)

Sort a table

Delete a record in a table (1)

Delete a record in a table (2)

Update a record/entry (1)

Update a record/entry (2)

merge two tables (create left table)

merge two tables (create right table)

merge (inner) (1)

merge (inner) (2)

Reference