Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import mysql.connector
- from dotenv import load_dotenv
- import json
- from pymemcache.client import base
- import time
- load_dotenv()
- conn = None
- mc = base.Client(('localhost','11211'))
- def main():
- connect()
- init()
- scenario()
- def connect():
- """ Connect to MySQL database """
- global conn
- try:
- print('Connecting to MySQL database...')
- conn = mysql.connector.connect(host="34.30.23.118", user="root", password='|0zHafT),7"#<H&<', database='model_storage')
- if conn.is_connected():
- print('Connection is established.')
- else:
- print('Connection is failed.')
- except mysql.connector.Error as error:
- print(error)
- def init():
- global conn
- cur = conn.cursor()
- cur.execute("CREATE TABLE IF NOT EXISTS students(id INT PRIMARY KEY AUTO_INCREMENT, student_data JSON NOT NULL);")
- cur.execute("CREATE TABLE IF NOT EXISTS lecture(id INT PRIMARY KEY AUTO_INCREMENT, lecture_data JSON NOT NULL);")
- cur.close()
- def write(data):
- global conn
- cur = conn.cursor()
- cur.execute("INSERT INTO students VALUES (null, '%s')" % data)
- conn.commit()
- cur.close()
- def read_v1(all=False):
- global conn
- cur = conn.cursor()
- sql = "SELECT * FROM students LIMIT 100" if not all else "SELECT * FROM students"
- print(sql)
- cur.execute(sql)
- result = cur.fetchall()
- return result
- def read_v2():
- if not mc.get("read"):
- global conn
- cur = conn.cursor()
- cur.execute("SELECT * FROM students LIMIT 100")
- result = cur.fetchall()
- mc.set("read", json.dumps(result))
- return result
- return mc.get("read")
- def read_id_v1(id=None):
- global conn
- cur = conn.cursor()
- cur.execute("SELECT * FROM students WHERE `student_data`->>'$.nim' = '%s' LIMIT 1" % str(id))
- result = cur.fetchall()
- return result
- def read_id_v2(id=None):
- if not mc.get("read_%s" % str(id)):
- global conn
- cur = conn.cursor()
- cur.execute("SELECT * FROM students WHERE `student_data`->>'$.nim' = '%s' LIMIT 1" % str(id))
- result = cur.fetchall()
- mc.set("read_%s" % str(id), json.dumps(result))
- return result
- return mc.get("read_%s" % str(id))
- def delete(id=None):
- global conn
- cur = conn.cursor()
- if(id):
- cur.execute("DELETE FROM students WHERE id=%s" % str(id))
- else:
- cur.execute("DELETE FROM students")
- conn.commit()
- cur.close()
- def reset():
- global conn
- cur = conn.cursor()
- cur.execute("TRUNCATE TABLE students")
- cur.execute("TRUNCATE TABLE lecture")
- conn.commit()
- cur.close()
- # Scenario:
- # i tried basic read, read with caching, write, and delete
- def scenario():
- # reset()
- example_data = {
- "name": "andika",
- "nim": 2105551019,
- "lecture": [
- {"name": "alprog", "description": "belajar algoritma"},
- {"name": "tbd", "description": "belajar basis data"},
- {"name": "ml", "description": "belajar machine learning"}
- ]
- }
- start = time.time()
- for i in range(5000):
- write(json.dumps(example_data))
- end = time.time()
- print(f"Write 5000 row time taken: {end - start:.6f} seconds")
- results = read_v1(all=True)
- start = time.time()
- for item in results:
- read_id_v1(json.loads(item[1])['nim'])
- end = time.time()
- print(f"Read 5000 row time taken: {end - start:.6f} seconds")
- start = time.time()
- for item in results:
- read_id_v2(json.loads(item[1])['nim'])
- end = time.time()
- print(f"Read with caching 5000 row time taken: {end - start:.6f} seconds")
- start = time.time()
- for item in results:
- delete(item[0])
- end = time.time()
- print(f"Delete 5000 row time taken: {end - start:.6f} seconds")
- if __name__ == '__main__':
- main()
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement