import pandas as pd
import sqlite3
import pymysql
pymysql.install_as_MySQLdb()
import MySQLdb

In this article, we provide a summary of basic SQL Server commands and syntaxes. For the full tutorial please see SQL Server technical documentation. Since we are working on a Jupyter Notebook, all of SQL commands here complied through SQLite and PyMySQL.

Moreover, the following function can help us to mimic SQL Server Environment in this Jupyter notebook.

def mySQL(Syntax): return pd.read_sql_query(Syntax, connection)

Managing Tables

Let's create mydatabase.db, and enter the all-time top goal scorers of the premier from this website.

connection = sqlite3.connect('mydatabase.db')
cursor = connection.cursor()

Delete a Table¶

# Delete A Table If Exists
_ = cursor.execute("""DROP TABLE IF EXISTS Players""")

Creating a Table¶

A table can be created using the following syntax,

CREATE TABLE MyTable (
    Column1 Datatype,
    Column2 Datatype,
    Column3 Datatype,
   ....
    );

To create Players table we can use the following SQL command

CREATE TABLE Players ( 
    Rank INTEGER PRIMARY KEY, 
    First_Name VARCHAR(20), 
    Last_Name VARCHAR(30), 
    Nationality CHAR(10), 
    Goals INTEGER,
    Birth_Date DATE
    );

# running that syntax
_ = cursor.execute("""
CREATE TABLE Players ( 
    Rank INTEGER PRIMARY KEY, 
    First_Name VARCHAR(20), 
    Last_Name VARCHAR(30), 
    Nationality CHAR(10), 
    Goals INTEGER
    );""")

Note that now, we have created a blank table.

mySQL("""select * from Players""")

Adding a column¶

This can be done using Alter function.

Alter Table MyTable
    Add Column_name Datatype

_ = cursor.execute("""
Alter Table Players
    Add Birth_Date DATE
""")

Thus,

mySQL("""select * from Players""")

Deleting a column¶

Alter Table MyTable
    Drop Column Column List

Renaming a column¶

Alter Table MyTable
  Rename PreviousName to NewName;

Rename Table¶

Alter Table MyTable
  Rename To NewTableName

Truncate Table¶

This command removed all data from a table

Truncate Table MyTable

Inserting a row¶

For example, to enter a row of a player data, we can use the following SQL syntax

INSERT INTO Players (Rank, First_Name, Last_Name, Nationality, Goals, Birth_Date)
    VALUES (Null, "Alan", "Shearer", "England", 260,"1970-08-13");

_ = cursor.execute(
"""INSERT INTO Players (Rank, First_Name, Last_Name, Nationality, Goals, Birth_Date)
    VALUES (Null, "Alan", "Shearer", "England", 260,"1970-08-13");"""
)

# Therefore
mySQL("""select * from Players""")

Inserting multiple rows¶

As you noticed, entering table rows by the previous command is not convenient, especially for entering a large number of rows. Therefore, we can enter the data as follows instead.

_ = cursor.execute("""
INSERT INTO Players (Rank, First_Name, Last_Name, Nationality, Goals, Birth_Date)
    VALUES
        (Null, "Wayne", "Rooney", "England", 208, "1985-10-24"),
        (Null, "Andrew", "Cole", "England", 187, "1971-10-15"),
        (Null, "Frank", "Lampard", "England", 177, "1978-06-20");""")
    
mySQL("""select * from Players""")

Updating a table¶

Assume that we are going to enter the following row in our table.

Last Name	First Name	Nationality	Goals	Birthdate
Thierry	Henry	France	175	1977-08-17

_ = cursor.execute("""
INSERT INTO Players (Rank, First_Name, Last_Name, Nationality, Goals, Birth_Date)
    VALUES (Null, "Thierry", "Henry", "France", 180, "1977-08-17");
    """)
mySQL("""select * from Players""")

However, the number of goals for Thierry Henry needs to be corrected to 175. Thus,

However, we entered another value by mistake for goal numbers.

Last Name	First Name	Nationality	Goals	Birthdate
Thierry	Henry	France	180	1977-08-17

In this case, we can update the table as follows

UPDATE Players
    SET goals =175
    WHERE Rank=5;

_ = cursor.execute("""
UPDATE Players
    SET goals =175
    WHERE Rank=5;
    """)

mySQL("""select * from Players""")

Copying a Table¶

Syntax:

CREATE TABLE NewTable As
    Select * From Original_Table;

_ = cursor.execute("""
Create Table PlayersCopy As
    Select * From Players;
""")

Therefore,

mySQL("""select * from PlayersCopy""")

Deleting a row¶

syntax

Delete From MyTable
    Where Condition;

For example, to delete the last entered row, we can try

_ = cursor.execute("""
Delete From PlayersCopy
    Where Last_Name="Henry";
""")

# Hence,
mySQL("""select * from PlayersCopy""")

Deleting a table¶

syntax

Drop Table TableName;

_ = cursor.execute("""
Drop Table PlayersCopy
""")

connection.commit()
connection.close()

	Rank	First_Name	Last_Name	Nationality	Goals	Birth_Date
0	1	Alan	Shearer	England	260	1970-08-13
1	2	Wayne	Rooney	England	208	1985-10-24
2	3	Andrew	Cole	England	187	1971-10-15
3	4	Frank	Lampard	England	177	1978-06-20