Python Virtual Training For Arcesium - Module III

Oct 11-15, 2021 Vikrant Patil

These notes are available online at https://notes.pipal.in/2021/arcesium_finop_batch2/

© Pipal Academy LLP

Day 1 | Day 2 | Day 3 | Day 4 | Day 5

We will be using jupyter hub from https://lab2.pipal.in for this training.

create a notebook with name module3-day4

Databases

What you need to have is databse connector to connect to any databse server. Here we will be using sqlite3

import sqlite3

conn = sqlite3.connect("data.db") # this will connect to sqlite database which is stored in a file with data.db
# if data.db does not exists it will be created

cur = conn.cursor()
cur.execute("create table person (name varchar(100), email varchar(100))") # table is created
conn.commit()

def insert_person(name, email, conn):
    cur = conn.cursor()
    cur.execute(f"insert into person (name, email) values('{name}', '{email}')")
    conn.commit() # make sure to commit , otherwise database is gauranteed to be synched

conn.close() # this will close connection to database

conn = sqlite3.connect("data.db")
insert_person("alice", "alice@lewis.caroll", conn)
insert_person("alex", "alex@newyork.zoo", conn)

def find_person(name, conn):
    query = f"select * from person where name='{name}'" # this is not very good way of executing query
    cur = conn.cursor()
    results = cur.execute(query)
    return results

r = find_person("alex", conn)

r

<sqlite3.Cursor at 0x7f2bc9765960>

r.fetchall() # meaning give all rows as list of tuples

[('alex', 'alex@newyork.zoo')]

r = find_person("alice", conn)
r.fetchone() # will get only one row at a time , so it will be tuple

('alice', 'alice@lewis.caroll')

insert_person("alice", "alice@wonder.land", conn)

find_person("alice", conn).fetchall()

[('alice', 'alice@lewis.caroll'), ('alice', 'alice@wonder.land')]

r = find_person("alice", conn)

r.fetchone()

('alice', 'alice@lewis.caroll')

r.fetchone()

('alice', 'alice@wonder.land')

r.fetchone()

for item in find_person("alice", conn):
    print(item)

('alice', 'alice@lewis.caroll')
('alice', 'alice@wonder.land')

r = conn.cursor().execute(f"insert into person (name, email) values('vikrant', 'vikrant@xyz.in')")

r

<sqlite3.Cursor at 0x7f2bc8e010a0>

r.fetchall()

[]

(2, 3)

(2, 3)

(1,) # tuple with single element in it

(1,)

def find_person_(name, conn):
    q = "select * from person where name=?"
    cur = conn.cursor()
    return cur.execute(q, (name,)).fetchall()
    

find_person_("alice", conn)

[('alice', 'alice@lewis.caroll'), ('alice', 'alice@wonder.land')]

API

• WEB API -> get, post using requests package
• python API -> it means you need to install the package/library in your virtual environment. then use python functions/classes to fetch data

def find_person_(name, conn):
    q = "select * from person where name=?"
    cur = conn.cursor()
    return cur.execute(q, (name,)).fetchall()
    

q = "select * from person where name=? and email=?" # there are two '?'
r = conn.cursor().execute(q, ("alice", "alice@wonder.land"))
r.fetchall()

[('alice', 'alice@wonder.land')]

conn.close()

pandas and databases

import pandas as pd

conn = sqlite3.connect("wallet.db")

wallet = pd.read_csv("wallet.csv", index_col=0)

wallet

	date	category	description	debit
0	2021-03-07 14:53:28.377359	Music	Amazon	421.207327
1	2020-10-08 09:53:28.377359	Food	Swiggy	328.440080
2	2021-02-23 09:53:28.377359	Books	Amazon	244.679437
3	2020-11-01 14:53:28.377359	Utility	Phone	222.756318
4	2021-06-05 13:53:28.377359	Books	Flipcart	494.128492
...	...	...	...	...
95	2021-07-19 13:53:28.377359	Utility	Phone	388.671213
96	2021-01-12 19:53:28.377359	Books	Flipcart	467.554562
97	2021-03-25 11:53:28.377359	Utility	Phone	320.789434
98	2021-05-13 15:53:28.377359	Travel	Taxi	442.096469
99	2020-10-11 16:53:28.377359	Food	Hotel	100.455501

