Skip to content

Instantly share code, notes, and snippets.

@MaxCodeXTC

MaxCodeXTC/python_mysql.py

Forked from bradtraversy/python_mysql.py
Created June 15, 2020 23:57
Show Gist options
  • Save MaxCodeXTC/83484997d5a38d49282b53e9640fb38f to your computer and use it in GitHub Desktop.
Save MaxCodeXTC/83484997d5a38d49282b53e9640fb38f to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @bradtraversy bradtraversy revised this gist Jun 15, 2020. No changes.
  2. @bradtraversy bradtraversy created this gist Jun 15, 2020.
    102 changes: 102 additions & 0 deletions python_mysql.py
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,102 @@
    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()