def new(name): cursor = connection.get_cursor() query = """ INSERT INTO tag(name) VALUES ('{name}') """.format( name=name ) cursor.execute(query) connection.close_connection(cursor)
def delete(id: int): cursor = connection.get_cursor() query = """ DELETE FROM question WHERE id={id} """.format( id=id ) cursor.execute(query) connection.close_connection(cursor)
def remove(question_id: int, tag_id: int): cursor = connection.get_cursor() query = """ DELETE FROM question_tag WHERE question_id={question} AND tag_id={tag} """.format( question=question_id, tag=tag_id ) cursor.execute(query) connection.close_connection(cursor)
def update(id: int, message=None, edited_count: int = None): cursor = connection.get_cursor() query = """ UPDATE comment SET {message}{edited} WHERE id={id} """.format( id=id, message=f"message='{message}'{',' if edited_count != None else ''}" if message else '', edited=f"edited_count={edited_count}" if edited_count is not None else '' ) cursor.execute(query) connection.close_connection(cursor)
def get(question_id: int = None): cursor = connection.get_cursor() query = """ SELECT {elements} FROM tag {condition} """.format( elements=f"{'id, name' if question_id != None else '*'}", condition=f"WHERE id IN (SELECT tag_id FROM question_tag WHERE question_id={question_id})" if question_id is not None else '' ) cursor.execute(query) data = cursor.fetchall() connection.close_connection(cursor) return data
def update(id: int, vote=None, message=None, image=None): cursor = connection.get_cursor() query = """ UPDATE answer SET {vote}{message}{image} WHERE id={id} """.format( id=id, vote=f"vote_number={vote}{',' if message or image else ''}" if vote is not None else '', message=f"message='{message}'{',' if image else ''}" if message else '', image=f"image='{image}'" if image else '' ) cursor.execute(query) connection.close_connection(cursor)
def update(id: int, view=None, vote=None, title=None, message=None, image=None): cursor = connection.get_cursor() query = """ UPDATE question SET {view}{vote}{title}{message}{image} WHERE id={id} """.format( id=id, view=f"view_number={view}{',' if vote != None or title or message or image else ''}" if view is not None else '', vote=f"vote_number={vote}{',' if title or message or image else ''}" if vote is not None else '', title=f"title='{title}'{',' if message or image else ''}" if title else '', message=f"message='{message}'{',' if image else ''}" if message else '', image=f"image='{image}'" if image else '' ) cursor.execute(query) connection.close_connection(cursor)
def add(title, message, image): cursor = connection.get_cursor() cursor.execute("SELECT nextval('question_id_seq')") id = cursor.fetchall()[0]['nextval'] query = """ INSERT INTO question(id, submission_time, view_number, vote_number, title, message, image) VALUES ({id}, '{time}', 0, 0, '{title}', '{message}', '{image}') """.format( id=id, time=datetime.now().strftime("%Y-%m-%d %H:%M:%S"), title=title, message=message, image=f"question_{id}.{image}" if image else '' ) cursor.execute(query) connection.close_connection(cursor) return id
def get(key=None, value=None): if key in ('id', 'question_id', 'answer_id', 'edited_count'): condition = f"{key}={value}" else: condition = f"UPPER({key}) LIKE UPPER('%{value}%')" cursor = connection.get_cursor() query = """ SELECT * FROM comment {condition} """.format( condition=f"WHERE {condition}" if key else "" ) cursor.execute(query) data = cursor.fetchall() connection.close_connection(cursor) return data[0] if key == 'id' else data
def get(key=None, value=None): # if key is not None return the answers with key == value if key in ('id', 'vote_number', 'question_id'): condition = f"{key}={value}" else: condition = f"UPPER({key}) LIKE UPPER('%{value}%')" cursor = connection.get_cursor() query = """ SELECT * FROM answer {condition} """.format( condition=f"WHERE {condition}" if key else "" ) cursor.execute(query) data = cursor.fetchall() connection.close_connection(cursor) return data[0] if key == 'id' else data
def add(message, question_id: int = None, answer_id: int = None): cursor = connection.get_cursor() cursor.execute("SELECT nextval('comment_id_seq')") id = cursor.fetchall()[0]['nextval'] query = """ INSERT INTO comment(id{id_for}, message, submission_time, edited_count) VALUES ({id}, {question}{answer}, '{message}', '{time}', 0) """.format( id=id, id_for=f", {'question_id' if question_id != None else 'answer_id' if answer_id != None else ''}", question=question_id if question_id is not None else '', answer=answer_id if answer_id is not None else '', message=message, time=datetime.now().strftime("%Y-%m-%d %H:%M:%S") ) cursor.execute(query) connection.close_connection(cursor) return id
def add(question_id: int, tag_id: int): try: cursor = connection.get_cursor() query = """ INSERT INTO question_tag(question_id, tag_id) VALUES ({question}, {tag}) """.format( question=question_id, tag=tag_id ) cursor.execute(query) connection.close_connection(cursor) except IndexError: pass except KeyError: pass except ValueError: pass except IndentationError: pass
import connection import numpy as np from numpy import random cur = connection.get_cursor() #cur.execute("""ALTER TABLE PROGRAMMERS ADD (FULL_NAME nvarchar(50))""") #cur.execute("""ALTER TABLE PROGRAMMERS ALTER (FULL_NAME nvarchar(50))""") cur.execute("""SELECT ID FROM PROGRAMMERS WHERE NAME IS NOT NULL""") ids = [i[0] for i in cur.fetchall()] firstNameMale = open('firstNameMale.txt', 'r').readlines() firstNameFemale = open('firstNameFemale.txt', 'r').readlines() surname = open('surname.txt', 'r').readlines() firstName = firstNameMale + firstNameFemale names = [] for i in ids: name = firstName[random.random_integers(len(firstName)-1)].strip() + ' ' + surname[random.random_integers(len(surname) - 1)].strip() names.append((str(name), i)) cur.executemany("""UPDATE PROGRAMMERS SET full_name=? WHERE id=?""", names) cur.commit() query = """CREATE ROW TABLE "TOUCHED" ( "ID" INT CS_INT NOT NULL, "TRANSACTION_ID" INT CS_INT NOT NULL, "PROGRAMMER_ID" INT CS_INT NOT NULL, PRIMARY KEY ( "ID" ) ) """ cur.execute(query) cur.execute("""SELECT TRANSACTIONS.ID, PROGRAMMER_ID FROM TRANSACTIONS INNER JOIN PROGRAMMERS ON TRANSACTIONS.PROGRAMMER_ID = PROGRAMMERS.ID WHERE PROGRAMMERS.NAME IS NOT NULL""")