a.py

import psycopg2, time
# 
# This script uses the module psycopg2 to create a table in a Postgres database
# that has 131 rows of values corresponding 
# to the data in the NBI records.
# Credit to Chad Cooper for most of the code.  This is meant to be a non-ArcGIS
# alternative for working with NBI data. 
#
start = time.clock()

con = psycopg2.connect(database="NBI_DB", user="userdoug", password="postgres")
cur = con.cursor()
# 3 + 42x3 + 2 = 131 records 
#      cur.execute("CREATE TABLE test2 (id serial PRIMARY KEY,STATE CHAR(20),STRUC_NO CHAR(17),REC_TYPE CHAR(3), \
cur.execute("CREATE TABLE test2 (STATE CHAR(20),STRUC_NO CHAR(17) PRIMARY KEY,REC_TYPE CHAR(3), \
		RT_SIG_PFX CHAR(40),LVL_SERV CHAR(50),RT_NO CHAR(7), \
		DIR_SUFFIX CHAR(30),HWY_AG_DST CHAR(4),COUNTY CHAR(30), \
		PLACE_CODE CHAR(40),FEAT_INTSC CHAR(26),CRT_FAC_IN CHAR(3), \
		FAC_CAR_ST CHAR(20),LOCATION CHAR(27),MIN_VT_CLR CHAR(4), \
		KM_POINT CHAR(7),HWY_NETWK CHAR(1),LRS_ROUTE CHAR(12), \
		SUBRT_NO CHAR(12),LAT_DMS CHAR(8),LAT_DEG CHAR(3), \
		LAT_MIN CHAR(2),LAT_SEC CHAR(5),LAT_DD DOUBLE PRECISION, \
		LON_DMS CHAR(9),LON_DEG CHAR(4),LON_MIN CHAR(2), \
		LON_SEC CHAR(5),LON_DD DOUBLE PRECISION,BYPASS_LEN FLOAT(3), \
		TOLL CHAR(50),MAINT_RESP CHAR(50),OWNER CHAR(50), \
		FUNCT_CLA CHAR(80),YEAR_BUILT CHAR(4),LANES_ON CHAR(2), \
		LANES_UNDR CHAR(2),AVG_TRAFIC INTEGER,YR_AVG_TRF CHAR(4), \
		DSGN_LOAD CHAR(1),APRCH_WIDH FLOAT(4),BRDG_MEDN CHAR(50), \
		SKEW CHAR(2),STRX_FLARD CHAR(1),BRDG_RAILS CHAR(1), \
		TRANSTNS CHAR(1),APRCH_RAIL CHAR(1),APRCH_R_ED CHAR(1), \
		HIST_SIGNF CHAR(1),NAV_CTRL CHAR(3),NAV_VT_CLR CHAR(4), \
		NAV_HZ_CLR CHAR(5),STRX_OPEN CHAR(1),SRV_TYP_ON CHAR(1), \
		SRV_TYP_UN CHAR(1),DSGN_TYPE CHAR(1),MAT_TYPE CHAR(2), \
		MAT_KIND CHAR(1),CNST_TYPE CHAR(2),NO_MN_SPAN CHAR(3), \
		NO_AP_SPAN CHAR(4),TOT_HZ_CLR CHAR(3),MX_SPN_LEN CHAR(5), \
		STRX_LEN CHAR(6),L_CURB_LEN CHAR(3),R_CURB_LEN CHAR(3), \
		RDWY_WIDTH CHAR(4),DECK_WIDTH CHAR(4),MN_V_CLR_O CHAR(4), \
		O_REF_FEAT CHAR(1),MN_V_CLR_U CHAR(4),U_REF_FEAT CHAR(1), \
		MN_LAT_UC CHAR(3),MN_LAT_UCL CHAR(3),DECK CHAR(1), \
		SUPERSTRX CHAR(1),SUBSTRX CHAR(1),CHAN_PROT CHAR(1), \
		CULVERTS CHAR(1),OP_RT_METH CHAR(1),OPER_RATE CHAR(3), \
		IV_RT_METH CHAR(1),INVEN_RATE CHAR(3),STRX_EVAL CHAR(1), \
		DECK_GEOM CHAR(1),UC_VT_HZ CHAR(1),BRDG_POST CHAR(1), \
		WW_ADEQCY CHAR(1),AP_RW_ALGN CHAR(1),PRP_WK_TYP CHAR(2), \
		WK_DONE_BY CHAR(1),IMPVMT_LEN CHAR(6),INSPECT_DT CHAR(4), \
		INSPT_FREQ CHAR(2),FRX_DET CHAR(3),UDWAT_INSP CHAR(3), \
		OT_INSP CHAR(3),FRX_DET_DT CHAR(4),UD_INSP_DT CHAR(4), \
		OT_INSP_DT CHAR(4),BRG_IMP_CT CHAR(6),RW_IMP_CT CHAR(6), \
		TOT_COST CHAR(6),YR_IMP_EST CHAR(4),NGHB_ST_CD CHAR(3), \
		PCT_RESP CHAR(2),BD_BRG_NO CHAR(15),STRAHNET CHAR(1), \
		STRX_DESIG CHAR(1),TRAFIC_DIR CHAR(1),TMP_STR_DS CHAR(1), \
		HY_SOI_RT CHAR(1),FD_LND_HY CHAR(1),YR_RECONST CHAR(4), \
		DK_ST_TYPE CHAR(1),WR_SF_TYPE CHAR(1),MEMB_TYPE CHAR(1), \
		DK_PROTCT CHAR(1),AVG_TRCK_C CHAR(2),NAT_NETWRK CHAR(1), \
		PIER_PROT CHAR(1),NBIS_LEN CHAR(1),SCR_BRDGS CHAR(1), \
		FUT_ADT CHAR(6),YR_FUT_ADT CHAR(4),MN_NAV_CLR CHAR(4), \
		FED_AGNCY CHAR(1),WASH_USE CHAR(1),STATUS CHAR(1), \
		ASTERISK CHAR(1),SUFF_RATNG CHAR(4));") 
	    
#cur.execute("INSERT INTO test2 (STATE,STRUC_NO,REC_TYPE) VALUES (%s,%s,%s)", ("caldoug","34","1"))
#cur.execute("SELECT * FROM test;")
#cur.fetchone()
con.commit()
cur.close()
con.close()
#
end = time.clock()
msg = '\n\n' + str(time.strftime('%I:%M:%S %p', time.localtime())) + \
              ':  Done! Database Table Created  ' 
print msg