def get_all_art(username: str) -> List:
images = []
if username is None:
username = ""
cursor.execute("SELECT id, title, creator, image from arts")
for art in cursor.fetchall():
cursor.execute("SELECT COUNT(*) from comments where artID = ?",
(art[0], ))
num_comments = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM likes where artID = ?",
(art[0], ))
likes = int(cursor.fetchone()[0])
images.append({
"image": art[3],
"likes": likes,
"creator": art[2],
"title": art[1],
"art_id": str(art[0]),
"num_comments": num_comments,
"hasLiked": did_user_like(username, art[0])
})
connection.commit()
return images
def get_image(id: str, username: str) -> Dict:
if username is None:
username = ""
cursor.execute("SELECT title, creator, image FROM arts WHERE id = ?",
(id, ))
data = cursor.fetchone()
if data is None:
return None
cursor.execute("SELECT COUNT(*) FROM likes where artID = ?", (int(id), ))
likes = int(cursor.fetchone()[0])
image = {
"title": data[0],
"creator": data[1],
"image": data[2],
"likes": likes,
"comments": [],
"hasLiked": did_user_like(username, id)
}
cursor.execute("SELECT username, content from comments WHERE artID = ?",
(id, ))
for comment in cursor.fetchall():
image["comments"].append({
"username": comment[0],
"content": comment[1]
})
connection.commit()
return image
def did_user_like(username: str, art_id: str):
if username is None:
username = ""
cursor.execute("SELECT * FROM likes WHERE username = ? AND artID = ?",
(username, int(art_id)))
connection.commit()
return cursor.fetchone() is not None
def store_image(title: str, image: str, username: str) -> str:
cursor.execute(
'''
INSERT INTO arts (
title,
creator,
image
)
VALUES (?, ?, ?)''', (title, username, image))
connection.commit()
return str(cursor.lastrowid)
def insert_movies():
with open('./ml-latest-small/movies.csv', newline='', encoding="utf8") as csvfile:
spamreader = csv.reader(csvfile)
for row in spamreader:
# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO movies(id,title, geners) VALUES ('%s', '%s', '%s' );" % (row[0], row[1], row[2])
print(sql)
try:
# Execute the SQL command
mycursor.execute(sql)
# Commit your changes in the database
connection.commit()
except:
# Rollback in case there is any error
connection.rollback()
def insert_movies():
with open('./ml-latest-small/movies.csv', newline='',
encoding="utf8") as csvfile:
spamreader = csv.reader(csvfile)
for row in spamreader:
# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO movies(id,title, geners) VALUES ('%s', '%s', '%s' );" % (
row[0], row[1], row[2])
print(sql)
try:
# Execute the SQL command
mycursor.execute(sql)
# Commit your changes in the database
connection.commit()
except:
# Rollback in case there is any error
connection.rollback()
def like_artwork(art_id: str, username: str):
cursor.execute("INSERT INTO likes VALUES (?, ?)", (username, art_id))
connection.commit()
def add_comment(art_id: str, content: str, username: str):
cursor.execute(
"INSERT INTO comments (username, content, artID) VALUES (?, ?, ?)",
(username, content, art_id))
connection.commit()
def is_valid_login(username: str, password: str) -> bool:
cursor.execute(
"SELECT username FROM users WHERE username = ? AND password = ?",
(username, password))
connection.commit()
return cursor.fetchone() is not None
def create_account(username: str, password: str):
cursor.execute("INSERT INTO users VALUES (?, ?)", (username, password))
connection.commit()
def does_username_exist(username: str) -> bool:
cursor.execute("SELECT username FROM users where username = ?",
(username, ))
connection.commit()
return cursor.fetchone() is not None
def unlike_artwork(art_id: str, username: str):
cursor.execute("DELETE FROM likes WHERE artID = ? AND username = ?",
(art_id, username))
connection.commit()
# INTO TABLE discounts
# FIELDS TERMINATED BY ','
# ENCLOSED BY '"'
# LINES TERMINATED BY '\n'
# IGNORE 1 ROWS;
# def insert_movies():
# header = ('title', 'geners')
# with open('./ml-latest-small/movies.csv', newline='', encoding="utf8") as csvfile:
# spamreader = csv.reader(csvfile)
# for row in spamreader:
# movie = dict(zip(header, row[1:]))
# print(spamreader)
# # Movie.objects.create(**movie)
with open('./ml-latest-small/movies.csv', newline='',
encoding="utf8") as csvfile:
spamreader = csv.reader(csvfile, delimiter=' ', quotechar='|')
for row in spamreader:
# sql = "INSERT INTO testSmall VALUES (%s);" % ', '.join('?' for _ in row)
# mycursor.execute (sql, row)
# sql = "INSERT INTO `movies` (`title`, `geners`) VALUES ( ?, ?);"
mycursor.execute(
"INSERT INTO `movies` (`id`, `title`, `geners`) VALUES ( %s, %s, %s);",
row)
#close the connection to the database.
connection.commit()
mycursor.close()
# mycursor.execute (sql, (row[1], row[2]))
# print(', '.join(row))