100 rows × 4 columns

wallet.to_sql("wallet", con=conn, if_exists="append")

r = conn.cursor().execute("select * from wallet")

for item in r:
    print(item)

(0, '2021-03-07 14:53:28.377359', 'Music', 'Amazon', 421.2073272347991)
(1, '2020-10-08 09:53:28.377359', 'Food', 'Swiggy', 328.4400802428426)
(2, '2021-02-23 09:53:28.377359', 'Books', 'Amazon', 244.67943701511356)
(3, '2020-11-01 14:53:28.377359', 'Utility', 'Phone', 222.75631758052768)
(4, '2021-06-05 13:53:28.377359', 'Books', 'Flipcart', 494.1284923793595)
(5, '2021-07-28 19:53:28.377359', 'Utility', 'Electricity', 219.9417113096841)
(6, '2021-04-16 11:53:28.377359', 'Books', 'Amazon Kindle', 270.32259514795845)
(7, '2021-02-15 10:53:28.377359', 'Food', 'Zomato', 457.1831036346536)
(8, '2021-08-10 19:53:28.377359', 'Utility', 'Phone', 151.4963725994779)
(9, '2020-11-29 14:53:28.377359', 'Travel', 'Auto', 443.61888423247854)
(10, '2021-06-15 13:53:28.377359', 'Travel', 'Metro', 328.1754210974373)
(11, '2021-07-24 13:53:28.377359', 'Food', 'Zomato', 434.4954675355444)
(12, '2021-07-24 14:53:28.377359', 'Music', 'Amazon', 329.5360031897569)
(13, '2021-06-06 10:53:28.377359', 'Utility', 'Phone', 154.04494918166588)
(14, '2021-06-09 13:53:28.377359', 'Travel', 'Taxi', 485.2977429821982)
(15, '2021-08-24 17:53:28.377359', 'Food', 'Zomato', 262.9439932340398)
(16, '2021-03-05 19:53:28.377359', 'Utility', 'Phone', 390.31687619327926)
(17, '2021-04-17 18:53:28.377359', 'Utility', 'Electricity', 316.87867542466364)
(18, '2021-05-08 15:53:28.377359', 'Travel', 'Auto', 433.8224042777937)
(19, '2021-05-16 10:53:28.377359', 'Books', 'Flipcart', 109.32590886550068)
(20, '2020-10-12 18:53:28.377359', 'Travel', 'Auto', 365.9218082537661)
(21, '2021-01-04 19:53:28.377359', 'Travel', 'Metro', 329.0973715025852)
(22, '2021-06-24 15:53:28.377359', 'Food', 'Zomato', 489.14348305222535)
(23, '2020-12-11 10:53:28.377359', 'Music', 'Netflix', 354.9402409919817)
(24, '2021-05-31 11:53:28.377359', 'Books', 'Amazon', 498.1004955046106)
(25, '2021-05-21 14:53:28.377359', 'Food', 'Hotel', 483.315863517772)
(26, '2020-08-26 15:53:28.377359', 'Books', 'Amazon Kindle', 138.806577801854)
(27, '2021-05-01 15:53:28.377359', 'Utility', 'Electricity', 103.68079074846584)
(28, '2020-12-14 15:53:28.377359', 'Utility', 'Phone', 358.4599327957656)
(29, '2021-06-20 10:53:28.377359', 'Utility', 'Electricity', 184.5577284049955)
(30, '2020-09-15 18:53:28.377359', 'Food', 'Swiggy', 203.52923978943267)
(31, '2020-09-25 11:53:28.377359', 'Books', 'Flipcart', 246.503527384528)
(32, '2021-06-23 11:53:28.377359', 'Food', 'Zomato', 345.0304360814152)
(33, '2021-05-14 18:53:28.377359', 'Food', 'Hotel', 449.24802955761743)
(34, '2021-05-14 10:53:28.377359', 'Utility', 'Phone', 499.8581815222449)
(35, '2021-02-18 18:53:28.377359', 'Travel', 'Metro', 441.6021430011205)
(36, '2020-12-10 10:53:28.377359', 'Travel', 'Auto', 472.94143917262176)
(37, '2021-04-18 16:53:28.377359', 'Music', 'Amazon', 266.0690783774673)
(38, '2021-08-15 10:53:28.377359', 'Travel', 'Auto', 494.12439940565713)
(39, '2021-05-17 17:53:28.377359', 'Food', 'Swiggy', 112.33316019807457)
(40, '2021-07-19 12:53:28.377359', 'Food', 'Swiggy', 291.54598801930536)
(41, '2021-02-20 19:53:28.377359', 'Utility', 'Phone', 425.18719068071806)
(42, '2021-08-22 17:53:28.377359', 'Food', 'Hotel', 210.25626950078566)
(43, '2020-09-21 12:53:28.377359', 'Utility', 'Phone', 486.0339327616073)
(44, '2020-12-26 19:53:28.377359', 'Utility', 'Electricity', 257.92759337085425)
(45, '2021-05-27 16:53:28.377359', 'Utility', 'Electricity', 154.74287259516657)
(46, '2021-05-15 15:53:28.377359', 'Utility', 'Electricity', 359.3249716537848)
(47, '2020-10-28 10:53:28.377359', 'Books', 'Flipcart', 310.408610004679)
(48, '2021-08-23 17:53:28.377359', 'Utility', 'Electricity', 310.05840961423314)
(49, '2021-03-16 09:53:28.377359', 'Music', 'spotify', 232.30340219121135)
(50, '2020-12-24 11:53:28.377359', 'Food', 'Zomato', 463.0018749263554)
(51, '2020-12-22 17:53:28.377359', 'Food', 'Zomato', 331.22702332837093)
(52, '2021-03-26 09:53:28.377359', 'Travel', 'Taxi', 403.61007013419334)
(53, '2021-01-27 09:53:28.377359', 'Utility', 'Electricity', 183.1866624101276)
(54, '2020-11-16 10:53:28.377359', 'Music', 'spotify', 160.81754340768396)
(55, '2021-01-21 19:53:28.377359', 'Books', 'Flipcart', 423.74970808720553)
(56, '2021-05-19 18:53:28.377359', 'Utility', 'Phone', 319.34287626846185)
(57, '2021-07-15 15:53:28.377359', 'Utility', 'Phone', 279.6090437716363)
(58, '2021-05-20 10:53:28.377359', 'Food', 'Hotel', 255.8710346734312)
(59, '2020-08-28 11:53:28.377359', 'Food', 'Swiggy', 208.2329120852039)
(60, '2021-01-17 11:53:28.377359', 'Utility', 'Electricity', 382.5195101154448)
(61, '2021-02-25 13:53:28.377359', 'Food', 'Hotel', 124.65827844174062)
(62, '2021-01-27 19:53:28.377359', 'Books', 'Amazon Kindle', 497.7708601564023)
(63, '2021-05-10 11:53:28.377359', 'Travel', 'Taxi', 355.9890502253258)
(64, '2021-01-31 14:53:28.377359', 'Food', 'Zomato', 232.2223798622789)
(65, '2020-10-23 18:53:28.377359', 'Music', 'Netflix', 188.7487426895118)
(66, '2020-10-09 16:53:28.377359', 'Food', 'Swiggy', 263.9577700340145)
(67, '2021-07-31 14:53:28.377359', 'Music', 'Netflix', 324.786916846731)
(68, '2020-08-26 09:53:28.377359', 'Travel', 'Taxi', 279.1478844739421)
(69, '2020-10-10 15:53:28.377359', 'Utility', 'Electricity', 300.5246204193512)
(70, '2021-08-17 13:53:28.377359', 'Utility', 'Phone', 125.22977317126335)
(71, '2021-03-30 12:53:28.377359', 'Food', 'Swiggy', 245.36050838040904)
(72, '2021-06-30 18:53:28.377359', 'Books', 'Amazon', 294.6628689900488)
(73, '2021-08-15 17:53:28.377359', 'Travel', 'Metro', 117.58872931045572)
(74, '2021-03-20 11:53:28.377359', 'Travel', 'Taxi', 303.0554209852045)
(75, '2021-03-03 12:53:28.377359', 'Food', 'Hotel', 425.62529099481486)
(76, '2020-11-17 09:53:28.377359', 'Music', 'Netflix', 197.5346000167895)
(77, '2021-01-18 14:53:28.377359', 'Books', 'Amazon Kindle', 482.1523430204321)
(78, '2020-09-09 16:53:28.377359', 'Music', 'spotify', 415.3728938035302)
(79, '2021-08-17 09:53:28.377359', 'Music', 'Netflix', 321.7634156544651)
(80, '2021-02-17 09:53:28.377359', 'Food', 'Swiggy', 283.09570727160764)
(81, '2020-10-29 16:53:28.377359', 'Food', 'Hotel', 470.0809953992362)
(82, '2020-09-22 09:53:28.377359', 'Music', 'spotify', 411.14270120842224)
(83, '2021-03-18 09:53:28.377359', 'Books', 'Flipcart', 451.5844070294999)
(84, '2020-09-21 10:53:28.377359', 'Music', 'Netflix', 158.7936457269333)
(85, '2021-01-12 09:53:28.377359', 'Music', 'Amazon', 130.37490757527002)
(86, '2021-05-07 16:53:28.377359', 'Food', 'Zomato', 198.45067179263802)
(87, '2021-05-19 15:53:28.377359', 'Food', 'Zomato', 378.82064134052473)
(88, '2021-04-18 09:53:28.377359', 'Utility', 'Phone', 124.22124784445779)
(89, '2021-04-12 14:53:28.377359', 'Music', 'Amazon', 218.487173429263)
(90, '2020-12-01 14:53:28.377359', 'Music', 'Amazon', 101.57327588889416)
(91, '2021-01-22 17:53:28.377359', 'Food', 'Hotel', 232.66346838787223)
(92, '2021-01-12 19:53:28.377359', 'Travel', 'Taxi', 356.84263798863265)
(93, '2021-01-11 09:53:28.377359', 'Utility', 'Electricity', 111.72080867898062)
(94, '2021-01-04 13:53:28.377359', 'Utility', 'Phone', 431.1855366816298)
(95, '2021-07-19 13:53:28.377359', 'Utility', 'Phone', 388.67121323884214)
(96, '2021-01-12 19:53:28.377359', 'Books', 'Flipcart', 467.5545618966052)
(97, '2021-03-25 11:53:28.377359', 'Utility', 'Phone', 320.78943360123816)
(98, '2021-05-13 15:53:28.377359', 'Travel', 'Taxi', 442.0964693975505)
(99, '2020-10-11 16:53:28.377359', 'Food', 'Hotel', 100.45550129902664)

wallet['date'] = pd.to_datetime(wallet['date'])

wallet.to_sql("wallet", con=conn, if_exists="replace")

r = conn.cursor().execute("select * from wallet")

for item in r:
    print(item)

(0, '2021-03-07 14:53:28.377359', 'Music', 'Amazon', 421.2073272347991)
(1, '2020-10-08 09:53:28.377359', 'Food', 'Swiggy', 328.4400802428426)
(2, '2021-02-23 09:53:28.377359', 'Books', 'Amazon', 244.67943701511356)
(3, '2020-11-01 14:53:28.377359', 'Utility', 'Phone', 222.75631758052768)
(4, '2021-06-05 13:53:28.377359', 'Books', 'Flipcart', 494.1284923793595)
(5, '2021-07-28 19:53:28.377359', 'Utility', 'Electricity', 219.9417113096841)
(6, '2021-04-16 11:53:28.377359', 'Books', 'Amazon Kindle', 270.32259514795845)
(7, '2021-02-15 10:53:28.377359', 'Food', 'Zomato', 457.1831036346536)
(8, '2021-08-10 19:53:28.377359', 'Utility', 'Phone', 151.4963725994779)
(9, '2020-11-29 14:53:28.377359', 'Travel', 'Auto', 443.61888423247854)
(10, '2021-06-15 13:53:28.377359', 'Travel', 'Metro', 328.1754210974373)
(11, '2021-07-24 13:53:28.377359', 'Food', 'Zomato', 434.4954675355444)
(12, '2021-07-24 14:53:28.377359', 'Music', 'Amazon', 329.5360031897569)
(13, '2021-06-06 10:53:28.377359', 'Utility', 'Phone', 154.04494918166588)
(14, '2021-06-09 13:53:28.377359', 'Travel', 'Taxi', 485.2977429821982)
(15, '2021-08-24 17:53:28.377359', 'Food', 'Zomato', 262.9439932340398)
(16, '2021-03-05 19:53:28.377359', 'Utility', 'Phone', 390.31687619327926)
(17, '2021-04-17 18:53:28.377359', 'Utility', 'Electricity', 316.87867542466364)
(18, '2021-05-08 15:53:28.377359', 'Travel', 'Auto', 433.8224042777937)
(19, '2021-05-16 10:53:28.377359', 'Books', 'Flipcart', 109.32590886550068)
(20, '2020-10-12 18:53:28.377359', 'Travel', 'Auto', 365.9218082537661)
(21, '2021-01-04 19:53:28.377359', 'Travel', 'Metro', 329.0973715025852)
(22, '2021-06-24 15:53:28.377359', 'Food', 'Zomato', 489.14348305222535)
(23, '2020-12-11 10:53:28.377359', 'Music', 'Netflix', 354.9402409919817)
(24, '2021-05-31 11:53:28.377359', 'Books', 'Amazon', 498.1004955046106)
(25, '2021-05-21 14:53:28.377359', 'Food', 'Hotel', 483.315863517772)
(26, '2020-08-26 15:53:28.377359', 'Books', 'Amazon Kindle', 138.806577801854)
(27, '2021-05-01 15:53:28.377359', 'Utility', 'Electricity', 103.68079074846584)
(28, '2020-12-14 15:53:28.377359', 'Utility', 'Phone', 358.4599327957656)
(29, '2021-06-20 10:53:28.377359', 'Utility', 'Electricity', 184.5577284049955)
(30, '2020-09-15 18:53:28.377359', 'Food', 'Swiggy', 203.52923978943267)
(31, '2020-09-25 11:53:28.377359', 'Books', 'Flipcart', 246.503527384528)
(32, '2021-06-23 11:53:28.377359', 'Food', 'Zomato', 345.0304360814152)
(33, '2021-05-14 18:53:28.377359', 'Food', 'Hotel', 449.24802955761743)
(34, '2021-05-14 10:53:28.377359', 'Utility', 'Phone', 499.8581815222449)
(35, '2021-02-18 18:53:28.377359', 'Travel', 'Metro', 441.6021430011205)
(36, '2020-12-10 10:53:28.377359', 'Travel', 'Auto', 472.94143917262176)
(37, '2021-04-18 16:53:28.377359', 'Music', 'Amazon', 266.0690783774673)
(38, '2021-08-15 10:53:28.377359', 'Travel', 'Auto', 494.12439940565713)
(39, '2021-05-17 17:53:28.377359', 'Food', 'Swiggy', 112.33316019807457)
(40, '2021-07-19 12:53:28.377359', 'Food', 'Swiggy', 291.54598801930536)
(41, '2021-02-20 19:53:28.377359', 'Utility', 'Phone', 425.18719068071806)
(42, '2021-08-22 17:53:28.377359', 'Food', 'Hotel', 210.25626950078566)
(43, '2020-09-21 12:53:28.377359', 'Utility', 'Phone', 486.0339327616073)
(44, '2020-12-26 19:53:28.377359', 'Utility', 'Electricity', 257.92759337085425)
(45, '2021-05-27 16:53:28.377359', 'Utility', 'Electricity', 154.74287259516657)
(46, '2021-05-15 15:53:28.377359', 'Utility', 'Electricity', 359.3249716537848)
(47, '2020-10-28 10:53:28.377359', 'Books', 'Flipcart', 310.408610004679)
(48, '2021-08-23 17:53:28.377359', 'Utility', 'Electricity', 310.05840961423314)
(49, '2021-03-16 09:53:28.377359', 'Music', 'spotify', 232.30340219121135)
(50, '2020-12-24 11:53:28.377359', 'Food', 'Zomato', 463.0018749263554)
(51, '2020-12-22 17:53:28.377359', 'Food', 'Zomato', 331.22702332837093)
(52, '2021-03-26 09:53:28.377359', 'Travel', 'Taxi', 403.61007013419334)
(53, '2021-01-27 09:53:28.377359', 'Utility', 'Electricity', 183.1866624101276)
(54, '2020-11-16 10:53:28.377359', 'Music', 'spotify', 160.81754340768396)
(55, '2021-01-21 19:53:28.377359', 'Books', 'Flipcart', 423.74970808720553)
(56, '2021-05-19 18:53:28.377359', 'Utility', 'Phone', 319.34287626846185)
(57, '2021-07-15 15:53:28.377359', 'Utility', 'Phone', 279.6090437716363)
(58, '2021-05-20 10:53:28.377359', 'Food', 'Hotel', 255.8710346734312)
(59, '2020-08-28 11:53:28.377359', 'Food', 'Swiggy', 208.2329120852039)
(60, '2021-01-17 11:53:28.377359', 'Utility', 'Electricity', 382.5195101154448)
(61, '2021-02-25 13:53:28.377359', 'Food', 'Hotel', 124.65827844174062)
(62, '2021-01-27 19:53:28.377359', 'Books', 'Amazon Kindle', 497.7708601564023)
(63, '2021-05-10 11:53:28.377359', 'Travel', 'Taxi', 355.9890502253258)
(64, '2021-01-31 14:53:28.377359', 'Food', 'Zomato', 232.2223798622789)
(65, '2020-10-23 18:53:28.377359', 'Music', 'Netflix', 188.7487426895118)
(66, '2020-10-09 16:53:28.377359', 'Food', 'Swiggy', 263.9577700340145)
(67, '2021-07-31 14:53:28.377359', 'Music', 'Netflix', 324.786916846731)
(68, '2020-08-26 09:53:28.377359', 'Travel', 'Taxi', 279.1478844739421)
(69, '2020-10-10 15:53:28.377359', 'Utility', 'Electricity', 300.5246204193512)
(70, '2021-08-17 13:53:28.377359', 'Utility', 'Phone', 125.22977317126335)
(71, '2021-03-30 12:53:28.377359', 'Food', 'Swiggy', 245.36050838040904)
(72, '2021-06-30 18:53:28.377359', 'Books', 'Amazon', 294.6628689900488)
(73, '2021-08-15 17:53:28.377359', 'Travel', 'Metro', 117.58872931045572)
(74, '2021-03-20 11:53:28.377359', 'Travel', 'Taxi', 303.0554209852045)
(75, '2021-03-03 12:53:28.377359', 'Food', 'Hotel', 425.62529099481486)
(76, '2020-11-17 09:53:28.377359', 'Music', 'Netflix', 197.5346000167895)
(77, '2021-01-18 14:53:28.377359', 'Books', 'Amazon Kindle', 482.1523430204321)
(78, '2020-09-09 16:53:28.377359', 'Music', 'spotify', 415.3728938035302)
(79, '2021-08-17 09:53:28.377359', 'Music', 'Netflix', 321.7634156544651)
(80, '2021-02-17 09:53:28.377359', 'Food', 'Swiggy', 283.09570727160764)
(81, '2020-10-29 16:53:28.377359', 'Food', 'Hotel', 470.0809953992362)
(82, '2020-09-22 09:53:28.377359', 'Music', 'spotify', 411.14270120842224)
(83, '2021-03-18 09:53:28.377359', 'Books', 'Flipcart', 451.5844070294999)
(84, '2020-09-21 10:53:28.377359', 'Music', 'Netflix', 158.7936457269333)
(85, '2021-01-12 09:53:28.377359', 'Music', 'Amazon', 130.37490757527002)
(86, '2021-05-07 16:53:28.377359', 'Food', 'Zomato', 198.45067179263802)
(87, '2021-05-19 15:53:28.377359', 'Food', 'Zomato', 378.82064134052473)
(88, '2021-04-18 09:53:28.377359', 'Utility', 'Phone', 124.22124784445779)
(89, '2021-04-12 14:53:28.377359', 'Music', 'Amazon', 218.487173429263)
(90, '2020-12-01 14:53:28.377359', 'Music', 'Amazon', 101.57327588889416)
(91, '2021-01-22 17:53:28.377359', 'Food', 'Hotel', 232.66346838787223)
(92, '2021-01-12 19:53:28.377359', 'Travel', 'Taxi', 356.84263798863265)
(93, '2021-01-11 09:53:28.377359', 'Utility', 'Electricity', 111.72080867898062)
(94, '2021-01-04 13:53:28.377359', 'Utility', 'Phone', 431.1855366816298)
(95, '2021-07-19 13:53:28.377359', 'Utility', 'Phone', 388.67121323884214)
(96, '2021-01-12 19:53:28.377359', 'Books', 'Flipcart', 467.5545618966052)
(97, '2021-03-25 11:53:28.377359', 'Utility', 'Phone', 320.78943360123816)
(98, '2021-05-13 15:53:28.377359', 'Travel', 'Taxi', 442.0964693975505)
(99, '2020-10-11 16:53:28.377359', 'Food', 'Hotel', 100.45550129902664)

