def create_tables():
""" create tables in the PostgreSQL database"""
commands = ("GRANT ALL ON SCHEMA public TO postgres",
"GRANT ALL ON SCHEMA public TO public",
"""CREATE TABLE IF NOT EXISTS users (
id serial PRIMARY KEY,
username character varying(60) NOT NULL,
email character varying(60) NOT NULL,
password character varying(255) NOT NULL
)
""", """CREATE TABLE IF NOT EXISTS questions (
id serial PRIMARY KEY,
user_id INTEGER NOT NULL,
title TEXT NOT NULL,
description TEXT NOT NULL,
FOREIGN KEY (user_id)
REFERENCES users (id)
ON DELETE CASCADE ON UPDATE CASCADE
)
""", """CREATE TABLE IF NOT EXISTS answers (
id serial PRIMARY KEY,
question_id INTEGER NOT NULL,
answer TEXT NOT NULL,
upvote INT DEFAULT 0,
downvote INT DEFAULT 0,
solved INT DEFAULT 0,
author character varying(60) NOT NULL,
FOREIGN KEY (question_id)
REFERENCES questions (id)
ON DELETE CASCADE ON UPDATE CASCADE)
""")
with connect() as connection:
with connection.cursor() as cursor:
for command in commands:
cursor.execute(command)
def find_by_user_id(self, userID):
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute(
"""SELECT questions.id, users.username,
questions.title, questions.description
FROM questions
INNER JOIN users ON users.id = questions.user_id
WHERE user_id=%s""", (userID, ))
questions = cursor.fetchall()
#If questions is not None, loop through the questions
#append them in the quest list as dictionaries
if questions:
#Create an empty list for storing the questions
quest = []
for q in questions:
quest.append({
"id": q[0],
"author": q[1],
"title": q[2],
"description": q[3]
})
return quest
else:
return None
def add_answer(self, questionID, identity):
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute(
"""INSERT INTO answers
(question_id, author, answer) VALUES(%s, %s, %s)""",
(questionID, identity, self.answer))
return True
def upvote(self, questionID):
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute(
"""UPDATE answers
SET upvote = upvote + %s WHERE id=%s AND question_id=%s""",
(1, self.id, questionID))
return True
def delete(self, questionID):
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute(
"""DELETE FROM answers
WHERE id = %s AND question_id = %s""",
(self.id, questionID))
return True
def populate_stop_times(self):
services = self.load_csv('calendar.txt')
routes = self.load_csv('routes.txt')
trips = self.load_csv('trips.txt')
times = self.load_csv('stop_times.txt')
# Turn list of services into a dictionary where each key is a service id.
services = dict((x['service_id'], x) for x in services)
# Turn list of routes into a dictionary where each key is a route id.
routes = dict((x['route_id'], x) for x in routes)
trips = self._filter_trips(trips)
# Two empty lists that will be populated with data.
stop_times = []
conn = db.connect()
# Not sure why but you have to recopy the entire DictReader list into a
# native Python list otherwise it won't work for the for loops.
times = [i for i in times]
for trip in trips:
# Retrieve all stop times for this trip.
stops = [i for i in times if i['trip_id'] == trip['trip_id']]
for stop in stops:
if stop['stop_id'] not in self.hamilton_stops:
continue
route = routes[trip['route_id']]
service = services[trip['service_id']]
if route['route_type'] == '2':
continue
if trip['trip_headsign'].split('-')[0].strip() == '8G':
print trip['trip_headsign']
stop_time = {
'stop_id': 'go-' + stop['stop_id'],
'endpoint': trip['trip_headsign'], #.split('-')[1].strip(),
'route_name': route['route_long_name'],
'route_number': trip['trip_headsign'].split('-')[0].strip(),
'stop_time': stop['departure_time'],
'monday': True if service['monday'] == '1' else False,
'tuesday': True if service['tuesday'] == '1' else False,
'wednesday': True if service['wednesday'] == '1' else False,
'thursday': True if service['thursday'] == '1' else False,
'friday': True if service['friday'] == '1' else False,
'saturday': True if service['saturday'] == '1' else False,
'sunday': True if service['sunday'] == '1' else False,
}
try:
conn.execute(schema.stop_times.insert().values(stop_time))
except IntegrityError as e:
print "Integrity Error for stop time: %s" % (stop_time)
def save(self, user_id):
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute(
"""INSERT INTO questions
(user_id, title, description)
VALUES(%s, %s, %s)""",
(user_id, self.title, self.description))
return {"message": "Question created successfully."}
def update(self):
#update the object and return True
#else, return False
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute(
"""UPDATE questions
SET title=%s, description=%s WHERE id=%s""",
(self.title, self.description, self.id))
return True
def find_by_id(cls, questionID):
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute("SELECT * FROM questions WHERE id = %s",
(questionID, ))
question = cursor.fetchone()
if question:
return cls(question[2], question[3], questionID)
else:
return None
def get_by_id(cls, user_id):
#Save the connection as connection
#Get the cursor from the connection variable
#No need to commit, Once the program exits the with block
#It will automatically commit and close the connection
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
user = cursor.fetchone()
return cls(user[1], user[2], user[3], user[0])
def find_by_description(cls, description):
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute(
"""SELECT * FROM questions
WHERE description = %s""", (description, ))
question = cursor.fetchone()
if question:
return True
else:
return None
def find_by_answer(cls, questionID, answer):
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute(
"""SELECT * FROM answers
WHERE answer = %s AND question_id = %s""",
(answer, questionID))
answer = cursor.fetchone()
if answer:
return True
else:
return None
def find_by_id(cls, questionID, answerID):
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute(
"""SELECT * FROM answers
WHERE id = %s AND question_id = %s""",
(answerID, questionID))
answer = cursor.fetchone()
if answer:
return cls(answer[3], answer[0])
else:
return None
def get_by_email(cls, email):
#Save the connection as connection
#Get the cursor fro0m the connection variable
#No need to commit, Once the program exits the with block
#It will automatically commit and close the connection
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute("SELECT * FROM users WHERE email = %s", (email,))
user = cursor.fetchone()
if user:
return cls(user[1], user[2], user[3], user[0])
else:
return None
def populate_stops(self):
"""
Loads all the stops from the data file lets the subclass process
them and then loads them into the database.
"""
stops = self.load_csv("stops.txt")
stops = self.process_stops(stops)
connection = db.connect()
for stop in stops:
try:
connection.execute(schema.stops.insert(), stop)
except DataError:
print "Missing data for stop: %s" % (stop)
async def search(request):
cnpj = request.form.get("cnpj")
if not cnpj:
raise NotFound("CNPJ não enviado na requisição POST.")
cleaned = sub(r"\D", "", cnpj)
if not cleaned or len(cleaned) != 14:
raise NotFound(f"CNPJ {cnpj} inválido.")
async with connect() as connection:
company = await get_company(connection, cleaned)
if not company:
raise NotFound(f"CNPJ {cnpj} não encontrado.")
return json_response(company)
def find_descriptive_single_question(cls, questionID):
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute(
"""SELECT questions.id, questions.title,
questions.description, users.username FROM questions
INNER JOIN users ON users.id = questions.user_id
WHERE questions.id = %s""", (questionID, ))
question = cursor.fetchone()
if question:
data = {
"id": question[0],
"title": question[1],
"description": question[2],
"author": question[3]
}
return data
def find_descriptive_single_answer(cls, answerID):
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute(
"""SELECT answers.id, questions.id,
answers.answer, answers.upvote, answers.downvote, answers.solved, answers.author FROM answers
INNER JOIN questions ON questions.id = answers.question_id
WHERE answers.id = %s""", (answerID, ))
question = cursor.fetchone()
if question:
data = {
"id": question[0],
"question_id": question[1],
"answer": question[2],
"upvotes": question[3],
"downvotes": question[4],
"solved": question[5],
"author": question[6]
}
return data
def get_answers(cls, questionID):
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute(
"""SELECT * FROM answers
WHERE question_id = %s""", (questionID, ))
answers = cursor.fetchall()
#Create an empty list to store the answres
answ = []
#Check if answer is not None
if answers:
for answer in answers:
answ.append({
"id": answer[0],
"question_id": answer[1],
"answer": answer[2],
"upvotes": answer[3],
"downvotes": answer[4],
"solved": answer[5],
"author": answer[6]
})
return answ
return None
def teardown():
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute("TRUNCATE TABLE users RESTART IDENTITY CASCADE")
async def db(create_test_db):
settings.POSTGRES_DB = POSTGRES_TEST_DB
async with connect(settings) as connection:
yield connection
settings.POSTGRES_DB = ORIGINAL_POSTGRES_DB
def save(self):
#Save the object to the database
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute("INSERT INTO users (username, email, password) VALUES(%s, %s, %s)", (self.username, self.email, self.password))
def delete(self):
with connect() as connection:
with connection.cursor() as cursor:
cursor.execute("DELETE FROM questions WHERE id=%s",
(self.id, ))
return True
import peewee
from datetime import datetime
from api import db
class Task(peewee.Model):
id = peewee.IntegerField()
title = peewee.CharField()
datetime = peewee.DateTimeField()
class Meta:
database: db
if __name__ == '__main__':
db.connect()
db.create_table(Task)
for i in range(1, 11):
Task.create(id=i,
title='Task {}'.format(i),
datetime=datetime.now().strftime('%d-%m-%Y %H:%M:%S'))
db.close()
