Page #5
- Using the embedded SQLite database with Python
- Connecting to Oracle databases with Python
- Using sys.platform
- Taking Garbage Collection into your own hands
- Largest or Smallest N items of an iterable
- Launching webpages using the webbrowser standard library module
- Fetching Environment Variables using the os module
- Finding pathnames matching a Unix-style pattern
- Using builtin class zip()
- Secure hashes using hashlib module
Using the embedded SQLite database with Python
Python ships a module called sqlite3 which enables you to work with a builtin database called SQLite3 without having to install its client application. SQLite3 is compliant with DB-API 2.0 specification with an SQL interface.
The following snippet briefly explains how to connect to an existing database, create a new database in the current directory and execute different types of statements.
>>> import sqlite3
# Use the connect() function to connect to an existing database (a .db file) in the current directory. If the specified file doesn’t exist, it will create a new file in the current directory. This method returns a Connection object which we can use later to execute DDL, DML and other types of statements.
>>> sqlite3Connection = sqlite3.connect('myTest.db')
# The execute() function of the Connection object allows us to execute statements.
## DDL: Data Definition Language: CREATE ##
>>> sqlite3Connection.execute('''CREATE TABLE EMPLOYEE
(EMP_ID INT PRIMARY KEY NOT NULL,
FIRST_NAME VARCHAR(50) NOT NULL,
LAST_NAME VARCHAR(50) NOT NULL);''')
<sqlite3.Cursor object at 0x0368C1A0>
## DDL: Data Definition Language: ALTER ##
>>> sqlite3Connection.execute('ALTER TABLE EMPLOYEE ADD COLUMN DOB DATE;')
<sqlite3.Cursor object at 0x039051A0>
## DML: Data Manipulation Language: INSERT ##
>>> sqlite3Connection.execute('''INSERT INTO EMPLOYEE
(EMP_ID, FIRST_NAME, LAST_NAME)
VALUES(1, 'Ethan', 'Hunt');''')
<sqlite3.Cursor object at 0x039051A0>
## DML: Data Manipulation Language: UPDATE ##
>>> sqlite3Connection.execute('''UPDATE EMPLOYEE
SET EMP_ID=1
WHERE FIRST_NAME = 'Ethan';''')
<sqlite3.Cursor object at 0x03D26D20>
## DML: Data Manipulation Language: DELETE ##
>>> sqlite3Connection.execute('''DELETE FROM EMPLOYEE
WHERE EMP_ID = 1;''')
<sqlite3.Cursor object at 0x03D26D60>
## DQL: Data Query Language SELECT ##
>>> cursor = sqlite3Connection.execute("SELECT * from EMPLOYEE")
>>> for record in cursor:
print(record)
(7, 'Ethan', 'Hunt')
Connecting to Oracle databases with Python
Python has a third party library called cx_Oracle, which helps you to work with Oracle databases. You can install it with pip or download it from The Cheese Shop. Expand the following snippet to view working examples of a few basic operations like:
- connecting to a database
- executing DDL (Data Definition Language) statements like CREATE, ALTER
- executing DML (Data Manipulation Language) statements like INSERT, UPDATE, DELETE
- executing DQL (Data Query Language) statement i.e. SELECT
############################### CONNECTING TO A DATABASE ######################
The connect() method of the cx_Oracle module takes a connection string which contain the database credentials in the format: user/password@host/instance. The method returns a Connection object if it succeeds in connecting. In my experience, it takes 10-15 seconds to connect.
>>> import cx_Oracle
>>> oracleConnection = cx_Oracle.connect('user/password@host/instance')
################### EXECUTING DDL (DATA DEFINITION LANGUAGE) STATEMENT: CREATE, ALTER #################
Before executing any statements, you will have to obtain a Cursor object by calling the cursor() method of the Connection object. Once you have done that, you can use the execute() method of the Cursor object to execute statements. Be sure to close the cursor when you are done by calling cursor.close().
>>> import cx_Oracle
>>> oracleConnection = cx_Oracle.connect('user/password@host/instance')
#### DDL: CREATE ####
>>> oracleCursor = oracleConnection.cursor()
>>> oracleCursor.execute('''CREATE TABLE EMPLOYEE
(EMP_ID INT PRIMARY KEY NOT NULL,
FIRST_NAME VARCHAR2(50) NOT NULL,
LAST_NAME VARCHAR2(50) NOT NULL)''')
#### DDL: ALTER ####
>>> oracleCursor.execute('ALTER TABLE EMPLOYEE ADD DOB DATE')
>>> oracleCursor.execute('ALTER TABLE EMPLOYEE DROP COLUMN DOB')
>>> oracleCursor.close()
# Do NOT put a semi-colon at the end of the statementS (e.g. NOT NULL);'''), because if you do, then Python raises cx_Oracle.DatabaseError.</p>
################ EXECUTING DML (DATA MANIPULATION LANGUAGE) STATEMENTS: INSERT, UPDATE, DELETE ###############
>>> import cx_Oracle
>>> oracleConnection = cx_Oracle.connect('user/password@host/instance')
>>> oracleCursor = oracleConnection.cursor()
>>> oracleCursor.execute('''CREATE TABLE EMPLOYEE
(EMP_ID INT PRIMARY KEY NOT NULL,
FIRST_NAME VARCHAR2(50) NOT NULL,
LAST_NAME VARCHAR2(50) NOT NULL)''')
#### DML: INSERT ####
>>> oracleCursor.execute('''INSERT INTO EMPLOYEE
(EMP_ID, FIRST_NAME, LAST_NAME)
VALUES(1, 'Ethan', 'Hunt')''')
>>> oracleCursor.execute('COMMIT')
#### DML: UPDATE ####
>>> oracleCursor.execute('''UPDATE EMPLOYEE
SET EMP_ID=7
WHERE FIRST_NAME = 'Ethan'
''')
>>> oracleCursor.execute('COMMIT')
#### DML: DELETE ####
>>> oracleCursor.execute('COMMIT')
>>> oracleCursor.execute('''DELETE FROM EMPLOYEE
WHERE EMP_ID = 7''')
>>> oracleCursor.execute('COMMIT')
>>> oracleCursor.close()
######### EXECUTING DQL (DATA QUERY LANGUAGE) STATEMENT: SELECT ###########
>>> import cx_Oracle
>>> oracleConnection = cx_Oracle.connect('user/password@host/instance')
>>> oracleCursor = oracleConnection.cursor()
>>> oracleCursor.execute('''CREATE TABLE EMPLOYEE
(EMP_ID INT PRIMARY KEY NOT NULL,
FIRST_NAME VARCHAR2(50) NOT NULL,
LAST_NAME VARCHAR2(50) NOT NULL)''')
>>> oracleCursor.execute('''INSERT INTO EMPLOYEE
(EMP_ID, FIRST_NAME, LAST_NAME)
VALUES(1, 'Ethan', 'Hunt')''')
>>> oracleCursor.execute('''INSERT INTO EMPLOYEE
(EMP_ID, FIRST_NAME, LAST_NAME)
VALUES(2, 'Tyrion', 'Lannister')''')
>>> oracleCursor.execute('COMMIT')
#### DQL: SELECT ####
>>> resultSet = oracleCursor.execute('SELECT * FROM EMPLOYEE')
>>> for record in resultSet:
print(record)
(1, 'Ethan', 'Hunt')
(2, 'Tyrion', 'Lannister')
>>> oracleCursor.close()
Using sys.platform
The builtin sys module provides a very useful attribute called platform, which can be used to execute separate code blocks in different platforms. Following are the values of this attribute in different operating systems.
| Platform | Value of sys.platform |
|---|---|
| FreeBSD | 'freebsd' |
| Linux | 'linux' |
| Windows | 'win32' |
| Windows/Cygwin | 'cygwin' |
| Mac OS X | 'darwin' |
>>> import sys
>>> sys.platform
'win32'
>>> if sys.platform.startswith('darwin'):
print("I'm on a Mac OS X!")
elif sys.platform.startswith('win32'):
print("I'm on a Windows platform!")
I'm on a Windows platform!
It is a safe bet to use the startswith() method to check platform values, since older versions of Python (less than 3.3) include the major version as well i.e. 'linux2', 'linux3'.
Taking Garbage Collection into your own hands
Garbage Collection refers to deleting objects from memory when they are no longer needed. Python implements this process using reference counts and reference cycles, and it is automatic, meaning that you don't have to worry about deleting no-longer needed objects yourself. However, Python provides an interface to its garbage collector via its standard library module called gc. This module provides, among other utilities, two functions to enable and disable the collector manually.
>>> import gc
>>> help(gc.enable)
Help on built-in function enable in module gc:
enable(...)
enable() -> None
Enable automatic garbage collection.
>>> help(gc.disable)
Help on built-in function disable in module gc:
disable(...)
disable() -> None
Disable automatic garbage collection.
Largest or Smallest N items of an iterable
Launching webpages using the webbrowser standard library module
Python's webbrowser module provides us with an interface to control the default web browser. You can use its open() function to launch webpages with provided URLs.
>>> webbrowser.open('https://www.djangospin.com/')
Fetching Environment Variables using the os module
The environment variables of your system can be read using the os module's environ attribute.
>>> import os >>> os.environ # an environ object, a dictionary-like object whose elements can be accessed using keys. >>> os.environ['PATH'] # value of PATH environment variable
Finding pathnames matching a Unix-style pattern
The glob standard library helps to find pathnames matching a Unix-style pattern, using its glob() function. You can use Unix wildcards such as *, ? and character ranges with [ ].
# Directory structure of sample Folder
│ 1ab.txt
│ a2.txt
│ a2b.txt
│ abc.txt
│ def.txt
│ picture.jpg
│ python.py
│ word.docx
└───dir1
file1underDir1.txt
>>> import glob
# The * denotes one or more characters
>>> glob.glob('*.txt') # matches files with extension 'txt'
['1ab.txt', 'a2.txt', 'a2b.txt', 'abc.txt', 'def.txt']
>>> glob.glob('*.doc*') # matches files with extension having the string 'doc' in it.
['word.docx']
# The ? denotes a single character
>>> glob.glob('??.txt') # matches text files with only two letters in its name.
['a2.txt']
# [character-range] matches any character lying in the said range.
>>> glob.glob('[a-z][0-9].txt') # matches two-character named text files with an alphabetic first character & numeric second character.
['a2.txt']
>>> glob.glob('[a-z][0-9][a-z].txt') # matches three-character named text files with an alphabetic first character, numeric second character & an alphabetic third character.
['a2b.txt']
# The '**' pattern matches any file, directory or subdirectory.
>>> glob.glob('**')
['1ab.txt', 'a2.txt', 'a2b.txt', 'abc.txt', 'def.txt', 'dir1', 'picture.jpg', 'python.py', 'word.docx']
Using builtin class zip()
The constructor of builtin class zip takes iterables as inputs amd returns an iterator of tuples of corresponding elements. The population of the iterator stops when the shortest input iterable is exhausted. Since it returns an iterator, the __next__() method is used fetch the next tuple. to Let's look at a few examples:
# TWO ITERABLES AS INPUTS
>>> zipOne = zip( [1, 2, 3, 4], [5, 6, 7, 8, 9] )
>>> zipOne.__next__()
(1, 5)
>>> zipOne.__next__()
(2, 6)
>>> zipOne.__next__()
(3, 7)
>>> zipOne.__next__()
(4, 8)
>>> zipOne.__next__()
Traceback (most recent call last):
zipOne.__next__()
StopIteration
>>> zipOne = zip( [1, 2, 3, 4], [5, 6, 7, 8, 9] )
>>> for element in zipOne:
print(element)
(1, 5)
(2, 6)
(3, 7)
(4, 8)
>>> zipOne = zip( range(5), range(5, 10) )
>>> for element in zipOne:
print(element)
(0, 5)
(1, 6)
(2, 7)
(3, 8)
(4, 9)
>>> zipOne = zip( range(5), range(5, 10) )
>>> zipOne.__next__()
(0, 5)
>>> zipOne.__next__()
(1, 6)
>>> zipOne.__next__()
(2, 7)
>>> zipOne.__next__()
(3, 8)
>>> zipOne.__next__()
(4, 9)
>>> zipOne.__next__()
Traceback (most recent call last):
zipOne.__next__()
StopIteration
# FOUR ITERABLES AS INPUT
>>> zipTwo = zip('Have', 'you', 'met', 'Ted')
>>> for element in zipTwo:
print(element)
('H', 'y', 'm', 'T')
('a', 'o', 'e', 'e')
('v', 'u', 't', 'd')
Secure hashes using hashlib module
The builtin hashlib module provides an interface to create secure hashes/message digests of algorithms such as SHA1, SHA224, SHA256, SHA384, and SHA512 & RSA’s MD5. Each of these algorithms has a corresponding class in the module, the constructors of which, along with its hexdigest() methods produces secure hashes/message digests. The input is in bytes form and output is in hexadecimal digits.
>>> import hashlib # sha1 hash >>> hashlib.sha1(b"DjangoSpin: Articles for Pythonistas").hexdigest() '8fe8bdf0a83213d9d5ff43a5d3f3d951994e18e3' # sha256 hash >>> hashlib.sha256(b"DjangoSpin: Articles for Pythonistas").hexdigest() 'ea6f0fc02c4fac9f95cf0e03a18004b87c212c473eaa4bb3e1882255f0bbbdbf' # MD5 hash >>> hashlib.md5(b"DjangoSpin: Articles for Pythonistas").hexdigest() '59af3c14b3f913f37e18a096bad723c2'
See also: 50+ Know-How(s) Every Pythonista Must Know