Learning by practicing: Beginning SQLalchemy

See this GitHub link for the full notebook. https://github.com/SecurityNik/Data-Science-and-ML/blob/main/beginning-sql-alchemy-blog.ipynb
#!/usr/bin/env python
# coding: utf-8

# In[1]:


'''
In this post, I am learning more about sqlalachemy
'''

# First up, import the sqlalchemy modules I will need need to use
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, Text, text, select, or_, and_, desc, func, case, cast, Float, DECIMAL, Boolean, insert, update, delete, Date, DateTime, ARRAY, ForeignKey

# Import datetime
from datetime import datetime

# import pandas as I will use this to importand view data
import pandas as pd


# In[2]:


# Delete the database file if it previous existed
get_ipython().system('del /f securitynik-db.sqlite')


# In[3]:


'''
Create a SQLite database and interface to it via create_engine.
As this database does not exist as yet, it will be created on the disk
using the relative path. Hence the  ///
This engine does not actually connect to the database at this time.
A connection will be made once a request has been made to perform a task
'''
securitynik_db_engine = create_engine('sqlite:///securitynik-db.sqlite', echo=True)
print(securitynik_db_engine)

''' 
Setup the metadata
Quoting from the sqlalchemy manual: "The MetaData is a registry which includes the ability to emit a limited set of schema generation commands to
the database"
'''
metadata = MetaData()
print(metadata)


# In[4]:


'''
With the engine created. Time to make a connection to the database
Since the database securitynik-db.sqlite does not exist,
the file will be created on the file system
'''
securitynik_db_connection = securitynik_db_engine.connect()
securitynik_db_connection


# In[5]:


'''
Verifying the securitynik-db.sqlite file has been created on the file system
and that it is currently empty, as no data has been written to it
'''
get_ipython().system('dir securitynik-db.sqlite')


# In[6]:


# With the file now created. Time to create some tables

# Create an employee Table
employee_table = Table('employees', metadata,
            Column('EmployeeID', Integer(), primary_key=True, nullable=False, unique=True, autoincrement=True),
            Column('FName', String(255), nullable=True),
            Column('LName', String(255), nullable=True),
            Column('Active', Boolean(), default=True, nullable=False),
            Column('Comments', String(255), default='securitynik.com employee')
            )


# In[7]:


'''
Create a blogs table
Setup the blogger_id field to link back to the EmployeeID field in the employees table
Note, could have also used foreign_key(employee_table.columns.EmployeeID to setup the foreign key
'''

blogs_table = Table('blogs', metadata,
            Column('BlogID', Integer(), primary_key=True, nullable=False, unique=True, autoincrement=True),
            Column('blogger_id', Integer(), ForeignKey('employees.EmployeeID'), nullable=False),
            Column('BlogTitle', String(255), nullable=True),
            Column('Blogger', String(255), default='Nik Alleyne', nullable=False),
            Column('Date', DateTime(), nullable=datetime.now),
            Column('URL', String(255), nullable=True),
            Column('Comments', Text(), default='Blog post created by Nik Alleyne')
            )


# In[8]:


# Create a table other
other_table = Table('other', metadata,
            Column('ID', Integer(), primary_key=True, nullable=False, unique=True, autoincrement=True),
            Column('Comments', String(255),  nullable=True)
            )


# In[9]:


# Create all the above defined tables
metadata.create_all(securitynik_db_connection)


# In[10]:


# Verifying the tables were successfully created by viewing the metadata object
metadata.tables


# In[11]:


# Taking a different view of the tables via metadata
metadata.sorted_tables


# In[12]:


'''
With the tables created time to insert data
first into the employees table.
I will first insert 1 record
At the same time, return the number of rows impacted via the rowcount
'''
securitynik_db_connection.execute(insert(employee_table).values(FName='Nik', LName='Alleyne', Active=True, Comments='Blog Author')).rowcount


# In[13]:


'''
Add an entry to the blog table
'''
securitynik_db_connection.execute(insert(blogs_table).values(blogger_id=1, BlogTitle='Beginning SQLAlchemy', URL='http://www.securitynik.com/beginning-sql-alchemy.html')).rowcount


# In[14]:


# Insert some data into the other table
securitynik_db_connection.execute(insert(other_table).values(Comments='Nothing Exciting')).rowcount


# In[15]:


'''
Now that I can assign 1 value at a time
time to insert multiple values via a list of 
dictionaries
'''
add_multiple_employees = [
        { 'FName':'S', 'LName':'Alleyne',  'Active':True, 'Comments':'Blog Author' }, 
        { 'FName':'P', 'LName':'Khan',  'Active':False, 'Comments':'Blog Admin'},
        { 'FName':'TQ', 'LName':'G', 'Active':True, 'Comments':'Blog Manager'},
        { 'FName':'T', 'LName':'A', 'Active':False, 'Comments':'Blog Author' },
        { 'FName':'D', 'LName':'P', 'Active':True, 'Comments':'Blog Maintainer' },
        { 'FName':'J', 'LName':'S', 'Active':False, 'Comments':'Blog Contributor' },
        { 'FName':'C', 'LName':'P',  'Active':True, 'Comments':'Blog Comments Admin' },
        { 'FName':'A', 'LName':'W', 'Active':False, 'Comments':'Blog Author' },
]

