Python scripting language with a heavy emphasis on code reuse and simplicity. It is a popular language with a large and active user group. Like Perl, it has a rich library set and is popular with projects like MapReduce with Hadoop and is rapidly becoming the default language in areas like computational intelligence. One interesting feature of the Python language is, indentation acts as block delimiters. So, when copying code do not change the indentation of the code will not operate as intended. It is also a powerful language meaning it can do a lot with very little code. Below is an example of a database containing two tables, customers, and purchase order. The Customer table has customerid (the unique identifier for the customer), name (The customer’s name), and age (the customer’s age). The PurchaseOrder table has POID (the unique id for the purchase order), customerid (which refers back to the customer who made the purchase), and Purchase (what the purchase was).
Example: | ||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
SELECT
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file select.py with the following code:
import sys
import fileinput
# Loop through file and print lines
for line in fileinput.input(sys.argv[1]):
print (line)
Then run the code: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
ORDER BY
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file orderby.py with the following code:
import sys # Initialize variables listLines =[] # Load file into an array
for line in fileinput.input(sys.argv[1]): # Create a custom sort based on second field
def getCustId(listLines): # Sort the array
listLines.sort(key=getCustId) # Print arrys lines for object in listLines: Then run the code: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
WHERE
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file select_by_id.py with the following code: import sys import fileinput # Loop through file
for line in fileinput.input(sys.argv[1]): # Split line using a comma
tokens = string.split( line, , ) # If ID matches passed ID then print
if tokens[0] == sys.argv[2]: Then run the code: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
INNER JOIN
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file innerjoin.py with the following code:
import sys # Initialize variables
listB =[] # Load second file into an array to loop through
for lineB in fileinput.input(sys.argv[2]): # Loop through first file
for lineA in fileinput.input(sys.argv[1]): # Split line using a comma
tokensA = string.split( lineA, , ) # Loop through array
for object in listB: # Split line using a comma
tokensB = string.split(object, , ) #If there is a match print
if tokensA[0] == tokensB[1]: # Remove newline character with strip method
print lineA.strip() + , + object, Then run the code: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
LEFT OUTER JOIN
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file leftouterjoin.py with the following code:
import sys # Initialize variables
listB =[] # Load second file into an array to loop through
for lineB in fileinput.input(sys.argv[2]): # Loop through first file
for lineA in fileinput.input(sys.argv[1]): # Split line using a comma
tokensA = string.split( lineA, , ) # Loop through array
for object in listB: # Split line using a comma
tokensB = string.split(object, , ) #If there is a match print
if tokensA[0] == tokensB[1]: # Remove newline character with strip method
print lineA.strip() + , + object, #If there is no match print
if iFound ==0: Then run the code: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
GROUP BY
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file groupby.py with the following code:
import sys
import fileinput import string import array # Initialize variables
iCnt =1 # Load and loop through file
for lineA in fileinput.input(sys.argv[1]): # Split line using a comma
tokensA = string.split( lineA, , ) # Adjust with header and first line
if iLoop <2: if tokensA[0] == priorTokens[0]: # print last line
print priorTokens[1] + , + priorTokens[2].strip() + , Then run the code: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
UPDATE
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file update with the following code:
import sys
import fileinput import string # Loop through file
for line in fileinput.input(sys.argv[1]): # Split line using a comma
tokens = string.split( line, , ) # If ID is not passed ID then print else replace age with passed parameter
if tokens[0] != sys.argv[2]: Then run the code: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
INSERT
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file insert.py with the following code:
import sys # Loop through file and print lines
for line in fileinput.input(sys.argv[1]): # Add new line from passed arguments
print sys.argv[2] + , + sys.argv[3] + , + sys.argv[4], Then run the code: |
|
|||||||||||||||||||||||||||||||||||||||||||||||||||
DELETE
|
||||||||||||||||||||||||||||||||||||||||||||||||||||
Create the file delete.py with the following code:
import sys # Loop through file
for line in fileinput.input(sys.argv[1]): # Split line using a comma
tokens = string.split( line, , ) # If ID is not passed ID then print
if tokens[0] != sys.argv[2]: Then run the code: |
|