def __init__(self):
_user = getenv("DATABASE.USER")
_secret = getenv("DATABASE.PASS")
_host = getenv("DATABASE.HOST")
_port = getenv("DATABASE.PORT")
_dbname = getenv("DATABASE.DB")
self.db = Postgres(url=f"postgresql://{_user}:{_secret}@{_host}:{_port}/{_dbname}")
def main():
print("Reading configuration")
config = read_config()
print("Fetching data")
raw_epc_data = get_epc_data(config.epc_data_url, True)
print("Cleaning data")
data = clean_data(raw_epc_data, config.data_columns)
print("Writing data")
# Wait for postgres to start ... should be handled at the infra level
time.sleep(5)
pg = Postgres(config)
pg.write_data(data, config.postgres_table_name)
print("Verifying data")
print(pg.read_data())
print("Complete")
def initdb(self, env):
db_dsn = env.get('db_dsn')
maxconn = env.get('db_maxconn')
db = Postgres(db_dsn, maxconn=maxconn)
db.register_model(Node)
return db
def load_User_Me():
global STRING_DB
global MY_CHAT_ID_TELEGRAM
db = Postgres(STRING_DB)
db.run(
"INSERT INTO users (chat_id,name,time_added) VALUES ('{}','{}','{}') ON CONFLICT (chat_id) DO NOTHING ;"
.format(MY_CHAT_ID_TELEGRAM, "@f126ck", "1503407762"))
def setUp(self): # override
self.db = Postgres(cursor_factory=self.cursor_factory)
self.db.run("DROP SCHEMA IF EXISTS public CASCADE")
self.db.run("CREATE SCHEMA public")
self.db.run("CREATE TABLE foo (key text, value int)")
self.db.run("INSERT INTO foo VALUES ('buz', 42)")
self.db.run("INSERT INTO foo VALUES ('biz', 43)")
def __init__(self, model, start, end, init_customers, growth, churn, mrr,seed):
'''
Creates the behavior/utility model objects, sets internal variables to prepare for simulation, and creates
the database connection
:param model: name of the behavior/utility model parameters
:param start: start date for simulation
:param end: end date for simulation
:param init_customers: how many customers to create at start date
:param growth: monthly customer growth rate
:param churn: monthly customer churn rate
:param mrr: customer MRR
'''
self.model_name=model
self.start_date = start
self.end_date = end
self.init_customers=init_customers
self.monthly_growth_rate = growth
self.monthly_churn_rate = churn
self.mrr=mrr
self.behave_mod=GaussianBehaviorModel(self.model_name,seed)
self.util_mod=UtilityModel(self.model_name,self.monthly_churn_rate,self.behave_mod)
self.subscription_count = 0
self.tmp_sub_file_name = os.path.join(tempfile.gettempdir(),'{}_tmp_sub.csv'.format(self.model_name))
self.tmp_event_file_name=os.path.join(tempfile.gettempdir(),'{}_tmp_event.csv'.format(self.model_name))
self.db = Postgres("postgres://%s:%s@localhost/%s" % (
os.environ['CHURN_DB_USER'], os.environ['CHURN_DB_PASS'], os.environ['CHURN_DB']))
self.con = post.connect( database= os.environ['CHURN_DB'],
user= os.environ['CHURN_DB_USER'],
password=os.environ['CHURN_DB_PASS'])
class PSQL(object):
def __init__(self, username, password, hostname, hostport, database):
self.db = Postgres("postgres://{}:{}@{}:{}/{}".format(
username, password, hostname, hostport, database))
def cursor(self, sql):
self.db.run(sql)
def setUp(self): # override
self.db = Postgres(DATABASE_URL)
self.db.run("DROP SCHEMA IF EXISTS public CASCADE")
self.db.run("CREATE SCHEMA public")
self.db.run("CREATE TABLE foo (bar text, baz int)")
self.db.run("INSERT INTO foo VALUES ('buz', 42)")
self.db.run("INSERT INTO foo VALUES ('biz', 43)")
def __init__(self, app, *a, **kw):
"""Extend to make the ``Application`` object available on models at
``.app``.
"""
Postgres.__init__(self, *a, **kw)
for model in MODELS:
self.register_model(model)
model.app = app
def setUp(self):
self.db = Postgres(cache=Cache(max_size=1),
cursor_factory=SimpleTupleCursor)
self.db.run("DROP SCHEMA IF EXISTS public CASCADE")
self.db.run("CREATE SCHEMA public")
self.db.run("CREATE TABLE foo (key text, value int)")
self.db.run("INSERT INTO foo VALUES ('a', 1)")
self.db.run("INSERT INTO foo VALUES ('b', 2)")
def _restore_postgres(tempdir, config):
ctx.logger.info('Restoring Postgres data')
postgres = Postgres(config)
queries = _clean_db_queries()
dump_file = os.path.join(tempdir, _POSTGRES_DUMP_FILENAME)
dump_file = postgres.prepend_dump(dump_file, queries)
postgres.restore(dump_file)
ctx.logger.debug('Postgres restored')
def __init__(self, db_url, batchsize=1000, processor_limit=5):
self.db_url = db_url
self.db = Postgres(url=db_url)
self.batchsize = batchsize
self.processor_limit = processor_limit
self.process_count = 0
self.processors = []
self.offset = 0
def __init__(self, app, *a, **kw):
"""Extend to make the ``Application`` object available on models at
``.app``.
"""
Postgres.__init__(self, *a, **kw)
for model in MODELS:
self.register_model(model)
model.app = app
def load_RSS_Feed_DB():
global STRING_DB
db = Postgres(STRING_DB)
selectList = db.all("SELECT * FROM feed;")
allRssFeed = [item[1] for item in selectList]
print("def load_RSS_Feed_DB():")
print(allRssFeed)
print("def load_RSS_Feed_DB():")
def __init__(self, model, start, end, init_customers, seed):
"""
Creates the behavior/utility model objects, sets internal variables to prepare for simulation, and creates
the database connection
:param model: name of the behavior/utility model parameters
:param start: start date for simulation
:param end: end date for simulation
:param init_customers: how many customers to create at start date
"""
self.model_name = model
self.start_date = start
self.end_date = end
self.init_customers = init_customers
self.monthly_growth_rate = 0.12
self.util_mod = UtilityModel(self.model_name)
behavior_versions = glob.glob("../conf/" + self.model_name + "_*.csv")
self.behavior_models = {}
self.model_list = []
for b in behavior_versions:
version = b[(b.find(self.model_name) + len(self.model_name) +
1):-4]
if version in ("utility", "population", "country"):
continue
behave_mod = FatTailledBehaviorModel(self.model_name, seed,
version)
self.behavior_models[behave_mod.version] = behave_mod
self.model_list.append(behave_mod)
if len(self.behavior_models) > 1:
self.population_percents = pd.read_csv(
"../conf/" + self.model_name + "_population.csv", index_col=0)
self.util_mod.setChurnScale(self.behavior_models,
self.population_percents)
self.population_picker = np.cumsum(self.population_percents)
self.country_lookup = pd.read_csv("../conf/" + self.model_name +
"_country.csv")
self.subscription_count = 0
self.tmp_sub_file_name = os.path.join(
tempfile.gettempdir(), "{}_tmp_sub.csv".format(self.model_name))
self.tmp_event_file_name = os.path.join(
tempfile.gettempdir(), "{}_tmp_event.csv".format(self.model_name))
self.db = Postgres("postgres://%s:%s@localhost/%s" % (
os.environ["CHURN_DB_USER"],
os.environ["CHURN_DB_PASS"],
os.environ["CHURN_DB"],
))
self.con = post.connect(
database=os.environ["CHURN_DB"],
user=os.environ["CHURN_DB_USER"],
password=os.environ["CHURN_DB_PASS"],
)
def __init__(self, app, *a, **kw):
"""Extend to make the ``Application`` object available on models at
``.app``.
"""
Postgres.__init__(self, *a, **kw)
for model in (AccountElsewhere, Community, Country, ExchangeRoute,
Participant, Team):
self.register_model(model)
model.app = app
def Conectar(self) -> bool:
try:
self.con = Postgres(
f"postgres://{self.user}:{self.password}@{self.host}:5432/{self.db}"
)
except:
return False
finally:
return True
class db():
"Wrapper for postgres https://postgres-py.readthedocs.org/en/2.1.2/index.html"
def __init__(self, user=user, schema=schema):
self.schema = schema
self.host = 'postgres://%[email protected]/%s' % (user, schema)
self.path = os.path.abspath(
os.path.join(os.path.dirname(__file__), ".."))
def connect(self):
print 'Connecting to %s ...' % self.host
self.db_conn = Postgres(self.host)
def init(self):
print self.schema
try:
# Enable PostGIS (includes raster)
self.db_conn.run("CREATE EXTENSION postgis")
# Enable Topology
self.db_conn.run("CREATE EXTENSION postgis_topology")
# fuzzy matching needed for Tiger
self.db_conn.run("CREATE EXTENSION fuzzystrmatch")
# Enable US Tiger Geocoder
self.db_conn.run("CREATE EXTENSION postgis_tiger_geocoder")
# catch all exception
except:
print 'PostGis is already installed'
query_hotspot = "CREATE TABLE IF NOT EXISTS %s.hotspot (gid serial, latitude numeric, longitude numeric, brightness numeric, scan numeric, track numeric, acq_date date, acq_time varchar(5), satellite varchar(1), confidence int4, version varchar(3), bright_t31 numeric, frp numeric, geom GEOGRAPHY(POINT,4326, 2), PRIMARY KEY(gid))" % self.schema
query_country = "CREATE TABLE IF NOT EXISTS %s.country (gid serial, fips varchar(2), iso2 varchar(2), iso3 varchar(3), un int4, name varchar(50), area int4, pop2005 int4, region int4, subregion int4, lon float8, lat float8, geom GEOGRAPHY(MULTIPOLYGON, 4326, 2), PRIMARY KEY(gid))" % self.schema
# create default tables
self.db_conn.run(query_hotspot)
self.db_conn.run(query_country)
def register(self):
''
#self.db_conn.register_model(Hotspot)
def parseShape(self):
path = "%s/global" % self.path
ts = int(time.time())
# here we run two transaction from the parsed files
for cmd in [{
'db': 'hotspot',
'parse': 'Global_24h.shp',
}, {
'db': 'country',
'parse': 'TM_WORLD_BORDERS-0.3.shp'
}]:
run = '/usr/local/bin/shp2pgsql -a -D -s 4326 -W latin1 -i %s/%s %s.%s | /usr/local/bin/psql -d %s' % (
path, cmd['parse'], self.schema, cmd['db'], self.schema)
p = subprocess.Popen(run, stdout=subprocess.PIPE, shell=True)
output, err = p.communicate()
print output
def connect():
""" Connect to the PostgreSQL database server """
try:
# connect to the PostgreSQL server
print('Connecting to the PostgreSQL database...')
# use a postgres connection string below
db = Postgres(
"host=localhost dbname=michaelmostachetti user=michaelmostachetti password=password"
)
# This is where you will run your four queries
# You can use either .one(), or .run() depending if you care
# about the return values of the queries
# Query 1
result1 = db.one("SELECT version();")
print(result1)
# Query 2
db.run("SELECT version();")
# Query 3
db.run("SELECT version();")
# Query 4
db.run("SELECT version();")
except:
print("An error occurred")
class Database:
def __init__(self):
_user = getenv("DATABASE.USER")
_secret = getenv("DATABASE.PASS")
_host = getenv("DATABASE.HOST")
_port = getenv("DATABASE.PORT")
_dbname = getenv("DATABASE.DB")
self.db = Postgres(url=f"postgresql://{_user}:{_secret}@{_host}:{_port}/{_dbname}")
def create_table(self, table: TableBase):
self.db.run(table.create_sql(drop=True))
def _agent_key_path_in_db(config, node_id, deployment_id):
postgres = Postgres(config)
get_node_data = "SELECT properties FROM nodes " \
"WHERE id = '{0}' " \
"AND deployment_id = '{1}';" \
"".format(node_id, deployment_id)
result = postgres.run_query(get_node_data)
pickled_buffer = result['all'][0][0]
properties = pickle.loads(pickled_buffer)
key_path = properties['cloudify_agent']['key']
ctx.logger.debug('Agent key path in db: {0}'.format(key_path))
return key_path
def count_words() -> dict:
words_dict = {}
psql = Postgres()
text = psql.get_posts_text()
for item in text:
item = clear_text(item[0])
for word in item.split():
words_dict[word] = words_dict.get(word, 0) + 1
psql.close()
return words_dict
def get_nth_article():
global STRING_DB
db = Postgres(STRING_DB)
selectList = db.all("SELECT * FROM url;")
allUrl = [item[1] for item in selectList]
for feed in allRssFeed:
print("parsing entries")
print(feed)
entries = feedparser.parse(feed).entries
for i in reversed(range(10)):
try:
url = entries[i].link
except Exception as e:
print("excp1 ", e)
continue
if url not in allUrl:
try:
db.run(
"INSERT INTO url (url) VALUES ('{}') ON CONFLICT (url) DO NOTHING;"
.format(url))
except Exception as e:
print("excp1", e)
article = Article(url)
article.download()
article.parse()
text = article.text
articleImage = article.top_image
articleTitle = article.title
articleUrl = article.url
string = text
string = re.sub(r"Zoom © .*[\n]*\(Motorsport-Total\.com\)", "",
string) # elimina
string = re.sub(
r"[0-9]+\. [A-Za-z]+ [0-9]+ - [0-9]+:[0-9]+ Uhr", "",
string) # elimina data
boldArticleContent = ""
######
#MULTITHREADING
######
multithreading = 1
if multithreading:
threading.Thread(target=sendTelegraph,
args=(articleImage, articleTitle,
boldArticleContent, articleUrl,
string, feed)).start()
else:
sendTelegraph(articleImage, articleTitle,
boldArticleContent, articleUrl, string, feed)
def __init__(self, model, start, end, init_customers,seed):
'''
Creates the behavior/utility model objects, sets internal variables to prepare for simulation, and creates
the database connection
:param model: name of the behavior/utility model parameters
:param start: start date for simulation
:param end: end date for simulation
:param init_customers: how many customers to create at start date
'''
self.model_name=model
self.start_date = start
self.end_date = end
self.init_customers=init_customers
self.monthly_growth_rate = 0.1
self.util_mod=UtilityModel(self.model_name)
local_dir = f'{os.path.abspath(os.path.dirname(__file__))}/conf/'
behavior_versions = glob.glob(local_dir+self.model_name+'_*.csv')
self.behavior_models = {}
self.model_list = []
for b in behavior_versions:
version = b[(b.find(self.model_name) + len(self.model_name)+1):-4]
if version in ('utility','population','country'):
continue
behave_mod=FatTailledBehaviorModel(self.model_name,seed,version)
self.behavior_models[behave_mod.version]=behave_mod
self.model_list.append(behave_mod)
local_dir = f'{os.path.abspath(os.path.dirname(__file__))}/conf/'
if len(self.behavior_models)>1:
self.population_percents = pd.read_csv(local_dir +self.model_name + '_population.csv',index_col=0)
self.util_mod.setChurnScale(self.behavior_models,self.population_percents)
self.population_picker = np.cumsum(self.population_percents)
self.country_lookup = pd.read_csv(local_dir +self.model_name + '_country.csv')
self.subscription_count = 0
self.tmp_sub_file_name = os.path.join(tempfile.gettempdir(),'{}_tmp_sub.csv'.format(self.model_name))
self.tmp_event_file_name=os.path.join(tempfile.gettempdir(),'{}_tmp_event.csv'.format(self.model_name))
con_string = f"postgresql://localhost/{os.environ['CHURN_DB']}?user={os.environ['CHURN_DB_USER']}&password={os.environ['CHURN_DB_PASS']}"
self.db = Postgres(con_string)
self.con = post.connect( database= os.environ['CHURN_DB'],
user= os.environ['CHURN_DB_USER'],
password=os.environ['CHURN_DB_PASS'])
class db():
"Wrapper for postgres https://postgres-py.readthedocs.org/en/2.1.2/index.html"
def __init__(self,password=password, user=user, dbname=dbname):
self.schema = schema
self.dbname = dbname
self.host = 'postgres://%s:%s@localhost/%s' % (password, user, dbname)
self.path = os.path.abspath(os.path.join(os.path.dirname(__file__),".."))
def connect(self):
print 'Connecting to %s ...' % self.host
self.db_conn = Postgres(self.host)
def init(self):
print self.schema
try:
# Enable PostGIS (includes raster)
self.db_conn.run("CREATE EXTENSION postgis")
# Enable Topology
self.db_conn.run("CREATE EXTENSION postgis_topology")
# fuzzy matching needed for Tiger
self.db_conn.run("CREATE EXTENSION fuzzystrmatch")
# Enable US Tiger Geocoder
self.db_conn.run("CREATE EXTENSION postgis_tiger_geocoder")
# catch all exception
except:
print 'PostGis is already installed'
query_hotspot = "CREATE TABLE IF NOT EXISTS %s.hotspot (gid serial, latitude numeric, longitude numeric, brightness numeric, scan numeric, track numeric, acq_date date, acq_time varchar(5), satellite varchar(1), confidence int4, version varchar(3), bright_t31 numeric, frp numeric, geom GEOGRAPHY(POINT,4326, 2), PRIMARY KEY(gid))" % self.schema
query_country = "CREATE TABLE IF NOT EXISTS %s.country (gid serial, fips varchar(2), iso2 varchar(2), iso3 varchar(3), un int4, name varchar(50), area int4, pop2005 int4, region int4, subregion int4, lon float8, lat float8, geom GEOGRAPHY(MULTIPOLYGON, 4326, 2), PRIMARY KEY(gid))" % self.schema
# create default tables
self.db_conn.run(query_hotspot)
self.db_conn.run(query_country)
def register(self):
''
#self.db_conn.register_model(Hotspot)
def parseShape(self):
path = "%s/global" % self.path
ts = int(time.time())
# here we run two transaction from the parsed files
for cmd in [{'db' : 'hotspot', 'parse' : 'Global_24h.shp', }, {'db' : 'country', 'parse': 'TM_WORLD_BORDERS-0.3.shp'}]:
run = '/usr/local/bin/shp2pgsql -a -D -s 4326 -W latin1 -i %s/%s %s.%s | /usr/bin/psql -d %s' % (path, cmd['parse'], self.schema, cmd['db'], self.dbname)
p = subprocess.Popen(run, stdout=subprocess.PIPE, shell=True)
output, err = p.communicate()
print output
class Test(unittest.TestCase):
postgres = Postgres(
connstring='postgresql://*****:*****@localhost:5432/test_user')
def test_parse(self):
connstring = 'postgresql://*****:*****@localhost:5432/test_user'
user, pwd, host, port, db = self.postgres._parse(connstring)
self.assertEqual(user, 'test_user')
self.assertEqual(pwd, 'test')
self.assertEqual(host, 'localhost')
self.assertEqual(port, 5432)
self.assertEqual(db, 'test_user')
def test_connect(self):
conn = self.postgres.get_connection()
self.assertIsInstance(conn, psycopg2.extensions.connection)
def test_query(self):
query = """
SELECT * FROM advertising
WHERE id=1
LIMIT 1
"""
rows = self.postgres.execute_query(query)
row_id = rows[0]['id']
self.assertIsInstance(rows, list)
self.assertIsInstance(rows[0], psycopg2.extras.DictRow)
self.assertEqual(row_id, 1)
def test_disconnect(self):
pass
def setUp(self): # override
self.db = Postgres(DATABASE_URL)
self.db.run("DROP SCHEMA IF EXISTS public CASCADE")
self.db.run("CREATE SCHEMA public")
self.db.run("CREATE TABLE foo (bar text, baz int)")
self.db.run("INSERT INTO foo VALUES ('buz', 42)")
self.db.run("INSERT INTO foo VALUES ('biz', 43)")
def get_last_event_id(db: postgres.Postgres, room_id: str,
before: datetime) -> Optional[str]:
return db.one(
"SELECT event_id FROM events WHERE received_ts <= %(before)s AND room_id=%(room_id)s ORDER BY received_ts DESC LIMIT 1",
before=int(before.timestamp() * 1000),
room_id=room_id,
)
class CardOracleTextProcessor:
def __init__(self, db_url, batchsize=1000, processor_limit=5):
self.db_url = db_url
self.db = Postgres(url=db_url)
self.batchsize = batchsize
self.processor_limit = processor_limit
self.process_count = 0
self.processors = []
self.offset = 0
def get_all_cards_from_db(self):
return self.db.all(
f"select id,oracle_text from cards where exists( select 1 from jsonb_each_text(cards.legalities) j where j.value not like '%not_legal%') and lang='en' limit {self.batchsize}"
)
def get_all_cards_from_db_with_offset(self):
return self.db.all(
f"select id,oracle_text from cards where exists( select 1 from jsonb_each_text(cards.legalities) j where j.value not like '%not_legal%') and lang='en' limit {self.batchsize} offset {self.offset}"
)
def limit_processes(self):
if len(self.processors) % self.processor_limit == 0:
print('waiting for processing jobs to finish')
for processor in self.processors:
processor.join()
print(f'{processor.name} finished.')
self.processors = []
def setup_and_start_card_processor_process(self, cards):
cards_copy = copy(cards)
card_preprocessor = CardProcessorProcess(cards=cards_copy,
db_url=self.db_url)
card_preprocessor.start()
self.processors.append(card_preprocessor)
return card_preprocessor
def process_all_cards(self):
print(f"Pulling first batch of {self.batchsize} cards from database")
cards = self.get_all_cards_from_db()
while len(cards):
self.offset += 1
self.limit_processes()
print('setting up card processing thread')
self.setup_and_start_card_processor_process(cards)
print(f"Pulling next 1000 cards (batch #{self.offset})")
cards = self.get_all_cards_from_db_with_offset()
class TestCursorFactory(WithData):
def setUp(self): # override
self.db = Postgres(DATABASE_URL)
self.db.run("DROP SCHEMA IF EXISTS public CASCADE")
self.db.run("CREATE SCHEMA public")
self.db.run("CREATE TABLE foo (bar text, baz int)")
self.db.run("INSERT INTO foo VALUES ('buz', 42)")
self.db.run("INSERT INTO foo VALUES ('biz', 43)")
def test_NamedDictCursor_results_in_namedtuples(self):
Record = namedtuple("Record", ["bar", "baz"])
expected = [Record(bar="biz", baz=43), Record(bar="buz", baz=42)]
actual = self.db.all("SELECT * FROM foo ORDER BY bar")
assert actual == expected
def test_namedtuples_can_be_unrolled(self):
actual = self.db.all("SELECT baz FROM foo ORDER BY bar")
assert actual == [43, 42]
class PostgresHelper:
#PostgreSQL özellikle store procedure çalıştırmak için yardımcı sınıf
def __init__(self, server, dbname, username):
self.server = server
self.dbname = dbname
self.username = username
self.db = Postgres("postgres://" + self.username + "@" + self.server +
"/" + self.dbname)
def executeCommand(self, commandStr):
self.db.run(sql=commandStr)
def getMultipleCursor(self, spname, parameters, refcursors):
paramstr = ""
cursorstr = ""
for refcursor in refcursors:
cursorstr += "'" + refcursor + "',"
for param in parameters:
paramstr += param + ","
if paramstr.endswith(','):
paramstr = paramstr[:-1]
if cursorstr.endswith(','):
cursorstr = cursorstr[:-1]
data = {}
with self.db.get_cursor() as cursor:
cursor.run("select " + spname + "(" + paramstr + "," + cursorstr +
");")
for refcursor in refcursors:
fetchstr = 'FETCH ALL IN "' + refcursor + '";'
tempdata = cursor.all(fetchstr)
##print(tempdata)
data[refcursor] = tempdata
return data
## kayit = self.db.run(sql="select "+spname+"("+paramstr+","+cursorstr+");")
def getSingleCursor(self, spname, parameters):
return self.getMultipleCursor(spname=spname,
parameters=parameters,
refcursors=["rc1"])
class Crime():
def __init__(self):
self.p = Postgres(False)
def get_data(self):
query = """
SELECT * FROM congressional_districts_data
"""
res = self.p.query_list(query)
d = pd.DataFrame(res,
columns=[
'gid', 'gid_theft', 'gid_not_theft',
'neigh_theft', 'neigh_not_theft'
])
return d
def plot(self, x, y, y_hat):
plt.scatter(x, y, color='black')
plt.plot(x, y_hat, color='blue', linewidth=3)
plt.xticks(())
plt.yticks(())
plt.show()
def run_spatial_ols(self):
data = self.get_data()
y = data['gid_theft'].values
x = data[['gid_not_theft', 'neigh_not_theft']]
y.resize(len(y), 1)
regr = linear_model.LinearRegression()
regr.fit(x, y)
print "spatial ols", mse(y, regr.predict(x))
# 9888290.8158
def run_ols(self):
data = self.get_data()
y = data['gid_theft'].values
x = data['gid_not_theft'].values
y.resize(len(y), 1)
x.resize(len(x), 1)
regr = linear_model.LinearRegression()
regr.fit(x, y)
print "ols", mse(y, regr.predict(x))
# 10987133.1401
def run(self):
pass
def __init__(self, drop_tables=False):
super(PostgreSQLDB, self).__init__()
if os.environ.get('DOCKERCLOUD_SERVICE_HOSTNAME', None) is not None:
self.db = Postgres(
u"postgres://*****:*****@postgres/hashes")
else:
# self.db = Postgres(u"postgres://*****:*****@localhost/postgres")
self.db = Postgres(
u"postgres://*****:*****@pervasivesounds.com/hashes"
)
if drop_tables:
self.db.run("DROP TABLE IF EXISTS %s CASCADE" %
self.SONGS_TABLENAME)
self.db.run("DROP TABLE IF EXISTS %s CASCADE" %
self.FINGERPRINTS_TABLENAME)
self.db.run(self.CREATE_SONGS_TABLE)
self.db.run(self.CREATE_FINGERPRINTS_TABLE)
def init_DB():
global STRING_DB
db = Postgres(STRING_DB)
db.run(
"CREATE TABLE IF NOT EXISTS url (id serial PRIMARY KEY, url varchar(100) unique );"
)
db.run(
"CREATE TABLE IF NOT EXISTS feed (id serial PRIMARY KEY, url varchar(100) unique);"
)
db.run(
"CREATE TABLE IF NOT EXISTS users (id serial PRIMARY KEY, chat_id int unique, name varchar(50), time_added varchar(20));"
)
class TestCursorFactory(WithData):
def setUp(self): # override
self.db = Postgres(DATABASE_URL)
self.db.run("DROP SCHEMA IF EXISTS public CASCADE")
self.db.run("CREATE SCHEMA public")
self.db.run("CREATE TABLE foo (bar text, baz int)")
self.db.run("INSERT INTO foo VALUES ('buz', 42)")
self.db.run("INSERT INTO foo VALUES ('biz', 43)")
def test_NamedDictCursor_results_in_namedtuples(self):
Record = namedtuple("Record", ["bar", "baz"])
expected = [Record(bar="biz", baz=43), Record(bar="buz", baz=42)]
actual = self.db.all("SELECT * FROM foo ORDER BY bar")
assert actual == expected
def test_namedtuples_can_be_unrolled(self):
actual = self.db.all("SELECT baz FROM foo ORDER BY bar")
assert actual == [43, 42]
class WithSchema(TestCase):
def setUp(self):
self.db = Postgres(DATABASE_URL, cursor_factory=SimpleDictCursor)
self.db.run("DROP SCHEMA IF EXISTS public CASCADE")
self.db.run("CREATE SCHEMA public")
def tearDown(self):
self.db.run("DROP SCHEMA IF EXISTS public CASCADE")
del self.db
def db():
dburl = os.environ['DATABASE_URL']
maxconn = int(os.environ['DATABASE_MAXCONN'])
db = Postgres(dburl, maxconn=maxconn)
# register hstore type
with db.get_cursor() as cursor:
psycopg2.extras.register_hstore(cursor, globally=True, unicode=True)
db.register_model(Community)
db.register_model(Participant)
return db
def connect(self): print 'Connecting to %s ...' % self.host self.db_conn = Postgres(self.host)
from collections import OrderedDict
import os
import csv
import re
from postgres import Postgres
db = Postgres(os.environ.get('DATABASE_URL'))
csv_reader = csv.DictReader(open('./data/ParcelCentroids.csv'))
for index, row in enumerate(csv_reader):
if re.search('^\d+$', row['PVANUM']):
db.run("INSERT INTO parcels VALUES (%s, %s, %s, %s)", (row['X'], row['Y'], row['ADDRESS'], row['PVANUM']))
else:
print row['PVANUM']
from collections import OrderedDict
import os
from postgres import Postgres
db = Postgres(os.environ.get('DATABASE_URL'))
columns = OrderedDict()
columns['x'] = 'float8'
columns['y'] = 'float8'
columns['address'] = 'varchar(255)'
columns['parcel_id'] = 'int'
columns_sql = ['%s %s' % (key, value) for key, value in columns.items()]
create_table_sql = 'CREATE TABLE parcels(%s)' % ', '.join(columns_sql)
db.run('DROP TABLE IF EXISTS parcels')
db.run(create_table_sql)
from collections import OrderedDict
import os
from postgres import Postgres
POSTGRES_URL = os.environ.get('DATABASE_URL')
db = Postgres(POSTGRES_URL)
columns = OrderedDict()
columns['QUERY_ADDRESS'] = 'varchar(255)'
columns['RETURNED_ADDRESS'] = 'varchar(255)'
columns['ES_SCORE'] = 'float8'
columns['ES_LAT'] = 'float8'
columns['ES_LONG'] = 'float8'
columns['GOOG_LAT'] = 'float8'
columns['GOOG_LONG'] = 'float8'
columns['DISTANCE'] = 'float8'
columns['ES_GEOCODED_AT'] = 'timestamptz'
columns['GOOG_GEOCODED_AT'] = 'timestamptz'
columns_sql = ['%s %s' % (key, value) for key, value in columns.items()]
create_table_sql = 'CREATE TABLE geocoder (%s)' % ', '.join(columns_sql)
db.run(create_table_sql)
"""A `simple cursor`_ that returns tuples.
"""
class SimpleNamedTupleCursor(SimpleCursorBase, NamedTupleCursor):
"""A `simple cursor`_ that returns namedtuples.
"""
class SimpleDictCursor(SimpleCursorBase, RealDictCursor):
"""A `simple cursor`_ that returns dicts.
"""
def isexception(obj):
"""Given an object, return a boolean indicating whether it is an instance
or subclass of :py:class:`Exception`.
"""
if isinstance(obj, Exception):
return True
if isclass(obj) and issubclass(obj, Exception):
return True
return False
if __name__ == '__main__':
from postgres import Postgres
db = Postgres("postgres://jrandom@localhost/test")
db.run("DROP SCHEMA IF EXISTS public CASCADE")
db.run("CREATE SCHEMA public")
import doctest
doctest.testmod()
parser.feed(target)
return parser.markdown
if __name__ == "__main__":
from sys import argv, stdin, stdout
if len(argv) == 1:
stdout.write(bbcode_to_markdown(stdin.read().decode('utf-8', 'replace')).encode('utf-8'))
else:
from postgres import Postgres
_, db_uri, db_table, id_column, db_column = argv
info = {"table": db_table, "column": db_column, "id": id_column}
db = Postgres(db_uri)
count = db.one('SELECT COUNT(*) FROM "{table}"'.format(**info))
query = 'UPDATE "{table}" SET "{column}" = %(markdown)s WHERE "{id}" = %(id)s'.format(**info)
failures = []
for i, row in enumerate(db.all('SELECT "{id}", "{column}" FROM "{table}"'.format(**info))):
print("\x1b[0G{done}/{total}".format(done=i, total=count), end="")
stdout.flush()
try:
db.run(query, {"id": getattr(row, id_column), "markdown": bbcode_to_markdown(getattr(row, db_column))})
except Exception as e:
print()
print(e)
failures.append(getattr(row, id_column))
def __init__(self, url):
Postgres.__init__(self, url)
continue
output += """,
// {0}.get{1}()""".format(table.java_name_lc, column.java_name)
output += """\n//);
}
}"""
return output
# The contents of db_string.txt should be: postgres://curtin_lims_user:password@localhost/curtin_lims
db_string = ''
with open(script_path + '/db_string.txt') as f:
db_string = f.readlines()[0]
db = Postgres(db_string)
tables = []
table_names = db.all("SELECT table_name FROM INFORMATION_SCHEMA.Tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE' AND table_name NOT IN ('spatial_ref_sys', 'gt_pk_metadata_table')")
reference_descriptions = db.all("""
SELECT
kcu.table_name as referencing_table,
ccu.table_name as referenced_table,
kcu.column_name
FROM
information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY'
Call this when you update state in the database and you want to keep
instance attributes in sync. Note that the only attributes we can set
here are the ones that were given to us by the :py:mod:`psycopg2`
composite caster machinery when we were first instantiated. These will
be the fields of the composite type for which we were registered, which
will be column names for table and view types.
"""
unknown = []
for name in kw:
if name not in self.__read_only_attributes:
unknown.append(name)
if unknown:
raise UnknownAttributes(unknown)
self.__dict__.update(**kw)
if __name__ == '__main__':
from postgres import Postgres
db = Postgres("postgres://jrandom@localhost/test")
db.run("DROP SCHEMA IF EXISTS public CASCADE")
db.run("CREATE SCHEMA public")
db.run("DROP TABLE IF EXISTS foo CASCADE")
db.run("CREATE TABLE foo (bar text, baz int)")
db.run("INSERT INTO foo VALUES ('blam', 42)")
db.run("INSERT INTO foo VALUES ('whit', 537)")
db.run("CREATE VIEW bar AS SELECT bar FROM foo")
import doctest
doctest.testmod()
def setUp(self):
self.db = Postgres(DATABASE_URL, cursor_factory=SimpleDictCursor)
self.db.run("DROP SCHEMA IF EXISTS public CASCADE")
self.db.run("CREATE SCHEMA public")
eval_string = "sample.set_%s(datetime_value)" % (heading)
elif value_type == INT_TYPE or value_type == DECIMAL_TYPE:
eval_string = "sample.set_%s(%s)" % (heading, value)
if validation_error:
sys.exit()
eval(eval_string)
samples.append(sample)
# Register and insert into database in groups of 10:
sample_groups = izip_longest(*(iter(samples),) * 10)
db = Postgres(postgres_connection_string)
for sample_group in sample_groups:
sample_group = filter(None, sample_group)
igsns = igsnClient.register_samples(sample_group)
insert_sql = "INSERT INTO geosample (mount_type_id, igsn, geosample_name, location) VALUES"
for i in range(0, len(igsns)):
# Insert geosample record into database
sample = sample_group[i]
igsn = igsns[i]
# WARNING: note that mount_type_id is not being set. All of this information should be in the IGSN record anyway.
db.run(insert_sql + "(1, %(igsn)s, %(geosample_name)s, ST_PointFromText('POINT({0} {1})', 4326))".format(sample.get_longitude(), sample.get_latitude()),
{
'igsn': igsn,
'geosample_name': sample.get_name()
"""
script for resetting the database whenever new data files are uploaded
"""
import urlparse
from postgres import Postgres
import psycopg2
import os
distances_file_name = "./data/distances.csv"
beer_names_file_name = "./data/metadata.csv"
username_file_name = "./data/usernames.csv"
reviews_file_name = "./data/reviews.csv"
db_location = os.environ.get("DATABASE_URL", "postgres://[email protected]:5432/postgres")
db = Postgres(db_location)
try:
db.run("DROP TABLE beer_names")
except psycopg2.ProgrammingError:
pass
finally:
db.run("CREATE TABLE beer_names(beer_id int PRIMARY KEY NOT NULL, beer_name varchar, beer_image_url varchar)")
with open (beer_names_file_name,"r") as infile:
for line in infile:
comma_seperated_values = line.strip().split(',')
values = {
"beer_id" : comma_seperated_values[0],
"beer_name" : comma_seperated_values[1],
"beer_image_url": comma_seperated_values[2]
}
