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, ))