Microsoft sql server

import pyodbc # you will have to install this library
cnxn = pyodbc.connect(r'Driver={SQL Server};Server=.\SQLEXPRESS;Database=myDB;Trusted_Connection=yes;')
cursor = cnxn.cursor()
cursor.execute("SELECT LastName FROM myContacts")
while 1:
    row = cursor.fetchone()
    if not row:
        break
    print(row.LastName)
cnxn.close()

---------------------------------------------------------------------------
Error                                     Traceback (most recent call last)
<ipython-input-46-c213123b307b> in <module>
      1 import pyodbc
----> 2 cnxn = pyodbc.connect(r'Driver={SQL Server};Server=.\SQLEXPRESS;Database=myDB;Trusted_Connection=yes;')
      3 cursor = cnxn.cursor()
      4 cursor.execute("SELECT LastName FROM myContacts")
      5 while 1:

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)")

mysql server

sample code from web

import mysql.connector

#establishing the connection
conn = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='mydb')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Executing an MYSQL function using the execute() method
cursor.execute("SELECT DATABASE()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()
print("Connection established to: ",data)

#Closing the connection
conn.close()

reading from db using pandas

pd.read_sql_query("select * from wallet", con= conn)

	index	date	category	description	debit
0	0	2021-03-07 14:53:28.377359	Music	Amazon	421.207327
1	1	2020-10-08 09:53:28.377359	Food	Swiggy	328.440080
2	2	2021-02-23 09:53:28.377359	Books	Amazon	244.679437
3	3	2020-11-01 14:53:28.377359	Utility	Phone	222.756318
4	4	2021-06-05 13:53:28.377359	Books	Flipcart	494.128492
...	...	...	...	...	...
95	95	2021-07-19 13:53:28.377359	Utility	Phone	388.671213
96	96	2021-01-12 19:53:28.377359	Books	Flipcart	467.554562
97	97	2021-03-25 11:53:28.377359	Utility	Phone	320.789434
98	98	2021-05-13 15:53:28.377359	Travel	Taxi	442.096469
99	99	2020-10-11 16:53:28.377359	Food	Hotel	100.455501

100 rows × 5 columns

c = sqlite3.connect("data.db")
pd.read_sql_query("select * from person", con=c)

	name	email
0	alice	alice@lewis.caroll
1	alex	alex@newyork.zoo
2	alice	alice@wonder.land

regular expressions

import re 

lines = """t
tt
ttt
tttt
ttttt


"""

pattern_single = re.compile(".")

for line in lines.split("\n"):
    if pattern_single.match(line):
        print(line)

t
tt
ttt
tttt
ttttt

pattern_only_one = re.compile("^.$")
for line in lines.split("\n"):
    if pattern_only_one.match(line):
        print(line)

t

for line in lines.split("\n"):
    if re.compile(".?").match(line):
        print(line)

t
tt
ttt
tttt
ttttt

for line in lines.split("\n"):
    if re.compile("^.?$").match(line):
        print(line)

t

data = """fdshf kjhkjgh fkjdshfds
dkjhfkjds hjhdfkjdhs  kjhdsfkjd
k;lkgfd oiurt  jhdsfkjh kjdshf
urytire 2021-12-01 iuyriuew r
Total             7654653.0 USD
"""

datep = re.compile(r".*\d{4,4}-\d\d-\d\d") # r before string says that take the string as it is!
for line in data.split("\n"):
    if datep.match(line):
        print(line)

urytire 2021-12-01 iuyriuew r

datep = re.compile(r"(?P<before>.*)(?P<date>\d{4,4}-\d\d-\d\d)(?P<after>.*)") # r before string says that take the string as it is!
for line in data.split("\n"):
    m = datep.match(line)
    if m:
        print(m.groups())

('urytire ', '2021-12-01', ' iuyriuew r')

m = datep.match("urytire 2021-12-01 iuyriuew r")

m.groupdict()['before']

'urytire '

m.groupdict()['after']

' iuyriuew r'

m.groupdict()['date']

'2021-12-01'

data = """fdshf kjhkjgh fkjdshfds
dkjhfkjds hjhdfkjdhs  kjhdsfkjd
k;lkgfd oiurt  jhdsfkjh kjdshf
urytire 2021-12-01 iuyriuew r
Total             7654653.0 USD
"""

totalp = re.compile(r"Total\s+[0-9.]+\s+USD")
for line in data.split("\n"):
    if totalp.match(line):
        print(line)

Total             7654653.0 USD

totalp = re.compile(r"Total\s+(?P<value>[0-9.]+)\s+USD") ## [XYZ] consider only X,Y or Z
for line in data.split("\n"):
    m = totalp.match(line)
    if m:
        print(line)
        print(m.groupdict()['value'])

Total             7654653.0 USD
7654653.0

How tos for regular expression

https://docs.python.org/3/howto/regex.html

"[a-z]" # it matches with a char from a to z 

'[a-z]'

"[a-z]{2,2}" # it matches extactly 2 cahr from a-z

'[a-z]{2,2}'

"[a-z]+" # it matches one or more chars from a to z

'[a-z]+'

PDF reading

pdfplumber is slightly better in extracting text as compared to PyPDF2