Skip to content

psycopg

Psycopg2

It is a package that can be used to integrate PostgreSQL with Jupyter Lab/Notebook

l1 = [1, 2, 3, 4]

l1.pop()
l1

[1, 2]
\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
IndexTypeScanEqualitySearchRangeSearchInsertDelete
0no_indexBD0.5BDBD2DSearch + D
1hashBD(R + 0.125) + 2BRC2DBD4DSearch + 2D
2btree1.5BDD$\\log_F$(1.5B)D$\\log_F$(1.5B) + $log_2$(R) * CSearch + DSearch + D
import pandas as pd

d = {'IndexType':['no_index', 'hash', 'btree'],
    'Scan':['BD', 'BD(R + 0.125) + 2BRC', '1.5BD'],
     'EqualitySearch': ['0.5BD', '2D', r'D$\log_F$(1.5B)'],
     'RangeSearch': ['BD', 'BD', r'D$\log_F$(1.5B) + $log_2$(R) * C'],
     'Insert': ['2D', '4D', 'Search + D'],
     'Delete': ['Search + D', 'Search + 2D', 'Search + D']
    }

pd.DataFrame(d).to_html()

'<table border="1" class="dataframe">\n  <thead>\n    <tr style="text-align: right;">\n      <th></th>\n      <th>IndexType</th>\n      <th>Scan</th>\n      <th>EqualitySearch</th>\n      <th>RangeSearch</th>\n      <th>Insert</th>\n      <th>Delete</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>no_index</td>\n      <td>BD</td>\n      <td>0.5BD</td>\n      <td>BD</td>\n      <td>2D</td>\n      <td>Search + D</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>hash</td>\n      <td>BD(R + 0.125) + 2BRC</td>\n      <td>2D</td>\n      <td>BD</td>\n      <td>4D</td>\n      <td>Search + 2D</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>btree</td>\n      <td>1.5BD</td>\n      <td>D$\\log_F$(1.5B)</td>\n      <td>D$\\log_F$(1.5B) + $log_2$(R) * C</td>\n      <td>Search + D</td>\n      <td>Search + D</td>\n    </tr>\n  </tbody>\n</table>'

d = {'eid':[1, 2, 3],'name':['Jackson', 'Jonathan', 'Nabi'], 'title':['Director', 'Professor', 'Engineer'], 'ssn':[None, None, None]}
df = pd.DataFrame(d)
df.to_csv('employees_2.csv', index = False)

! pwd

/Users/deepaksingh/Desktop/MSDS/RDBMS

! conda env list

# conda environments:
#
base                  *  /Users/deepaksingh/opt/anaconda3
discom                   /Users/deepaksingh/opt/anaconda3/envs/discom
env-cassandra            /Users/deepaksingh/opt/anaconda3/envs/env-cassandra
env-obb                  /Users/deepaksingh/opt/anaconda3/envs/env-obb
env-tensors              /Users/deepaksingh/opt/anaconda3/envs/env-tensors

df = pd.read_csv('Iowa_Fleet_Summary_By_Year__County_And_Vehicle_Type.csv')
df.head()
Year Year Ending County Name County FIP Feature ID Motor Vehicle Vehicle Category Vehicle Type Tonnage Registrations Annual Fee Primary County Lat Primary County Long Primary County Coordinates
0 2005 12/31/2005 No County NaN NaN Yes Automobile Automobile NaN 3711 NaN NaN NaN NaN
1 2006 12/31/2006 No County NaN NaN Yes Motor Home Motor Home - A NaN 1 NaN NaN NaN NaN
2 2008 12/31/2008 No County NaN NaN Yes Automobile Automobile NaN 14 1021.0 NaN NaN NaN
3 2008 12/31/2008 Ida 19093.0 465235.0 Yes Bus Bus NaN 5 680.0 42.386875 -95.513496 POINT (-95.5134962 42.3868747)
4 2011 12/31/2011 Jasper 19099.0 465238.0 Yes Moped Moped NaN 198 1386.0 41.686039 -93.053765 POINT (-93.053765 41.6860394) 
import psycopg2 as pg

host = 'localhost'
port = 5432
database = 'msds691'
user = 'postgres'
password = 'postgres'

conn = pg.connect(host = host, port = port, database = database, user = user, password = password)

cur = conn.cursor()

cur.execute("select * from employees2")
cur.fetchall()

[(1, 'Jackson', 'Director', None),
 (2, 'Jonathan', 'Professor', None),
 (3, 'Nabi', 'Engineer', None)]

cur.execute("select department_id from departments;")
data = cur.fetchall()
did_list = []
for i in data:
    did_list.append(i[0])
did_list

[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11]







conn

<connection object at 0x137a48c10; dsn: 'user=postgres password=xxx dbname=msds691 host=localhost port=5432', closed: 0>

cur.close()
conn.close()