def addGDBTable2postgres_histo(currentobject, eu, eu_col):
print 'addGDBTable2postgres_histo..................................................'
print currentobject
##set the engine.....
engine = create_engine(
'postgresql://*****:*****@144.92.235.105:5432/usxp')
# Execute AddField twice for two new fields
fields = [f.name for f in arcpy.ListFields(currentobject)]
# converts a table to NumPy structured array.
arr = arcpy.da.TableToNumPyArray(currentobject, fields)
print arr
#### convert numpy array to pandas dataframe
df = pd.DataFrame(data=arr)
### remove column
del df['OBJECTID']
print df
##perform a psuedo pivot table
df = pd.melt(df,
id_vars=["LABEL"],
var_name="atlas_st",
value_name="count")
df.columns = map(str.lower, df.columns)
print df
#### format column in df #########################
## strip character string off all cells in column
df['atlas_st'] = df['atlas_st'].map(lambda x: x.strip('atlas_'))
## remove comma from year
df['value'] = df['label'].str.replace(',', '')
print df
print 'lkl', gen.getPixelConversion2Acres(30)
####add column
df['acres'] = df['count'] * gen.getPixelConversion2Acres(30)
tablename = currentobject.split('\\')[-1]
print 'tablename', tablename
print df
df.to_sql(tablename, engine, schema='zonal_hist')
def addAcresField(tablename, schema, res):
#this is a sub function for addGDBTable2postgres()
cur = conn.cursor()
####DDL: add column to hold arrays
query1 = 'ALTER TABLE {}.{} ADD COLUMN acres bigint'.format(
schema, tablename, tablename)
print query1
cur.execute(query1)
conn.commit()
#####DML: insert values into new array column
cur.execute('UPDATE {}.{} SET acres = count * {}'.format(
schema, tablename, gen.getPixelConversion2Acres(res)))
conn.commit()
cur.execute(
'ALTER TABLE sa.acres_ytc ADD COLUMN {} bigint'.format(tablename))
conn.commit()
query2 = 'update sa.acres_ytc set {} = instance.acres from (select value, acres from sa.{}) as instance where acres_ytc.value = instance.value'.format(
tablename, tablename)
cur.execute(query2)
conn.commit()
query3 = 'DROP TABLE sa.{}'.format(tablename)
cur.execute(query3)
conn.commit()
def addRasterAttrib2postgres_specific(path, filename, database, schema):
print path
print filename
# set the engine.....
engine = create_engine(
'postgresql://*****:*****@144.92.235.105:5432/{}'.format(
database))
# Execute AddField twice for two new fields
fields = [f.name for f in arcpy.ListFields(path)]
print 'fields:', fields
# converts a table to NumPy structured array.
arr = arcpy.da.TableToNumPyArray(path, fields)
print arr
# convert numpy array to pandas dataframe
df = pd.DataFrame(data=arr)
df.columns = map(str.lower, df.columns)
## add acres column
df['acres'] = df['count'] * gen.getPixelConversion2Acres(30)
## add acres column
df['hectares'] = df['count'] * gen.getPixelConversion2Hectares(30)
print 'df-----------------------', df
# # # use pandas method to import table into psotgres
df.to_csv(
'D:\\projects\\usxp\\deliverables\\s35\\csv\\{}.csv'.format(path),
index=False,
header=True)
def addGDBTable2postgres(currentobject, eu, eu_col):
print currentobject
##set the engine.....
engine = create_engine(
'postgresql://*****:*****@144.92.235.105:5432/usxp')
# Execute AddField twice for two new fields
fields = [f.name for f in arcpy.ListFields(currentobject)]
# converts a table to NumPy structured array.
arr = arcpy.da.TableToNumPyArray(currentobject, fields)
print arr
# convert numpy array to pandas dataframe
df = pd.DataFrame(data=arr)
df.columns = map(str.lower, df.columns)
print 'lkl', gen.getPixelConversion2Acres(30)
####add column
df['acres'] = df['count'] * 0.22227
print 'df-----------------------', df
schema = 'deliverables'
tablename = currentobject.split('\\')[-1]
print 'tablename', tablename
tryit(df, tablename, eu, eu_col)
def addGDBTable2postgres_histo(gdb, pgdb, schema, table):
print 'addGDBTable2postgres_histo..................................................'
# Set the workspace environment to local file geodatabase
arcpy.env.workspace = gdb
##set the engine.....
engine = create_engine('postgresql://*****:*****@144.92.235.105:5432/{}'.format(pgdb))
# Execute AddField twice for two new fields
fields = [f.name for f in arcpy.ListFields(table)]
# converts a table to NumPy structured array.
arr = arcpy.da.TableToNumPyArray(table,fields)
print arr
#### convert numpy array to pandas dataframe
df = pd.DataFrame(data=arr)
### remove column
del df['OBJECTID']
# ##perform a psuedo pivot table
# df=pd.melt(df, id_vars=["LABEL"],var_name="atlas_st", value_name="count")
df=pd.melt(df, id_vars=["LABEL"], value_name="count")
df.columns = map(str.lower, df.columns)
# ####add column
df['acres'] = df['count']*gen.getPixelConversion2Acres(30)
tablename = table.split('\\')[-1]
print 'tablename', tablename
df.to_sql(tablename, engine, schema=schema)
def addGDBTable2postgres_histo(gdb, tablename, eu_col):
print 'addGDBTable2postgres_histo..................................................'
print tablename
arcpy.env.workspace = "D:\\projects\\usxp\\deliverables\\maps\\{0}\\{0}.gdb".format(
gdb)
##set the engine.....
engine = create_engine(
'postgresql://*****:*****@144.92.235.105:5432/usxp_deliverables')
# Execute AddField twice for two new fields
fields = [f.name for f in arcpy.ListFields(tablename)]
# converts a table to NumPy structured array.
arr = arcpy.da.TableToNumPyArray(tablename, fields)
print arr
#### convert numpy array to pandas dataframe
df = pd.DataFrame(data=arr)
### remove column
del df['OBJECTID']
print df
##perform a psuedo pivot table
df = pd.melt(df, id_vars=["LABEL"], var_name=eu_col, value_name="count")
df.columns = map(str.lower, df.columns)
print df
#### format column in df #########################
## strip character string off all cells in column
df['state'] = df[eu_col].map(lambda x: x.strip('atlas_'))
## remove comma from year
# df['value'] = df['label'].str.replace(',', '')
print df
print 'lkl', gen.getPixelConversion2Acres(30)
####add column
df['acres'] = df['count'] * gen.getPixelConversion2Acres(30)
print df
df.to_sql(tablename, engine, schema=gdb)
def addRasterAttrib2postgres_specific(graphic_type, yxc, cdl_type, database, schema):
filename = 's35_{0}_{1}'.format(cdl_type, graphic_type)
print filename
path='D:\\projects\\usxp\\deliverables\\s35\\conus.gdb\\{0}'.format(filename)
print path
# set the engine.....
engine = create_engine('postgresql://*****:*****@144.92.235.105:5432/{}'.format(database))
# Execute AddField twice for two new fields
fields = [f.name for f in arcpy.ListFields(path)]
print 'fields:', fields
# converts a table to NumPy structured array.
arr = arcpy.da.TableToNumPyArray(path,fields)
print arr
# convert numpy array to pandas dataframe
df = pd.DataFrame(data=arr)
if graphic_type == 'sm':
df.columns = ['objectid','value','count','state','year','label']
##add 0 infront of state where length of state code is equal to 1
df['state'] = np.where(df['state'].astype(str).str.len()==1, '0' + df['state'].astype(str), df['state'].astype(str))
## add acres column
df['acres'] = df['count'] * gen.getPixelConversion2Acres(30)
print 'df-----------------------', df
# # # use pandas method to import table into psotgres
df.to_sql(filename, engine, schema=schema, if_exists='replace')
elif graphic_type == 'conus':
print 'inside conus!!!!!!!!!!!!!!!!!!!!!!'
df.columns = ['objectid','value','count','year','label']
## add acres column
df['acres'] = df['count'] * gen.getPixelConversion2Acres(30)
print 'df-----------------------', df
# # # use pandas method to import table into psotgres
df.to_sql(filename, engine, schema=schema, if_exists='replace')
def addAcresField(tablename, schema):
#this is a sub function for addGDBTable2postgres()
cur = conn.cursor()
#DDL: add column to hold arrays
cur.execute('ALTER TABLE ' + schema + '.' + tablename + ' ADD COLUMN acres bigint;');
#DML: insert values into new array column
cur.execute('UPDATE '+ schema + '.' + tablename + ' SET acres = count * ' + gen.getPixelConversion2Acres(data['global']['res']));
conn.commit()
print "Records created successfully";
conn.close()
def addAcresField(schema, tablename, res):
#this is a sub function for addGDBTable2postgres()
cur = conn.cursor()
####DDL: add column to hold arrays
query = 'ALTER TABLE {}.{} ADD COLUMN acres bigint'.format(schema, tablename)
print query
cur.execute(query)
print int(tablename.split("_")[0][1:])
#####DML: insert values into new array column
cur.execute("UPDATE {0}.{1} SET acres=count*{2}".format(schema, tablename, gen.getPixelConversion2Acres(res) ))
conn.commit()
def addAcresField_now(schema, tablename, res):
#this is a sub function for addGDBTable2postgres()
cur = conn.cursor()
####DDL: add column to hold arrays
query1 = 'ALTER TABLE {}.{} ADD COLUMN acres bigint'.format(
schema, tablename, tablename)
print query1
cur.execute(query1)
conn.commit()
#####DML: insert values into new array column
cur.execute('UPDATE {}.{} SET acres = count * {}'.format(
schema, tablename, gen.getPixelConversion2Acres(res)))
conn.commit()
def addAcresField(schema, tablename, res, total):
#this is a sub function for addGDBTable2postgres()
cur = conn.cursor()
####DDL: add column to hold arrays
query1 = 'ALTER TABLE {}.{} ADD COLUMN acres bigint, ADD COLUMN perc numeric, ADD COLUMN series text'.format(
schema, tablename)
print query1
cur.execute(query1)
#####DML: insert values into new array column
cur.execute(
"UPDATE {0}.{1} SET acres=count*{2}, perc=(count/{3})*100, series='{4}'"
.format(schema, tablename, gen.getPixelConversion2Acres(res), total,
tablename.split("_")[0]))
conn.commit()
def addAcresField(schema, tablename, res):
try:
conn = psycopg2.connect("dbname= 'usxp_deliverables' user='******' host='144.92.235.105' password='******'")
except:
print "I am unable to connect to the database"
#this is a sub function for addGDBTable2postgres()
cur = conn.cursor()
####DDL: add column to hold arrays
query = 'ALTER TABLE {}.{} ADD COLUMN acres bigint'.format(schema, tablename)
print query
cur.execute(query)
#####DML: insert values into new array column
cur.execute("UPDATE {0}.{1} SET acres=count*{2}".format(schema, tablename, gen.getPixelConversion2Acres(res) ))
conn.commit()
def addAcresField(schema, tablename, yxc, res):
#this is a sub function for addGDBTable2postgres()
cur = conn.cursor()
####DDL: add column to hold arrays
ddl_query = 'ALTER TABLE {}.{} ADD COLUMN acres bigint, ADD COLUMN series text, ADD COLUMN yxc text, ADD COLUMN series_order integer'.format(schema, tablename)
print ddl_query
cur.execute(ddl_query)
#####DML: insert values into new array column
dml_query="UPDATE {0}.{1} SET acres=count*{2}, series='{3}', yxc='{4}', series_order={5}".format(schema, tablename, gen.getPixelConversion2Acres(res), tablename.split("_")[0], yxc, int(tablename.split("_")[0][1:]))
print dml_query
cur.execute(dml_query)
conn.commit()