# With the list of dictionaries built, time to submit to the database
# At the same time, get the number of rows impacted
securitynik_db_connection.execute(insert(employee_table, add_multiple_employees)).rowcount


# In[16]:


'''
Trying another strategy to get users into the database
In this case, read data from a CSV file and push int into the datbase
First read the csv file with pandas and print the first 5 records
'''
df_employees = pd.read_csv('employees.csv', header=0, sep=',')
df_employees.head(5)


# In[17]:


'''
With the dataframe now containing the CSV data
time to take the dataframe data and push it into the SQLite database
'''
df_employees.to_sql(name='employees', con=securitynik_db_connection, if_exists='append', index=False)


# In[18]:


'''
With no errors above, it looks like all is well
Using the same strategy to add new blog entries
'''
df_blogs = pd.read_csv('blogs.csv', header=0, sep=',')
df_blogs.head(5)


# In[19]:


'''
With the dataframe now containing the CSV data
time to take the dataframe data and push it into the SQLite database
'''
df_blogs.to_sql(name='blogs', con=securitynik_db_connection, if_exists='append', index=False)


# In[20]:


'''
 With the data added to the various coluimns
 Time to now query the various tables
 Select the first 5 records from the employees table
'''
result_proxy = securitynik_db_connection.execute(select(employee_table)).fetchall()
result_proxy


# In[21]:


# How many records are there in the Employees table
len(result_proxy)


# In[22]:


# Get a sample result Key
result_proxy[0].keys()


# In[23]:


# With the result key, iterate through the results
print('EmployeeID | FName     |    LName  |     Active     |  Comments      ')
for result in result_proxy:
    print(f'{result.EmployeeID} |    {result.FName}   | {result.LName}  | { result.Active }  | {result.Comments}')


# In[24]:


'''
Building on the query, adding a where clause
'''
securitynik_db_connection.execute(select(employee_table).where(employee_table.columns.LName=='Alleyne')).fetchmany(size=5)


# In[25]:


'''
Building on the above query, taking advantage of 'and_'
to compound the query.
Leveraging both .columns and .c 
'''
securitynik_db_connection.execute(select(employee_table).where(and_(
                                                    employee_table.columns.LName=='Alleyne',
                                                    employee_table.c.FName=='Nik',
                                                    employee_table.c.Active==True))).fetchone()


# In[26]:


'''
Taking advantage of 'or_'
to compound the query.
Leveraging both .columns and .c 
'''
securitynik_db_connection.execute(select(employee_table).where(or_(
                                                    employee_table.columns.LName=='Alleyne',
                                                    employee_table.c.FName=='Nik',
                                                    employee_table.c.Active==False))).fetchmany(size=5)


# In[27]:


'''
Looking at columns in the blog table 
identif all records where the URL field is null
'''
securitynik_db_connection.execute(select(blogs_table).where(blogs_table.columns.URL==None)).fetchmany(size=5)


# In[29]:


'''
Looking for all records where the URL is not NULL in the blogs table 
'''
securitynik_db_connection.execute(select(blogs_table).where(blogs_table.columns.URL!=None)).fetchmany(size=5)


# In[32]:


'''
Finding records using Like
Looking specifically for records where the name is like kibana
Note I am ignorning the case by using iLike
'''
securitynik_db_connection.execute(select(blogs_table).where(blogs_table.columns.BlogTitle.ilike('%Kibana%'))).fetchmany(size=5)


# In[46]:


'''
Revisiting the employee table 
ordering by Employee FName
Do it descending, as in going from Z to A rather than A to Z
Limit the results to 5 records
Only return the employee first and last name
'''
securitynik_db_connection.execute(select(employee_table.columns.FName, employee_table.c.LName).order_by(desc(employee_table.columns.FName)).limit(5)).fetchall()


# In[55]:


'''
Updating records where comments is empty in the blog table
'''

securitynik_db_connection.execute(update(blogs_table).where(blogs_table.c.Comments == None).values(Comments='SecurityNik is the blogger')).rowcount


# In[57]:


# Verifying the change was made on the blog table
securitynik_db_connection.execute(select(blogs_table.columns.Comments)).fetchall()


# In[60]:


# Delete the records we just created above
securitynik_db_connection.execute(delete(blogs_table).where(blogs_table.c.Comments =='SecurityNik is the blogger')).rowcount


# In[64]:


'''
Drop the other table
'''
#other_table.drop(securitynik_db_engine)


# In[ ]:


# Drop all tables
metadata.drop_all(securitynik_db_engine)


# In[28]:


'''
References:
https://campus.datacamp.com/courses/introduction-to-relational-databases-in-python
https://www.sqlalchemy.org/library.html
https://buildmedia.readthedocs.org/media/pdf/sqlalchemy/rel_1_0/sqlalchemy.pdf
https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_quick_guide.htm
https://www.topcoder.com/thrive/articles/sqlalchemy-1-4-and-2-0-transitional-introduction
https://overiq.com/sqlalchemy-101/installing-sqlalchemy-and-connecting-to-database/


'''
Learning by practicing

Thursday, April 14, 2022

Beginning SQLalchemy

No comments:

Post a Comment
