tpjfern03

"""
Patched version to support PostgreSQL 
(original version: https://github.com/pydata/pandas/blob/v0.13.1/pandas/io/sql.py)

Adapted functions are:
- added _write_postgresql
- updated table_exist
- updated get_sqltype
- updated get_schema

tpjfern03

#A SQLAlchemy Cheat Sheet
###Introduction

##Basic Models
One model is used to describe one database table. For example:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import scoped_session,sessionmaker
from zope.sqlalchemy import ZopeTransactionExtension
from sqlalchemy import (

tpjfern03

import cx_Oracle
import pandas

connection = cx_Oracle.connect('username/pwd@host:port/dbname')

def read_query(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute( query )
        names = [ x[0] for x in cursor.description]

tpjfern03

Introduction to cx_Oracle

cx_Oracle is a third-party Python library that facilitates Oracle-to-Python database communication. A cursor is a control structure that enables traversal over the records in a database. This is Python's primary means of accessing database table data. The setup is:

• Create a connection object
• Define a cursor and call the cursor() method on the connection object
• Construct a query string for the data of interest
• Pass the query string to the cursor's execute method

tpjfern03

# Note: it is a copy of great answer by "mgoldwasser" from Stackoverflow
# Check the original answer here: http://stackoverflow.com/a/26018934/1032439

# Imagine that post1, post5, and post1000 are posts objects with ids 1, 5 and 1000 respectively
# The goal is to "upsert" these posts.
# we initialize a dict which maps id to the post object

my_new_posts = {1: post1, 5: post5, 1000: post1000} 

for each in posts.query.filter(posts.id.in_(my_new_posts.keys())).all():

tpjfern03

import csv
import sqlalchemy as sa
import os
import pytz
from datetime import datetime
import itertools

tz = pytz.timezone('America/Chicago')

# DB_CONN = os.environ['DATABASE_URL']

tpjfern03

"""Aliases for SQL/SQLAlchemy objects that are assured to be correctly type-checked.

https://gist.github.com/Stiivi/8618942769b0bc6a8ba3371c4431ad75
"""

import sqlalchemy


# Engine
# ======

tpjfern03

rtdb_init()
# Open the file for a status station
# status_file = vde.open_status_telem(10, 'R', 'S', read_only=False)
for station, station_name in rtdb_station_list(names=True):
    if station in stationList:
        if station_name != '':
            for cat in categoryList:
                for pt in pointTypeList:
                    # print('Listing points for ' + str(station) + '/' + cat + '/' + pt)
                    # Open the vde file...

tpjfern03

from acsprism import vde
from acsprism import rtdb_init, rtdb_station_list, rtdb_point_list, RtdbPoint

# Open the file for a status station
# status_file = vde.open_status_telem(10, 'R', 'S', read_only=False)
#records = status_record.record_count()
#logger.info("Reading %d records: station=%d, category=%s, type=%s from rtdb." % (records, stn, cat, pointType))

for station, station_name in rtdb_station_list(names=True):
   for cat in categoryList:

tpjfern03

echo "Loading data....." 
sqlldr schema/pw@sid control=hdc_stage_points.ctl rows=10000 bad=hdc_stage_points.bad log=hdc_stage_points.log errors=100000

# Wait until the data is loaded & log file is available
cat hdc_stage_points.log | grep "Rows successfully loaded." > temp.log
IFS=" "
while read -r records b c d; do
    export RC=$records
    #echo "${RC}"
done < temp.log