Python & MySQL Crash Course

Python & MySQL Crash Course

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()

view raw
python_mysql.py
hosted with ❤ by GitHub

ūüíĖ Become a Patron: Show support & get perks!
http://www.patreon.com/traversymedia

Website & Udemy Course Links:
https://www.traversymedia.com

Follow Traversy Media:

https://www.instagram.com/traversymedia
https://www.facebook.com/traversymedia

Powered by WPeMatico

About Guaripete Solutions

Guaripete Solutions SEO Agency in Charlotte North Carolina, with personalized SEO Consulting Services, Small and Medium Business Social Media Marketing Services, and Website Builder Experts oriented to our Customers.