Dec 17-23, 2020 Vikrant Patil
These notes are available online at http://notes.pipal.in/2020/arcesium_finop_batch3/module3-day5.html
© Pipal Academy LLP
Day 1 | Day 2 | Day 3 | Day 4 | Day 5
We will be using jupyter hub from http://lab.pipal.in for this training. Create a notebook with name module3-day5.ipynb for today's session. Before you start shutdown all kernels except today's notebook.
import sqlite3
conn = sqlite3.connect("database.db")# database.db is file name for this sqlite3 db
cur = conn.cursor()
import pandas as pd
df = pd.read_csv("wallet.csv")
df
df.to_sql("wallet", con=conn, if_exists="append")
results = conn.cursor().execute("select * from wallet")
for item in results:
print(item)
cur = conn.cursor()
r = cur.execute("select * from wallet where category='{}'".format("Books"))
for item in r:
print(item)
def find_transcactions(conn, cat):
q = "select * from wallet where category='{}'".format(cat)
cur = conn.cursor()
result = cur.execute(q)
return result.fetchall()
find_transcactions(conn, "Food")
def find_transcactions(conn, cat):
q = "select * from wallet where category=?"
cur = conn.cursor()
result = cur.execute(q, (cat,))
return result.fetchall()
find_transcactions(conn, "Music")
def find_transcactions(conn, cat, desc):
q = "select * from wallet where category=? and description=?"
cur = conn.cursor()
result = cur.execute(q, (cat,desc))
return result.fetchall()
find_transcactions(conn, "Music", "Amazon")
find_transcactions(conn, "Music", "Netflix")
df = pd.read_sql_query("select * from wallet", con= conn)
df
df.info()
help(pd.read_sql_query)
import time
with conn: # to lock the database
#time.sleep(10) # wait for 10 seconds
conn.execute("select * from wallet")
def insert_values(values):
conn = sqlite3.connect("database.db")
with conn:
cur = conn.cursor()
cur = cur.execute("insert into wallet values (?,?,?,?,?,?)" ,values)
conn.close()
insert_values((101,101,'2020-09-21 10:53:28.377359', "NEW","Amazon", 450.0))
conn = sqlite3.connect("database.db")
find_transcactions(conn, "NEW", "Amazon")
%%file samplecats.csv
NEW,Amazon
def read_cat_desc(filename):
with open(filename) as f:
return f.read().strip().split(",")
cat, desc = read_cat_desc("samplecats.csv")
print(cat, desc)
print("NEW", "Amazon")
find_transcactions(conn, cat, desc)
#find_transcactions(conn, "NEW", "Amazon")
q = "select * from tablre where field=?"
conn.execute(q, (30.5, ))
sqlalchemy gives nice framework which allows mapping classes and database tables
Learn to look at errors
x = 20
2x = x*20 # is it possible
x2 = x*20
x = 20
x2 = x*20
def function1(x, key):
n = len(x)
return [x[i][key] for i in range(n)]
def column(tabular, colindex):
return [row[colindex] for row in tabular]
table = [[i*j for i in range(1,6)] for j in range(1,11)]
table
column(table, 1)
column(table, 4)
function1(table, 5)
column(table, 5)
table
def transpose(tabubar):
columncount = len(tabubar[0])
return [column(tabubar, c) for c in range(columncount)]
transpose(table)
"name" in "this senetence conatins no name"
text = "this senetence conatins no name"
import re
#2020-09-21 10:
p = re.compile(r"2020-\d{2,2}-\d{2,2}")
with open("wallet.csv") as f:
for line in f:
fields = line.strip().split(",")
if p.match(fields[1]):
print(fields)
p = re.compile(r"2020-(?P<month>\d{2,2})-\d{2,2}")
with open("wallet.csv") as f:
for line in f:
fields = line.strip().split(",")
m = p.match(fields[1])
if m:
print(fields, m.groupdict()['month'])
You can refer https://docs.python.org/3/howto/regex.html for more details
%%file download.py
import sys
import requests
def download(url, filename):
resp = requests.get(url)
with open(filename, "w") as f:
f.write(resp.text)
if __name__ == "__main__":
url = sys.argv[1]
filename = sys.argv[2]
download(url, filename)