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