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
