This is a beginner-based crash course on working with MySQL with the Python programming language.
Code Gist:
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() |
💖 Become a Patron: Show support & get perks!
http://www.patreon.com/traversymedia
Website & Udemy Course Links:
https://www.traversymedia.com
Follow Traversy Media:
Tweets by traversymedia
https://www.instagram.com/traversymedia
https://www.facebook.com/traversymedia
Powered by WPeMatico