def fetch_id(value,table): # Make PostgreSQL Connection connection = None engine = setup_environment.get_database() connection = engine.connect() query = 'select id from '+table+' where name = :name' result_set = connection.execute(text(query), name = value).fetchone() connection.close() for r in result_set: return r
def fetch_citations_id(author,year,title): # Make PostgreSQL Connection engine = setup_environment.get_database() connection = None connection = engine.connect() query = 'select id from citations where author = :author and year = :year and title = :title' result_set = connection.execute(text(query),author=author,year=year, title = title).fetchone() connection.close() for r in result_set: return r
def fetch_cultivars_id(name,specie_id): # Make PostgreSQL Connection engine = setup_environment.get_database() connection = None connection = engine.connect() query = 'select id from cultivars where name = :name and specie_id = :specie_id' result_set = connection.execute(text(query), name = name,specie_id=specie_id).fetchone() connection.close() for r in result_set: return r
def fetch_sites_id(sitename): # Make PostgreSQL Connection engine = setup_environment.get_database() connection = None connection = engine.connect() query = 'select id from sites where sitename = :name' result_set = connection.execute(text(query), name = sitename).fetchone() connection.close() for r in result_set: return r
def insert_sites_table(table,data): #Make PostgreSQL Connection csv.field_size_limit(sys.maxsize) engine = setup_environment.get_database() connection = None connection = engine.raw_connection() cur = connection.cursor() with open(data, 'r') as f: reader = csv.reader(f) next(reader) # Skip the header row. for row in reader: #cur.execute("INSERT INTO sites (sitename,city,state,country,notes,greenhouse,geometry,time_zone,soil,soilnotes) VALUES (%s, %s, %s, %s,%s, %s, ST_Transform(ST_Geomfromtext(%s,32612),4326), %s,%s, %s)",row) cur.execute("INSERT INTO sites (sitename,city,state,country,notes,greenhouse,geometry,time_zone,soil,soilnotes) VALUES (%s, %s, %s, %s,%s, %s, ST_MakeValid(ST_Geomfromtext(%s,4326)), %s,%s, %s)",row) print(row) connection.commit()
import sys import os import pandas as pd import subprocess import argparse import pdb import pickle from setup import setup_environment # Make PostgreSQL Connection engine = setup_environment.get_database() try: con = engine.raw_connection() con.cursor().execute("SET SCHEMA '{}'".format('your_schema_name')) except: pass # import psycopg2 # import sys, os # import numpy as np # import pandas as pd # import cred as creds # import pandas.io.sql as psql # ## ****** LOAD PSQL DATABASE ***** ## # # Set up a connection to the postgres server. # conn_string = "host="+ creds.PGHOST +" port="+ "5432" +" dbname="+ creds.PGDATABASE +" user="******" password="******"Connected!")
def insert_table(table,data): #Make PostgreSQL Connection engine = setup_environment.get_database() data.to_sql(table, engine, if_exists = 'append', index=False)