Esempio n. 1
0
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
Esempio n. 3
0
 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
Esempio n. 4
0
 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
Esempio n. 5
0
 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
Esempio n. 6
0
    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
Esempio n. 10
0
 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])
Esempio n. 11
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
Esempio n. 12
0
 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
Esempio n. 13
0
 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
Esempio n. 14
0
 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
Esempio n. 15
0
    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)
Esempio n. 16
0
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)
Esempio n. 17
0
 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
Esempio n. 18
0
 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
Esempio n. 19
0
 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
Esempio n. 20
0
def teardown():
    with connect() as connection:
        with connection.cursor() as cursor:
            cursor.execute("TRUNCATE TABLE users RESTART IDENTITY CASCADE")
Esempio n. 21
0
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
Esempio n. 22
0
 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))
Esempio n. 23
0
 def delete(self):
     with connect() as connection:
         with connection.cursor() as cursor:
             cursor.execute("DELETE FROM questions WHERE id=%s",
                            (self.id, ))
             return True
Esempio n. 24
0
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()