Vertica

This quick introduction to using Vertica inside IPython starts with a datarama blog post.



In [ ]:

    
import json
from vertica_python import connect

Create Connection



In [ ]:

    
# create connection
connection = connect({
    'host': '127.0.0.1',
    'port': 5434,
    'user': '<usrname>',
    'password': '<pswd>',
    'database': '<db>'
    })

# SQL strings are executed within the cursor
cur = connection.cursor()

Build New Table



In [ ]:

    
# build new table
cur.execute(r"""DROP TABLE IF EXISTS test.ddis_blehman_test""")
cur.execute(r"""CREATE TABLE test.ddis_blehman_test 
(
userID INT
, endDate_oldCarrier TIMESTAMP
, startDate_newCarrier TIMESTAMP
, oldCarrier VARCHAR(50)
, newCarrier VARCHAR(50)
, days_oldCarrier INT
, days_newCarrier INT
, ratio FLOAT
)""")

Insert Data Into Table



In [ ]:

    
# insert data to table
cur.execute(r"""INSERT INTO test.ddis_blehman_test VALUES 
(
'158871820'
, '2014-10-20 17:00:00'
, '2014-10-21 05:00:00'
, 'Verizon'
, 'AT&T'
, '97'
, '91'
, '0.990611'
)""")

Print records



In [ ]:

    
# print records as list
cur = connection.cursor('list') # list is the default behavior
cur.execute(r"""SELECT * FROM test.ddis_blehman_test""")
cur.fetchall()



In [ ]:

    
# print records as dict
cur = connection.cursor('dict')
cur.execute(r"""SELECT * FROM test.ddis_blehman_test""")
cur.fetchall()

Iterate



In [ ]:

    
cur = connection.cursor()
cur.execute(r"""SELECT * FROM test.ddis_blehman_test""")
for row in cur.iterate():
    print(row)

Close Connection



In [ ]:

    
# manual close
connection.close()