Python -Final
Steps in Python for database interface:
- Connect to the database and obtain handle - Execute commands using the handle - Close the database using the handle
SQLite DBMS
- It is a common database. Most used database engine. - The file format is stable, cross-platform, and backwards compatible. Open source GNU license. - It is built into Python, in mobile phones and most computers. Firefox uses SQLite.
['2', '19', '42']
>>> import re >>> x = 'My 2 favorite numbers are 19 and 42' >>> y = re.findall('[0-9]+',x) >>> print(y)
['2', '19', '42'] []
>>> import re >>> x = 'My 2 favorite numbers are 19 and 42' >>> y = re.findall('[0-9]+',x) >>> print(y) >>> y = re.findall('[AEIOU]+',x) >>> print(y)
['$10.00']
>>> import re >>> x = 'We just received $10.00 for cookies.' >>> y = re.findall('\$[0-9.]+',x) >>> print(y)
set
A __________ is an unordered collection of immutable data types but the __________ itself is mutable -are iterable, mutable and have no duplicate elements -can have multiple types (integers, strings, tuples, etc.) -cannot have duplicates -Elements in a set cannot be referenced individually and are immutable
cursor
A ____________is like a file handle. Calling ___________ is similar to a file open() command
immutable
Are sets mutable immutable?
immutable
Are tuples mutable or immutable, and list?
"import re"
Before you can use regular expressions in your program, you must import the library using ____________
Oracle, MySQL, Microsoft SQL Server, SQLite
Common Relational DBMS
HTML is a Language while HTTP is a Protocol. ... On the contrary, HTTP (Hypertext Transfer Protocol) is a protocol for transferring the hypertext pages from Web Server to Web Browser. For exchanging web pages between Server and Browser, an HTTP session is setup using protocol methods (e.g. GET, POST etc.)
Difference between HTTP vs HTML
Hypertext Markup Language
HTML
The HyperTextTransfer Protocol is the set of rules to allow browsers to retrieve web documents from servers over the Internet
HTTP
• Assumption: Database and a Table exists • Create the data in a list() • Construct a string containing the SQL command • Call the execute() method with the appropriate variables
How do you write a a database from a csv file?
re.findall()
If we actually want the matching strings to be extracted, we use _______________
Parentheses
In Fine-Tuning String Extraction ______________ are not part of the match - but they tell where to start and stop what string to extract >>> x='From [email protected] Sat Jan 5 09:14:16 2008' >>> y = re.findall('\S+@\S+',x) >>> print(y) >>> y = re.findall('^From (\S+@\S+)',x) >>> print(y)
commit() changes to the database, otherwise the delete will not be persistent
In order to make sure you delete changes to your database you need to use __________.
yes
List can be sliced, reassigned, and concatenated, can tuples?
^ $ . \S \S * *? + +? [aeiou] [^XYZ] [a-z0-9] ( )
Matches the beginning of a line Matches the end of the line Matches any character Matches whitespace Matches any non-whitespace character Repeats a character zero or more times Repeats a character zero or more times (non-greedy) Repeats a character one or more times Repeats a character one or more times (non-greedy) Matches a single character in the listed set Matches a single character not in the listed set The set of characters can include a range Indicates where string extraction is to start Indicates where string extraction is to end
add()
The __________ method adds an item to an existing set The addition is only accomplished if the item does not exist in the set
update()
The __________ method adds multiple items to an existing set • update takes an iterable and makes a unions of the set with that iterable. • Multiple datatypes can be added • Duplicate items are ignored
union()
The __________ method gives the union of all elements in the two sets -The symbol "|" can be used to form the union of sets
pop()
The __________ method removes a random element from the set (since sets are unordered) -method returns the removed element
discard()
The ____________ method removes the element if it exists and does not give an error if the element cannot be found
intersection()
The _____________ method gives the common elements in the two sets • The original sets are not modified • The symbol "&" can be used to form the intersection of sets
difference()
The ______________ method gives the unique elements in the first set as compared to the second set • The original sets are not modified • The symbol "-" can be used to form the union of sets
remove()
The ______________ method removes the element if it exists or gives an error if it does not
symmetric_difference()
The __________________ method gives the unique elements in the first set and the second set • The original sets are not modified • The symbol "^" can be used to form the _________________________ of two sets
conn.commit()
The changes are committed to the database using __________
Greedy Matching
The repeat characters (* and +) push outward in both directions (greedy) to match the largest possible string
Non-Greedy Matching
The repeat characters (* and +) push outward in both directions (greedy) to match the largest possible string
A Uniform Resource Locator (URL), colloquially termed a web address, is a reference to a web resource that specifies its location on a computer network and a mechanism for retrieving it.
URL
multiple
We can return single or multiple values from a function using a tuple datatype
hand = open('mbox.txt') for line in hand: line = line.rstrip() if line.startswith('From:') : print(line) #or import re hand = open('mbox.txt') for line in hand: line = line.rstrip() if re.search('^From:', line) : print(line)
We fine-tune what is matched by adding special characters to the string
• There is some controversy about web page scraping and some sites are a bit strict about it. • Read the Terms of Service before scraping data for commercial purposes. • Republishing copyrighted information is not allowed • Yelp Terms of Service extract:
What are the Don'ts of Web Scraping
It will create a new one
What happens if the file you are trying to create in DB4S does not exist?
•When a program or script pretends to be a browser and retrieves web pages, looks at those web pages, extracts information, and then looks at more web pages • Search engines scrape web pages - we call this "spidering the web" or "web crawling"
What is Web Scraping?
•A set of rules that all parties follow so we can predict each other's behavior •And not bump into each other-In the US, drive on the right-hand side of the road-In the UK, drive on the left-hand side of the road
What is a Protocol?
Delete a Table
What is the code doing? import sqlite3 conn = sqlite3.connect('music.sqlite') cur = conn.cursor() cur.execute('DELETE FROM Tracks WHERE Plays < 100') conn.commit() cur.close()
Add rows to a Table
What is the code doing? import sqlite3 conn = sqlite3.connect('music.sqlite') cur = conn.cursor() cur.execute('INSERT INTO Tracks (Title, Plays) VALUES (?,?)', ('Thunderstruck', 20)) conn.commit() cur.close()
Display data from a Table
What is the code doing? import sqlite3 conn = sqlite3.connect('music.sqlite') cur = conn.cursor() print("Tracks: ") cur.execute('SELECT Title, Plays FROM Tracks') for row in cur: print(row) cur.close()
['From: Using the :'] Greedy Matching
What is the output and what is this an example of? >>> import re >>> x = 'From: Using the : character' >>> y = re.findall('^F.+:', x) >>> print(y)
['From:'] Non-Greedy Matching
What is the output and what is this an example of? >>> import re >>> x = 'From: Using the : character' >>> y = re.findall('^F.+?:', x) >>> print(y)
Create a Database file and a Table with 2 columns in Python
What is this code doing? import sqlite3 conn = sqlite3.connect('music.sqlite') cur = conn.cursor() cur.execute('DROP TABLE IF EXISTS Tracks') cur.execute('CREATE TABLE Tracks (Title TEXT, Plays INTEGER)') conn.close()
['[email protected]'] ['[email protected]']
What's the output? >>> x='From [email protected] Sat Jan 5 09:14:16 2008' >>> y = re.findall('\S+@\S+',x) >>> print(y) >>> y = re.findall('^From (\S+@\S+)',x) >>> print(y)
tuple
When insert values in a table for SQLlite DB4S. The values of the row are passed as a
re.findall()
When we use ____________, it returns a list of zero or more sub-strings that match the regular expression
• Pull data - particularly social data - who links to who? • Get your own data back out of some system that has no "export capability" • Monitor a site for new information • Spider the web to make a database for a search engine
Why Scrape Data?
re.findall()
You can refine the match for _______________ and separately determine which portion of the match is to be extracted by using parentheses
re.search()
You can use ___________ to see if a string matches a regular expression
re.findall()
You can use ____________ to extract portions of a string that match your regular expression
Tuples
________ are used for heterogeneous sequence of fixed length
Lists
__________ are used for a homogeneous sequence of variable length
re.search()
______________returns a True/False depending on whether the string matches the regular expression
('Pepsi', 'Coke', '7-Up') ('Water', 'Coke', '7-Up')
a = ("Pepsi", "Coke", "7-Up") print(a) a=("Water", "Coke", "7-Up") print(a)
('Pepsi', 'Coke', '7-Up') TypeError: 'tuple' object does not support item assignment
a = ("Pepsi", "Coke", "7-Up") print(a) a[0]="Water" print(a)
2182131317312 2182131821696
a = ("Pepsi", "Coke", "7-Up") print(id(a)) a=("Water", "Coke", "7-Up") print(id(a))
['Pepsi', 'Coke', '7-Up'] ['Water', 'Coke', '7-Up']
a = ["Pepsi", "Coke", "7-Up"] print(a) a[0]="Water" print(a)
2182131303872 2182131303872
a = ["Pepsi", "Coke", "7-Up"] print(id(a)) a[0]="Water" print(id(a))
Row (or Tuple)
a set of fields that generally represents an "object" like a person or a music track
[(701, 'S.Oak', 'Arlington', 'TX')] [(701, 'S.Oak', 'Arlington', 'TX'), (211, 'S.Cooper', 'Arlington', 'TX')]
addr_list=list() addr_list.append((701,'S.Oak','Arlington','TX')) print(addr_list) addr_list.append((211,'S.Cooper','Arlington','TX')) print(addr_list)
Database
contains many tables
Table (or Relation)
contains rows and columns
CREATE TABLE
creates a new table with the specified fields
(1, 2, 3) <class 'tuple'>
def myoutput(i): if i==0: return(1,2,3) else: return('a','b','c') print(myoutput(0)) print(type(myoutput(1)))
DROP TABLE
deletes the table and all its contents
tuple
is a sequence of values • Any value can be stored in a tuple • Values are indexed by integers like lists • Comma separated list of values
execute()
method is used to execute commands in the Database using SQL
3
mylist=[frozenset({'a','b'}), frozenset({'c','d'})] mydict1=dict() count=1 for item in mylist: mydict1[item]=count count=count+1 print(count)
frozenset({'d', 'c'})
mylist=[frozenset({'a','b'}), frozenset({'c','d'})] mydict1=dict() count=1 for item in mylist: mydict1[item]=count count=count+1 print(item)
{frozenset({'a', 'b'}): 1, frozenset({'d', 'c'}): 2}
mylist=[frozenset({'a','b'}), frozenset({'c','d'})] mydict1=dict() count=1 for item in mylist: mydict1[item]=count count=count+1 print(mydict1)
<class 'set'> <class 'set'> {1, (1, 2), 'hello'} <class 'dict'> <class 'set'> {1, 2, 3, 4}
myset = {"a", "b", "c"} print(type(myset)) myset = set(["a", "b","c"]) print(type(myset)) myset = {1,'hello',(1,2)} print(myset) mynewset={} print(type(mynewset)) myrealset=set() print(type(myrealset)) myset = {1,2,3,4,3,2} print(myset)
{'a', 'b'} {'a', 'c', 'b'} {'d', 'c', 'e'} {'a', 'b'}
myset1={'a', 'b', 'c'} myset2={'c','d','e'} print(myset1.difference(myset2)) print(myset1, myset2) print(myset1-myset2)
{'c'} {'a', 'c', 'b'} {'d', 'c', 'e'} {'c'}
myset1={'a', 'b', 'c'} myset2={'c','d','e'} print(myset1.intersection(myset2)) print(myset1, myset2) print(myset1&myset2)
{'d', 'b', 'a', 'e'} {'a', 'c', 'b'} {'d', 'c', 'e'} {'d', 'b', 'a', 'e'}
myset1={'a', 'b', 'c'} myset2={'c','d','e'} print(myset1.symmetric_difference(myset2)) print(myset1, myset2) print(myset1^myset2)
{'d', 'a', 'e', 'c', 'b'} {'a', 'c', 'b'} {'d', 'e'} {'d', 'a', 'e', 'c', 'b'}
myset1={'a', 'b', 'c'} myset2={'d','e'} print(myset1.union(myset2)) print(myset1) print(myset2) print(myset1|myset2)
{'a', 'c', 'b'} frozenset({'a', 'c', 'b'})
myset1={'a','b','c'} print(myset1) myfrozenset1=frozenset(myset1) print(myfrozenset1)
True False False True
myset1={'a','b'} myset2={'a','b','c','d'} print(myset1<myset2) print(myset1>myset2) print(myset2<myset1) print(myset2>myset1)
{'d', 'b', 'a', 'e', 'c'} {'d', 'b', 'a', 'c'} {'d', 'b', 'a', 'c'}
myset={'a', 'b', 'c','d','e'} print(myset) myset.discard('e') print(myset) myset.discard('e') print(myset)
{'d', 'b', 'a', 'e', 'c'} {'d', 'b', 'a', 'c'} KeyError: 'e'
myset={'a', 'b', 'c','d','e'} print(myset) myset.remove('e') print(myset) myset.remove('e') print(myset)
{'d', 'b', 'a', 'e', 'c'} {'b', 'a', 'e', 'c'} d {'a', 'e', 'c'} b
myset={'a', 'b', 'c','d','e'} print(myset) x=myset.pop() print(myset) print(x) y=myset.pop() print(myset) print(y)
{'d', 'c', 'b', 'a', 'y', 'e', 'x'} {'h', 'b', 'o', 'a', 'e', 'l', 'c'} {'a', 'c', 'b'} {'world', 'b', 'a', 'hello', 'c'} {('hello', 'world'), 'a', 'c', 'b'}
myset={'a', 'b', 'c'} myset.update('dx','ey') print(myset) myset={'a', 'b', 'c'} myset.update('hello') print(myset) myset={'a', 'b', 'c'} print(myset) myset.update(['hello','world']) print(myset) myset={'a', 'b', 'c'} myset.update([('hello','world')]) print(myset)
{'a', 'c', 'b'} {'d', 'a', 'c', 'b'} {'d', 'a', 'c', 'b'}
myset={'a', 'b', 'c'} print(myset) myset.add('d') print(myset) myset.add('d') print(myset)
{'a', 'c', 'b'} {'d', 'b', 'a', 'e', 'c'} {'d', 1, 2, 3, 'b', 'a', 'e', 'c'} TypeError: 'int' object is not iterable
myset={'a', 'b', 'c'} print(myset) myset.update('d','e') print(myset) myset.update([1,2,3]) print(myset) myset.update(5,6) myset.update([5,6],{7,8},(9,10)) print(myset)
Column (or Attribute or Field)
one of possibly many elements of data corresponding to the object represented by the row
(1, 2, 3, 1, 2, 3) (1, 2, 3, 1)
t=(1,2,3) newtuple=t+t print(newtuple) newtuple=t+t[0:1] print(newtuple)
(1,) (1, 2) (1, 2, 3)
t=(1,2,3) print(t[0:1]) print(t[:2]) print(t[:])
('h', 'e', 'l', 'l', 'o') (1, 2, 3) (1, 2, 3)
t=tuple('hello') print(t) t=tuple([1,2,3]) print(t) t=tuple((1,2,3)) print(t)
Database Browser for SQLite (DB4S)
• DB4S is a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite. • Uses a familiar spreadsheet-like interface • Does not require familiarity with SQL commands, simple point and click interface
frozenset "freezes" "freeze"
• Note that a set is mutable • The ____________ method ___________ a set and makes it immutable -A set cannot be used as a dictionary key (since it is mutable) -One can ________ a set and use it as a dictionary key
< and <= > and >=
• The __________ operators test if one set is a subset of the other set • The __________ operators test if one set is a superset of the other set
Select
• The ______________ command is used to retrieve rows from a Table • After the ___________ command is executed the cursor is like a file handle that we can loop through to retrieve the values
Python Interface for SQL
• Using the Structured Query Language (SQL), all the DB4S commands and more can be executed • Python provides an interface into a SQLite database file through SQL commands
