Skip to content

Instantly share code, notes, and snippets.

@amos-kibet
Forked from bradtraversy/python_mysql.py
Created July 25, 2022 08:44
Show Gist options
  • Save amos-kibet/788b566e16b4199bac4a3c4fa6f40150 to your computer and use it in GitHub Desktop.
Save amos-kibet/788b566e16b4199bac4a3c4fa6f40150 to your computer and use it in GitHub Desktop.
Python & MySQL crash course for beginners
import mysql.connector
from mysql.connector import errorcode
config = {
'user': 'root',
'password': '',
'host': 'localhost',
'database': 'acme'
}
db = mysql.connector.connect(**config)
cursor = db.cursor()
DB_NAME = 'acme'
TABLES = {}
TABLES['logs'] = (
"CREATE TABLE `logs` ("
" `id` int(11) NOT NULL AUTO_INCREMENT,"
" `text` varchar(250) NOT NULL,"
" `user` varchar(250) NOT NULL,"
" `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,"
" PRIMARY KEY (`id`)"
") ENGINE=InnoDB"
)
def create_database():
cursor.execute(
"CREATE DATABASE IF NOT EXISTS {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME))
print("Database {} created!".format(DB_NAME))
def create_tables():
cursor.execute("USE {}".format(DB_NAME))
for table_name in TABLES:
table_description = TABLES[table_name]
try:
print("Creating table ({}) ".format(table_name), end="")
cursor.execute(table_description)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
print("Already Exists")
else:
print(err.msg)
create_database()
create_tables()
def add_log(text, user):
sql = ("INSERT INTO logs(text, user) VALUES (%s, %s)")
cursor.execute(sql, (text, user,))
db.commit()
log_id = cursor.lastrowid
print("Added log {}".format(log_id))
def get_logs():
sql = ("SELECT * FROM logs ORDER BY created DESC")
cursor.execute(sql)
result = cursor.fetchall()
for row in result:
print(row[1])
def get_log(id):
sql = ("SELECT * FROM logs WHERE id = %s")
cursor.execute(sql, (id,))
result = cursor.fetchone()
for row in result:
print(row)
def update_log(id, text):
sql = ("UPDATE logs SET text = %s WHERE id = %s")
cursor.execute(sql, (text, id))
db.commit()
print("Log updated")
def delete_log(id):
sql = ("DELETE FROM logs WHERE id = %s")
cursor.execute(sql, (id,))
db.commit()
print("Log removed")
# add_log('This is log one', 'Brad')
# add_log('This is log two', 'Jeff')
# add_log('This is log three', 'Jane')
# get_logs()
# get_log(2)
# update_log(2, 'Updated log')
# delete_log(2)
# get_logs()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment