def insert(cls, item):
connection = DBConnection.db_connection()
with connection.cursor() as cursor:
sql = "INSERT INTO `items`(`name`, `price`) VALUES (%s, %s)"
cursor.execute(sql, (item['name'], item['price']))
connection.commit()
logger.info("item inserted! - {}".format(item))
connection.close()
logger.info("Connection closed!")
def update(cls, item):
connection = DBConnection.db_connection()
with connection.cursor() as cursor:
sql = "UPDATE `items` SET `name` = %s `price` = %s WHERE `name` = %s"
cursor.execute(
sql, (item['name'], item['price'], item['name']))
connection.commit()
logger.info("item updated! - {}".format(item))
connection.close()
logger.info("Connection closed!")
def delete(self, name):
connection = DBConnection.db_connection()
try:
with connection.cursor() as cursor:
sql = "DELETE FROM `items` WHERE `name` = %s"
cursor.execute(sql, (name))
connection.commit()
logger.info("Data deleted! -Name of the item: {}".format(name))
connection.close()
logger.info("Connection closed!")
return {"message": "Item removed"}, 204
except pymysql.MySQLError as e:
logger.exception(e)
return {"message": "Internal server error.", "error": e}, 500
def find_by_name(cls, name):
result = None
connection = DBConnection.db_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM `items` WHERE `name` = %s"
cursor.execute(sql, (name))
result = cursor.fetchall()
logger.info("Fetch item by name: {}".format(result))
connection.close()
logger.info("Connection closed!")
if result:
return result
return None
except pymysql.MySQLError as e:
logger.exception(e)
def get(self):
result = None
connection = DBConnection.db_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM `items`"
cursor.execute(sql)
result = cursor.fetchall()
logger.info("Fetch items are : {}".format(result))
connection.close()
logger.info("Connection closed!")
items = []
if result:
for item in result:
items.append({'name': item[0], 'price': item[1]})
return {"Item": items}, 200
return {"message": "Items not found!"}, 404
except pymysql.MySQLError as e:
logger.exception(e)
return {"message": "Internal server error.", "error": e}, 500
def find_by_id(cls, _id):
logger.debug("'find_by_id' function called!")
connection = DBConnection.db_connection()
try:
with connection.cursor() as cursor:
sql = "SELECT * FROM users WHERE id = %s"
cursor.execute(sql, (_id))
row = cursor.fetchone()
logger.info(row)
if row:
user = cls(*row)
else:
user = None
connection.close()
logger.info("Conncetion closed!")
logger.info("Return value from find_by_id: {}".format(user))
return user
except pymysql.MySQLError as e:
logger.exception(e)
def post(cls):
logger.info("POST method called from UserRegister Resource")
data = cls.parser.parse_args()
""" Check the user is exixts or not."""
if UserModel.find_by_username(data['username']):
return {"message": "Username already exists!"}, 400
""" Connecting to the db server"""
connection = DBConnection.db_connection()
"""Inserting the user data into user table"""
try:
with connection.cursor() as cursor:
sql = "INSERT INTO `users` ( `username`, `password`) VALUES (%s, %s)"
cursor.execute(sql, (data['username'], data['password']))
connection.commit()
logger.info("Inserted data: ", data)
connection.close()
logger.info("Connection closed!")
return {"message": "User created successfully."}, 201
except Exception as e:
logger.exception(e)
return {"message": "Internal server error.", "error": e}, 500
import pymysql
from config.database_connection import DBConnection
connection = DBConnection.db_connection()
def create_table():
try:
with connection.cursor() as cursor:
# create_table_query = "CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(64) NOT NULL, password VARCHAR(255) NOT NULL)"
# create_table_query = "CREATE TABLE items (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(64) NOT NULL, price INT(25) NOT NULL)"
create_table_query = "CREATE TABLE user (id INT AUTO_INCREMENT PRIMARY KEY, public_id VARCHAR(255) NOT NULL, name VARCHAR(64) NOT NULL, password VARCHAR(255) NOT NULL, admin BOOLEAN NOT NULL)"
# create_table_query = "CREATE TABLE todo (id INT AUTO_INCREMENT PRIMARY KEY, text VARCHAR(255) NOT NULL, complete BOOLEAN NOT NULL, user_id INT NOT NULL)"
cursor.execute(create_table_query)
connection.commit()
print('Table created')
except pymysql.MySQLError as e:
print("Exception: ", e)
finally:
connection.close()
print("Conncetion closed!")
def insert_data():
try:
with connection.cursor() as cursor:
# Create a new record
sql = "INSERT INTO `users` ( `username`, `password`) VALUES (%s, %s)"
cursor.execute(sql, ('kd-dey', 'kdey123'))
connection.commit()
print("Inserted!")
except Exception as e: