import wget

url = '/media/blog/files/python-tutorials/nbk.csv'
wget.download(url, 'nbk.csv') # download the file into current directory

'nbk.csv'

import os.path
import time
from datetime import datetime as dt

def sizeof_fmt(num, suffix='B'):
    for unit in ['','Ki','Mi','Gi','Ti','Pi','Ei','Zi']:
        if abs(num) < 1024.0:
            return "%3.1f %s%s" % (num, unit, suffix)
        num /= 1024.0
    return "%.1f %s%s" % (num, 'Yi', suffix)

def fileLineCount(filePath):
    try: fileHandle = open(filePath, encoding='utf-8')
    except: print('File cannot be opened.')
    lineCounter = 0
    for line in fileHandle: lineCounter+=1
    fileHandle.close()
    return lineCounter

def fileHead(filePath, n=5):
    try: fileHandle = open(filePath, encoding='utf-8')
    except: print('File cannot be opened.')
    lineCounter = 0
    for line in fileHandle: 
        if lineCounter >= n: break
        else:
            print('Line {lineCounter:,}: \t {line}'.format(lineCounter=lineCounter, line=line), end='')
            lineCounter+=1
    fileHandle.close()
    
def fileTail(filePath, n=5):
    try: fileHandle = open(filePath, encoding='utf-8')
    except: print('File cannot be opened.')
    totalLines = fileLineCount(filePath)
    if n > totalLines:
        print('Error: number of lines required cannot be greater than total lines in file.')
        fileHandle.close()
        return
    lineCounter = 0
    for line in fileHandle: 
        if lineCounter in range(totalLines-n, totalLines):
            print('Line {lineCounter:,}: \t {line}'.format(lineCounter=lineCounter, line=line), end='')
        lineCounter+=1
    fileHandle.close()


myFile = r'nbk.csv' # change here 

print('\nFile details')
print('------------')
print('File       : \t', myFile)
print('Created    : \t', dt.strptime(time.ctime(os.path.getctime(myFile)), "%a %b %d %H:%M:%S %Y"))
print('Modified   : \t', dt.strptime(time.ctime(os.path.getmtime(myFile)), "%a %b %d %H:%M:%S %Y"))
print('Accessed   : \t', dt.strptime(time.ctime(os.path.getatime(myFile)), "%a %b %d %H:%M:%S %Y"))
print('Size       : \t', sizeof_fmt(os.path.getsize(myFile)))
print('Line count : \t {lineCount:,}'.format(lineCount=fileLineCount(myFile)))

print('\n\nFile preview')
print('------------')
fileHead(myFile)
print('...')
fileTail(myFile)

File details
------------
File       : 	 nbk.csv
Created    : 	 2021-03-19 16:38:27
Modified   : 	 2021-03-19 16:38:27
Accessed   : 	 2021-03-19 16:38:26
Size       : 	 105.4 KiB
Line count : 	 2,465


File preview
------------
Line 0: 	 date,price,open,high,low,vol,change
Line 1: 	 Nov 14 2019,968,973,973,967,1719234,-0.51%
Line 2: 	 Nov 13 2019,973,970,974,970,5404608,0.10%
Line 3: 	 Nov 12 2019,972,967,974,967,4525303,0.31%
Line 4: 	 Nov 11 2019,969,963,971,962,3996610,0.73%
...
Line 2,460: 	 Jan 10 2010,683,683,683,670,147681,0.00%
Line 2,461: 	 Jan 07 2010,683,670,683,658,2272648,3.80%
Line 2,462: 	 Jan 06 2010,658,658,670,658,1062484,-1.79%
Line 2,463: 	 Jan 05 2010,670,658,670,646,1895241,1.82%
Line 2,464: 	 Jan 04 2010,658,658,658,646,1136324,-3.66%

filePath = myFile
tableName = 'test_table' # change here

try: fileHandle = open(filePath, encoding='utf-8')
except: print('File cannot be opened.')
for line in fileHandle: 
    break
fileHandle.close()

myTuple = line.split(',')

print('\nTable name to be used : {tableName}\n'.format(tableName=tableName))

for i in range(0, len(myTuple)):
    if i != 0: print(',', end='')
    print('"{attr}"'.format(attr=myTuple[i].strip().lower()), end='')
    
print('\n')
    
for i in range(0, len(myTuple)):
    if i != 0: print(',', end='')
    print('{{{attr}}}'.format(attr=myTuple[i].strip().lower()), end='')
    
print('\n')
    
for i in range(0, len(myTuple)):
    if i != 0: print(',', end='')
    print('{attr}=myTuple[{i}]'.format(attr=myTuple[i].strip().lower(), i=i))

print('\n')

Table name to be used : test_table

"date","price","open","high","low","vol","change"

{date},{price},{open},{high},{low},{vol},{change}

date=myTuple[0]
,price=myTuple[1]
,open=myTuple[2]
,high=myTuple[3]
,low=myTuple[4]
,vol=myTuple[5]
,change=myTuple[6]

##############
# CREATE TABLE
##############

import psycopg2

con = psycopg2.connect("dbname='test_db' user='test_user' host='localhost' password='test_password'") # change here
cur = con.cursor()

# create table
# use the output from the previous step to adjust the below
# put the appropriate data types
queryString = """
drop table if exists {tableName};
create table {tableName} (
     "date" date
    ,"price" float
    ,"open" float
    ,"high" float
    ,"low" float
    ,"vol" float
    ,"change" float
);
""".format(tableName=tableName)
cur.execute(queryString)
con.commit()

# close connection
con.close()

print('Table "{tableName}" created.'.format(tableName=tableName))

Table "test_table" created.

"""REFERENCE



-------------------
strptime / stftime:
-------------------

resource: https://www.programiz.com/python-programming/datetime/strptime

year         -- %Y
month (name) -- %b -- %B
month        -- %m -- %-m
day          -- %d -- %-d

hour         -- %H -- %-H
minute       -- %M -- %-M
seconds      -- %S -- %-S



--------
strings:
--------

string methods: dir(str)

string formatting: https://docs.python.org/3/library/string.html#formatspec



------
round:
------

round(5.76543, 2)



"""

con = psycopg2.connect("dbname='test_db' user='test_user' host='localhost' password='test_password'")
cur = con.cursor()
filePath = myFile

# load data 
try: fileHandle = open(filePath, encoding='utf-8')
except: print('File cannot be opened.')
lineCounter = 0
for line in fileHandle: 
    
    print ("Working on line : {i:<,d}".format(i=lineCounter), end='\r', flush=True)
    
    # skip header
    if lineCounter != 0:
    
        # specify delimeter
        myTuple = line.split(',')
        
        # fix nulls, if any
        for i in range(0, len(myTuple)):
            if len(myTuple[i]) == 0: myTuple[i] = 'null'
        
        # update here, step 2 output can be used
        queryString = 'insert into {tableName} ("date","price","open","high","low","vol","change") values (\'{date}\',{price},{open},{high},{low},{vol},{change});'.format(
             date=dt.strptime(myTuple[0].strip(), "%b %d %Y").strftime("%Y-%m-%d %H:%M:%S")
            ,price=myTuple[1]
            ,open=myTuple[2]
            ,high=myTuple[3]
            ,low=myTuple[4]
            ,vol=myTuple[5]
            ,change=round(float(myTuple[6].replace('%', ''))/100, 5)
            ,tableName=tableName
        )
        cur.execute(queryString)
        
    lineCounter += 1

con.commit()
fileHandle.close()

# read number of records in database to make sure it's a match
cur.execute("select count(*) from {tableName};".format(tableName=tableName))
print("\nNumber of records in table \"{tableName}\" : {recordCount:<,d}".format(tableName=tableName, recordCount=cur.fetchall()[0][0], ))

# close connection
con.close()

Working on line : 2,464
Number of records in table "test_table" : 2,464

import pandas as pd

con = psycopg2.connect("dbname='test_db' user='test_user' host='localhost' password='test_password'")
cur = con.cursor()
query = 'select * from test_table;'
df = pd.read_sql_query(query, con)

# close connection
con.close()

df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 2464 entries, 0 to 2463
Data columns (total 7 columns):
 #   Column  Non-Null Count  Dtype  
---  ------  --------------  -----  
 0   date    2464 non-null   object 
 1   price   2464 non-null   float64
 2   open    2464 non-null   float64
 3   high    2464 non-null   float64
 4   low     2464 non-null   float64
 5   vol     2464 non-null   float64
 6   change  2464 non-null   float64
dtypes: float64(6), object(1)
memory usage: 134.9+ KB

df.head()

df.tail()