def requestDB(requete='select * from ',tableName='planet_osm_line',base='champ',
limit=None,only=False,verbose=False):
"""
Function that execute an sql request into a postgres database.
---
parameters :
string requete : the request will be executed.
string tableName : the name of the table will be request if only is false.
string base : name of the database
int limit : integer, request limit option.
produces :
string requeteResult : request result.
"""
db = DB(dbname=base)
if verbose :
tablesList = db.get_tables()
print('Tables :')
printPuces(tablesList)
print('\n')
headerLinesOSM = db.get_attnames(tableName)
print('Table header :')
printPuces(headerLinesOSM)
print('\n')
if only :
requeteResult = db.query(requete).getresult()
elif limit is None :
requeteResult = db.query(requete+tableName).getresult()
else :
requeteResult = db.query(requete+tableName+' limit '+str(limit)).getresult()
return requeteResult
def get_tables():
db = DB(dbname = vDatabase, host = vHost)
table_list = []
if options.usertables:
table_list = db.get_tables()
else:
table_list = db.get_tables('system')
db.close()
if vSchema:
tables = []
regex = "^" + vSchema + "\."
for table in table_list:
if re.match(regex, table, re.I):
tables.append(table)
else:
tables = table_list
return tables
def healthcheck(self):
self._generic_healthcheck()
spec = self.get_spec()
env = {e['name']: e['value'] for e in spec['containers'][0]['env']}
user = env['POSTGRES_USER']
passwd = env['POSTGRES_PASSWORD']
db = DB(dbname=user,
host=self.host,
port=5432,
user=user,
passwd=passwd)
sql = "create table test_table(id serial primary key, name varchar)"
db.query(sql)
assert_in('public.test_table', db.get_tables())
class PostGreDBConnector:
"""PostGreDBConnector opens a PostGre DB connection. Different functions allow you to add, delete or update
documents in PostGre DB."""
def __init__(self):
"""Connecting to localhost (default host and port [localhost, 4532]) PostGre DB and initializing needed data
base and tables."""
try:
print("Connecting to PostGre DB...")
self.__db = DB(dbname='testdb', host='localhost', port=5432, user='******', passwd='superuser')
print("PostGre DB connection successfully built.")
except ConnectionError:
print("PostGre DB connection could not be built.")
self.delete_all_data()
self.drop_all_tables()
def close_connection(self):
self.__db.close()
def create_schema(self, schema_name):
self.__db.query("CREATE SCHEMA " + schema_name)
self.__create_tables(schema_name)
self.__create_functions(schema_name)
def __create_tables(self, schema):
"""Create needed tables for RDF parsing."""
schema += "."
self._add_table("CREATE TABLE " + schema + "texts (id serial primary key, title text)")
self._add_table(
"CREATE TABLE " + schema + "bscale (id serial primary key, bscale text, nominal bool, ordinal bool, interval bool)")
self._add_table("CREATE TABLE " + schema + "bsort (id serial primary key, bsort text)")
self._add_table("CREATE TABLE " + schema + "pattern (id serial primary key, pattern text)")
self._add_table("CREATE TABLE " + schema + "single_pattern (id serial primary key, single_pattern text)")
self._add_table("CREATE TABLE " + schema + "snippets (id serial primary key, snippet text)")
# relations
self._add_table("CREATE TABLE " + schema + "has_attribute (bsort_id int, bscale_id integer[], aggregation int)")
self._add_table("CREATE TABLE " + schema + "has_object (bscale_id int, pattern_id integer[], aggregation int)")
self._add_table(
"CREATE TABLE " + schema + "pattern_single_pattern (pattern_id int, single_pattern_id integer[], aggregation int)")
self._add_table("CREATE TABLE " + schema + "texts_snippets (text_id int primary key, snippet_id integer[], aggregation int)")
self._add_table(
"CREATE TABLE " + schema + "snippet_offsets (id serial primary key,"
" single_pattern_id int, snippet_id int, offsets integer[][], aggregation int)")
# adjective and verb extractions
self._add_table("CREATE TABLE " + schema + "subject_occ (id serial primary key, subject text, count int)")
self._add_table("CREATE TABLE " + schema + "adjective_occ (id serial primary key, adjective text, count int)")
self._add_table("CREATE TABLE " + schema + "verb_occ (id serial primary key, verb text, count int)")
self._add_table("CREATE TABLE " + schema + "object_occ (id serial primary key, object text, count int)")
self._add_table("CREATE TABLE " + schema + "subject_adjective_occ (id serial primary key, subject int, adjective int, count int, pmi float)")
self._add_table("CREATE TABLE " + schema + "subject_object_occ (id serial primary key, subject int, object int, count int, pmi float)")
self._add_table("CREATE TABLE " + schema + "object_verb_occ (id serial primary key, object int, verb int, count int, pmi float)")
self._add_table("CREATE TABLE " + schema + "subject_verb_occ (id serial primary key, subject int, verb int, count int, pmi float)")
# correlating pattern
self._add_table("CREATE TABLE " + schema + "bscale_single_pattern (id serial primary key, bscale_id int, single_pattern_id int, single_pattern text, count int)")
self._add_table(
"CREATE TABLE " + schema + "correlating_pattern (id serial primary key, pattern_a int, pattern_b int, count int, pmi float)")
def __create_functions(self, schema):
"""Create all necessary functions to aggregate the results saved in the database."""
schema += "."
self.add_function(schema + "aggregate_texts_snippets", "SELECT text_id, array_length(snippet_id, 1) FROM " + schema + "texts_snippets")
self.add_function(schema + "aggregate_snippet_offsets", "SELECT id, array_length(offsets, 1) FROM " + schema + "snippet_offsets")
def add_function(self, name, function):
"""Create a new function in the db."""
create_function = "CREATE FUNCTION "
returns = "() RETURNS SETOF RECORD AS "
lang = " LANGUAGE SQL"
query = create_function + name + returns + add_quotes(function) + lang
self.__db.query(query)
def _add_table(self, query):
"""Create a new table with a query."""
self.__db.query(query)
def add_table(self, schema, name, rows):
"""Create a new table with a name and rows given in query form."""
create_table = "CREATE TABLE "
query = create_table + schema + "." + name + rows
self.__db.query(query)
def insert(self, schema, table, row):
"""Insert a new row element into a specified table."""
return self.__db.insert(schema + "." + table, row)
def is_in_table(self, schema, table, where_clause):
"""Returns whether a row already exists in a table or not."""
select = "SELECT * FROM "
where = " WHERE "
q = select + schema + "." + table + where + where_clause
result = self.__db.query(q).dictresult()
if len(result) > 0:
return True
else:
return False
def update(self, schema, table, values, where_clause):
"""Update an entry in a specified table."""
UPDATE = "UPDATE "
SET = " SET "
WHERE = " WHERE "
query = UPDATE + schema + "." + table + SET + values + WHERE + where_clause
self.query(query)
def get(self, schema, table, where_clause, key):
"""Return the key of a specific item in a table."""
select = "SELECT "
_from = " FROM "
where = " WHERE "
q = select + key + _from + schema + "." + table + where + where_clause
result = self.__db.query(q).dictresult()
if len(result) > 0:
return result[0][key]
else:
return None
def get_data_from_table(self, schema, table):
"""Gets all data available in a specific table."""
return self.__db.query("SELECT * FROM " + schema + "." + table).dictresult()
def get_id(self, schema, table, where_clause):
"""Return the id of an item in a table. If found return id number of found item, else None."""
select = "SELECT id FROM "
where = " WHERE "
q = select + schema + "." + table + where + where_clause
result = self.__db.query(q).dictresult()
if len(result) > 0:
return result[0]['id']
else:
return None
def delete_from_table(self, schema, table, row):
"""Delete a row element form a specific table."""
return self.__db.delete(schema + "." + table, row)
def delete_data_in_table(self, schema, table):
"""Delete all data in a specific table."""
self.__db.truncate(schema + "." + table, restart=True, cascade=True, only=False)
def delete_all_data(self):
"""Deletes all data from all existing tables."""
tables = self.get_tables()
for table in tables:
table_name = str(table)
self.__db.truncate(table_name, restart=True, cascade=True, only=False)
def get_tables(self):
"""Get all available tables in the database."""
return self.__db.get_tables()
def get_attributes(self, schema, table):
"""Get all attributes of a specified table."""
return self.__db.get_attnames(schema + "." + table)
def drop_table(self, schema, table):
"""Drops a specified table."""
query = "DROP TABLE "
self.__db.query(query + schema + "." + table)
def drop_all_tables(self):
"""Drops all existing tables."""
tables = self.get_tables()
table_names = ""
if len(tables) > 0 :
for ind, table in enumerate(tables):
if ind == 0:
table_names = str(table)
else:
table_names = table_names + ", " + str(table)
self.__db.query("DROP TABLE " + table_names)
else:
print("Nothing to delete.")
def get_all(self, schema, table, attribute):
"""Gets one or more attributes of all entries from a specified table."""
select = "SELECT "
_from = " FROM "
query = select + attribute + _from + schema + "." + table
return self.__db.query(query).dictresult()
def query(self, query):
"""Sends a query to the database."""
result = self.__db.query(query)
if result is not None:
if not isinstance(result, str):
return result.dictresult()
else:
return result
from pg import DB
db = DB(dbname='test',
host='localhost',
port=5432,
user='******',
passwd='password')
if ('public.movies' not in db.get_tables()):
db.query(
'create table movies(id serial primary key, title varchar UNIQUE, imdb_url varchar, rating decimal, ratings_count integer)'
)
def insert_movie(title, imdb_url, rating, ratings_count):
db.insert('movies',
title=title,
imdb_url=imdb_url,
rating=rating,
ratings_count=ratings_count)
#
# print("Using psycopg2…")
#
# myConnection = psycopg2.connect( host=hostname, user=username, password=password, dbname=database )
# cur = myConnection.cursor()
# cwd = os.getcwd()
# print(cwd)
# cur.execute("""INSERT INTO dummy_table VALUES(2, 'ken')""")
# cur.execute(r"""CREATE TABLESPACE fortnite_db LOCATION '{}';""".format(cwd))
# myConnection.commit()
# doQuery( myConnection )
#
# myConnection.close()
# !/usr/bin/python
# -*- coding: utf-8 -*-
import psycopg2
import sys
from pg import DB
db = DB(dbname='testdb', host='localhost', port=5432, user='******', passwd='tiger')
db.query("""CREATE TABLE weather (
city varchar(80),
temp_lo int, temp_hi int,
prcp float8,
date date)""")
print(db.get_tables())
class PGSQL_DB(DB_Abstract):
conn = None
def __init__(self, dbname, dbhost, dbport, dbuser, dbpass):
try:
self.conn = DB(dbname=dbname,
host=dbhost,
port=dbport,
user=dbuser,
passwd=dbpass)
except Exception as ex:
print(ex)
self.conn = None
if (None != self.conn):
tables = self.conn.get_tables()
if (not ("public.users" in tables)):
try:
self.conn.query(
"CREATE TABLE users(uid bigint primary key, name text, bio text)"
)
except Exception as ex:
print(ex)
self.conn = None
if (not ("public.tweets" in tables)):
try:
self.conn.query(
"CREATE TABLE tweets(tid bigint primary key, author_id bigint, parent_id bigint, timestamp bigint, text text)"
)
except Exception as ex:
print(ex)
self.conn = None
def insert_user(self, user):
if (not (type(user) is User)):
print("type isn't user")
return 400
if ("" == user.user_id or "" == user.name):
print("empty user")
return 400
if (None == self.conn):
print("no connection")
return 400
try:
self.conn.insert('users', {
'uid': user.user_id,
'name': user.name,
'bio': user.bio
})
except Exception as ex:
print(ex)
return 400
return 200
def insert_tweet(self, tweet):
if (not (type(tweet) is Tweet)):
print("type isn't Tweet")
return 400
if ("" == tweet.tweet_id or "" == tweet.author_id
or "" == tweet.parent_id or "" == tweet.timestamp
or "" == tweet.text):
print("empty tweet")
return 400
if (None == self.conn):
print("no connection")
return 400
try:
self.conn.insert(
'tweets', {
'tid': tweet.tweet_id,
'author_id': tweet.author_id,
'parent_id': tweet.parent_id,
'timestamp': tweet.timestamp,
'text': tweet.text
})
except Exception as ex:
print(ex)
return 400
return 200
def get_user_by_id(self, user_id):
if ("" == user_id):
print("empty user_id")
return None
if (None == self.conn):
print("no connection")
return None
try:
u = self.conn.get('users', {'uid': user_id})
except Exception as ex:
print(ex)
return None
user = User()
user.user_id = u['uid']
user.name = u['name']
user.bio = u['bio']
return user
def get_user_tweets(self, uid):
if ("" == uid):
print("empty uid")
return None
if (None == self.conn):
print("no connection")
return None
try:
t = self.conn.get_as_list('tweets',
where="author_id = {0}".format(uid))
except Exception as ex:
print(ex)
return None
tweets = []
for tweet in t:
new_tweet = Tweet()
new_tweet.tweet_id = tweet[0]
new_tweet.author_id = tweet[1]
new_tweet.parent_id = tweet[2]
new_tweet.timestamp = tweet[3]
new_tweet.text = tweet[4]
tweets.append(new_tweet)
if (0 == len(tweets)):
return None
return tweets
def get_tweet_by_id(self, tweet_id):
if (None == self.conn):
print("no connection")
return None
try:
t = self.conn.get('tweets', {'tid': tweet_id})
except Exception as ex:
print(ex)
return None
tweet = Tweet()
tweet.tweet_id = t['tid']
tweet.author_id = t['author_id']
tweet.parent_id = t['parent_id']
tweet.timestamp = t['timestamp']
tweet.text = t['text']
return tweet
def get_replies_by_id(self, tweet_id):
if (None == self.conn):
print("no connection")
return None
try:
t = self.conn.get_as_list('tweets',
where="parent_id = " + tweet_id)
except Exception as ex:
print(ex)
return None
tweets = []
for tweet in t:
if (tweet[0] != tweet[2]):
new_tweet = Tweet()
new_tweet.tweet_id = tweet[0]
new_tweet.author_id = tweet[1]
new_tweet.parent_id = tweet[2]
new_tweet.timestamp = tweet[3]
new_tweet.text = tweet[4]
tweets.append(new_tweet)
if (0 == len(tweets)):
return None
return tweets
