Thursday, April 14, 2022

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
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
#!/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/


'''

1 comment:

  1. The minimal width of a bend aid is one material thickness or 1.50 mm (0.06 inch), whichever is bigger. The maximum depth for a V embossment the identical as} three times the material thickness. ALIGN PRECISION understands the crucial nature of every part we make. We make use of a range of metal-forming processes that enable us to achieve the specified shapes whereas managing stresses to the material. clear thongs Create production-ready sheet metallic designs with a strong, intuitive, browser-based resolution.

    ReplyDelete