class PostgresqlAdapter(object):
def __init__(self, dbname, user, passwd, host="localhost", port=5432):
self._db = DB(dbname=dbname,
host=host,
port=port,
user=user,
passwd=passwd)
def create(self, inhabitant):
record = self._db.insert('inhabitants', data=inhabitant)
return _normalize(record)
def find_all(self):
all_inhabitants = self._db.query(
'select * from inhabitants').dictresult()
return [_normalize(x) for x in all_inhabitants]
def find_by_id(self, id):
inhabitant = self._find_by_id(id)
return _normalize(inhabitant)
def find_by_name(self, name):
all_inhabitants = self._db.query(
"SELECT * FROM inhabitants " +
"WHERE data ->> 'FirstName' ILIKE '%' || $1 || '%' OR " +
" data ->> 'LastName' ILIKE '%' || $1 || '%'",
(name, )).dictresult()
return [_normalize(x) for x in all_inhabitants]
def delete(self, id):
try:
self._db.delete('inhabitants', id=int(id))
except ValueError:
pass
def update(self, inhabitant):
id = inhabitant["Id"]
self._find_by_id(id)
del inhabitant["Id"]
stored = self._db.update('inhabitants', id=id, data=inhabitant)
return _normalize(stored)
def _find_by_id(self, id):
try:
return self._db.get('inhabitants', int(id))
except (ValueError, DatabaseError):
raise NoRecordFound()
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
