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