def draw_graph(name, period=None):
if period == 'm':
print('Month')
old_date = date.today() - timedelta(days=30)
with CursorFromConnectionPool() as cursor:
cursor.execute(
'SELECT weight, date FROM {} WHERE date BETWEEN %s AND %s ORDER BY date ASC'
.format(name), (old_date, date.today()))
measurements = cursor.fetchall()
elif period == 'y':
print('Year')
old_date = date.today() - timedelta(days=365)
with CursorFromConnectionPool() as cursor:
cursor.execute(
'SELECT weight, date FROM {} WHERE date BETWEEN %s AND %s ORDER BY date ASC'
.format(name), (old_date, date.today()))
measurements = cursor.fetchall()
else:
print('All')
with CursorFromConnectionPool() as cursor:
cursor.execute(
'SELECT weight, date FROM {} ORDER BY date ASC'.format(name))
measurements = cursor.fetchall()
weight = [float(row[0]) for row in measurements]
dates = [row[1] for row in measurements]
plt.plot(dates, weight)
plt.ylabel('Weight')
plt.xlabel('Date')
plt.grid(True)
plt.xticks(rotation=60)
plt.show()
def test_check_users_table_exists(self):
with CursorFromConnectionPool() as cursor:
cursor.execute('DROP SCHEMA public CASCADE;')
cursor.execute('CREATE SCHEMA public;')
user_functions.check_users_table_exists()
with CursorFromConnectionPool() as cursor:
cursor.execute(
"SELECT EXISTS(SELECT * FROM information_schema.tables WHERE table_name='users')"
)
table_exists = cursor.fetchone()[0]
self.assertTrue(table_exists)
def save_to_db(self):
# This is creating a new connection pool every time! Very expensive...
with CursorFromConnectionPool() as cursor:
cursor.execute(
'INSERT INTO stock_data (etf,Date,Open,High,Low,Close,Volume,OpenInt) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)',
(self.etf, self.date, self.open, self.high, self.low,
self.close, self.volume, self.openint))
def load_from_db_by_screen_name(cls, screen_name):
with CursorFromConnectionPool() as cursor:
cursor.execute("SELECT * FROM users WHERE screen_name=%s", (screen_name,))
user_data = cursor.fetchone()
if user_data:
return User(screen_name=user_data[1], oauth_token=user_data[2],
oauth_token_secret=user_data[3], id=user_data[0])
def load_from_db_by_email(cls, email):
#with connection_pool.getconn() as connection: ####
#with ConnectionFromPool() as connection: #####
with CursorFromConnectionPool() as cursor:
cursor.execute("SELECT * FROM users WHERE users.email=%s ;", (email,))
user_data = cursor.fetchone() #Used to Retieve first row in table
return cls(email=user_data[1], first_name=user_data[2] , last_name=user_data[3], id=user_data[0])
def load_from_db_by_screen_name(cls, screen_name):
with CursorFromConnectionPool() as cursor:
# Note the (email,) to make it a tuple!
cursor.execute('SELECT * FROM users WHERE screen_name=%s', (screen_name,))
user_data = cursor.fetchone()
if user_data is not None:
return cls(screen_name=user_data[1], id=user_data[0],
oauth_token=user_data[2], oauth_token_secret= user_data[3])
async def delete_user(id):
try:
with CursorFromConnectionPool() as cursor:
query = """delete from users where id = %s"""
cursor.execute(query, (id, ))
except Exception:
return 'Deletion wasnt successful'
return 'Data deleted successfully'
def load_from_db_by_email(cls,email):
with CursorFromConnectionPool() as cursor:
cursor.execute("Select * from users where email=%s",(email,))
user_data=cursor.fetchone()
return cls(user_data[1],user_data[2],user_data[3],user_data[0])
def load_from_db_by_email(cls, email):
#with psycopg2.connect(user='******', password='******', database='learning', host='localhost') as connection:
#with connection_pool.getconn() as connection:
#connection = connection_pool.getconn()
with CursorFromConnectionPool() as cursor:
cursor.execute('SELECT * FROM users WHERE email=%s', (email, ))
user_data = cursor.fetchone()
return cls(user_data[1], user_data[2], user_data[3], user_data[0])
def home():
with CursorFromConnectionPool() as cursor:
cursor.execute('SELECT * FROM users')
posts = [
dict(id=row[0], email=row[1], first_name=row[2], last_name=row[3])
for row in cursor.fetchall()
]
return render_template('index.html', post=posts)
def load_from_db_by_email(cls, email):
with CursorFromConnectionPool() as cursor:
# Note the (email,) to make it a tuple!
cursor.execute('SELECT * FROM users WHERE email=%s', (email, ))
user_data = cursor.fetchone()
return cls(email=user_data[0],
first_name=user_data[1],
last_name=user_data[2])
def create_new_user(name):
with CursorFromConnectionPool() as cursor:
height = input('How tall are you? (in centimeters) ')
cursor.execute('INSERT INTO users (name, height) VALUES (%s, %s)',
(name.lower(), height))
cursor.execute(
'CREATE TABLE {} (id serial, date date, weight numeric)'.format(
name.lower()))
def check_user_exists(name):
with CursorFromConnectionPool() as cursor:
cursor.execute(
'SELECT EXISTS(SELECT * FROM information_schema.tables WHERE table_name=%s)',
(name.lower(), ))
user_exists = cursor.fetchone()[0]
if not user_exists:
create_new_user(name)
def save_to_db(self):
# This is creating a new connection pool every time! Very expensive...
with CursorFromConnectionPool() as cursor:
cursor.execute(
'INSERT INTO sushi_train_packinglist (dispatch_date, product_type, customer, sf_code, product_name, qty, unit, arrival_date) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)',
(self.dispatch_date, self.product_type, self.customer,
self.sf_code, self.product_name, self.qty, self.unit,
self.arrival_date))
def save_to_db(self):
with CursorFromConnectionPool() as cursor:
# cursor.execute("INSERT INTO public.users (email, first_name, last_name, oauth_token, oauth_token_secret) "
# "VALUES (%s, %s, %s, %s, %s)",
# (self.email, self.first_name, self.last_name, self.oauth_token, self.oauth_token_secret))
cursor.execute(
"INSERT INTO public.users (screen_name, oauth_token, oauth_token_secret) "
"VALUES (%s, %s, %s)",
(self.screen_name, self.oauth_token, self.oauth_token_secret))
def save_to_db(self):
#with psycopg2.connect(user='******', password='******', database='learning', host='localhost') as connection:
# request connection from the pool
#with connection_pool.getconn() as connection: == connection = connection_pool.getconn() and nothing more
#connection = connection_pool.getconn()
with CursorFromConnectionPool() as cursor:
cursor.execute(
'INSERT INTO users (screen_name, oauth_token, oauth_token_secret) VALUES (%s, %s, %s)',
(self.screen_name, self.oauth_token, self.oauth_token_secret))
def save_to_db(self):
#with psycopg2.connect(user='******', password='******', database='learning', host='localhost') as connection:
# request connection from the pool
#with connection_pool.getconn() as connection: == connection = connection_pool.getconn() and nothing more
#connection = connection_pool.getconn()
with CursorFromConnectionPool() as cursor:
cursor.execute(
'INSERT INTO users (email, first_name, last_name) VALUES (%s, %s, %s)',
(self.email, self.first_name, self.last_name))
def setUp(self):
Database.initialise(database='test-weight-app',
user='******',
password='******',
host='localhost')
with CursorFromConnectionPool() as cursor:
cursor.execute(
'CREATE TABLE public.users (id serial PRIMARY KEY, name varchar (255), height integer)'
)
def save_to_db(self):
# This is creating a new connection pool every time! Very expensive...
with CursorFromConnectionPool() as cursor:
cursor.execute('INSERT INTO Voter VALUES (%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s, %s,%s, %s)',
(self.first_name, self.second_name, self.third_name,
self.family_name, self.date_birth, self.place_birth,
self.province, self.district, self.city,
self.quarter, self.status, self.type,
self.national_id_no, self.family_book_no))
def check_users_table_exists():
with CursorFromConnectionPool() as cursor:
cursor.execute(
'SELECT EXISTS(SELECT * FROM information_schema.tables WHERE table_name=%s)',
('users', ))
table_exists = cursor.fetchone()[0]
if not table_exists:
cursor.execute(
'CREATE TABLE public.users (id serial PRIMARY KEY, name varchar (255), height integer)'
)
def load_from_db_by_name(cls, first_name):
with CursorFromConnectionPool() as cursor:
# Note the (first_name,) to make it a tuple!
cursor.execute('SELECT * FROM voter where id=%s', (1, ))
voter_data = cursor.fetchone()
print(voter_data)
return cls(first_name=voter_data[1],
third_name=voter_data[2],
family_name=voter_data[3],
id=voter_data[0])
def load_to_db_by_screen_name(cls, screen_name): # cls is the currently bound class a.k.a 'User' in this case.
with CursorFromConnectionPool() as cursor:
cursor.execute('SELECT * FROM users WHERE screen_name=%s',
(screen_name,)) # put comma at the end of inside bracked to tell python it is a tuple
user_data = cursor.fetchone() # fetchone fetches the first row of the cursor. fetchall fetches all rows
if user_data:
return cls(screen_name=user_data[1],
oauth_token=user_data[2],
oauth_token_secret=user_data[3],
id=user_data[0])
def load_from_db(cls, center_number):
with CursorFromConnectionPool() as cursor:
cursor.execute('SELECT * FROM results WHERE "center_number"=%s',
(center_number, ))
user_data = cursor.fetchone()
print(user_data)
return cls(center_number=user_data[1],
center_name=user_data[2],
walid_votes=user_data[3],
id=user_data[0])
def calculate_bmi(name):
with CursorFromConnectionPool() as cursor:
cursor.execute('SELECT weight FROM {} ORDER BY date DESC'.format(
name.lower()))
weight = cursor.fetchone()[0]
cursor.execute('SELECT height FROM users WHERE name=%s',
(name.lower(), ))
height = cursor.fetchone()[0]
height = height / 100
bmi = float(weight) / (height**2)
return '%.2f' % bmi
def test_save_measurements_first_time_that_day(self, input):
name = 'Test'
weight = 88.5
user_functions.create_new_user(name)
user_functions.save_measurements(name, weight)
with CursorFromConnectionPool() as cursor:
cursor.execute(
'SELECT EXISTS(SELECT * FROM {} WHERE date=%s)'.format(name),
(date.today(), ))
exists = cursor.fetchone()[0]
self.assertTrue(exists)
def load_from_db_by_screen_name(cls, screen_name):
#with psycopg2.connect(user='******', password='******', database='learning', host='localhost') as connection:
#with connection_pool.getconn() as connection:
#connection = connection_pool.getconn()
with CursorFromConnectionPool() as cursor:
cursor.execute('SELECT * FROM users WHERE screen_name=%s',
(screen_name, ))
user_data = cursor.fetchone()
if user_data:
return cls(screen_name=user_data[1],
oauth_token=user_data[2],
oauth_token_secret=user_data[3],
id=user_data[0])
def test_calculate_difference(self, input):
name = 'Test'
weight = 88.5
user_functions.save_measurements(name, weight)
old_date = date.today() - timedelta(days=7)
with CursorFromConnectionPool() as cursor:
cursor.execute(
'INSERT INTO {} (date, weight) VALUES (%s, %s)'.format(name),
(old_date, 86.5))
output = user_functions.calculate_difference(name)
self.assertEqual(output, "You have gained 2.0 kg since last time. ")
def test_create_new_user(self, input):
name = 'test_user'
user_functions.create_new_user(name)
with CursorFromConnectionPool() as cursor:
cursor.execute(
'SELECT EXISTS(SELECT * FROM information_schema.tables WHERE table_name=%s)',
(name.lower(), ))
user_table_exists = cursor.fetchone()[0]
cursor.execute('SELECT EXISTS(SELECT * FROM users WHERE name=%s)',
(name.lower(), ))
user_is_in_users_table = cursor.fetchone()[0]
self.assertTrue(user_table_exists)
self.assertTrue(user_is_in_users_table)
def calculate_difference(name):
with CursorFromConnectionPool() as cursor:
cursor.execute('SELECT weight FROM {} ORDER BY date DESC'.format(name))
data = cursor.fetchall()
if len(data) > 1:
current_weight = data[0][0]
previous_weight = data[1][0]
else:
return "Welcome in weight-app! Your weight is saved. "
difference = current_weight - previous_weight
if difference > 0:
return f"You have gained {difference} kg since last time. "
elif difference == 0:
return "You weight the same as last time. "
else:
return f"You have lost {difference} kg since last time. "
def save_measurements(name, weight):
check_user_exists(name)
with CursorFromConnectionPool() as cursor:
cursor.execute(
'SELECT EXISTS(SELECT * FROM {} WHERE date=%s)'.format(name),
(date.today(), ))
exists = cursor.fetchone()[0]
if exists:
print(
"You have already saved your weight today. Come back tomorrow! :-) "
)
else:
cursor.execute(
'INSERT INTO {} (date, weight) VALUES (%s, %s)'.format(
name.lower()), (
date.today(),
weight,
))