python mysql connector

gistfile1.txt

#!/usr/local/bin/env python3

import mysql.connector
# pip3 install mysql-connector-python

user_input = input("Enter something: ")

db = mysql.connector.connect(
    host="localhost",
    port="3306",
    user="root",
    passwd="password"
    )

dbname = "mydb"
table = "mytable"
column = "mycolumn"

cursor = db.cursor()

cursor.execute(f"CREATE DATABASE IF NOT EXISTS {dbname}")

cursor.execute(f"USE {dbname};")

cursor.execute(f"CREATE TABLE IF NOT EXISTS {table} ({column} VARCHAR(255));")

query = f"INSERT INTO {table} ({column}) VALUES (%s)"

cursor.execute(query, (user_input,))

db.commit()

db.close()

print("Successfully inserted input into database.")

mysql-connector.py

#!/usr/local/bin/env python3

import mysql.connector
# pip3 install mysql-connector-python

# Database connection
db = mysql.connector.connect(
    host="localhost",
    port="3306",
    user="root",
    passwd="password"
    )

# Set some database vars
dbname = "appdb"
table = "usertable"

# Create a cursor to execute queries.
mycursor = db.cursor()

# Create database if it's not already created
mycursor.execute(f"CREATE DATABASE IF NOT EXISTS {dbname}")

# Connect to the database.
mycursor.execute(f"USE {dbname};")

# Execute a query to create a new table.
mycursor.execute(f"CREATE TABLE IF NOT EXISTS {table} (FirstName VARCHAR(255), LastName VARCHAR(255), PhoneNumber VARCHAR(255), AddressLine1 VARCHAR(255), AddressLine2 VARCHAR(255), City VARCHAR(255), State VARCHAR(255), ZipCode VARCHAR(255));")
#mycursor.execute(f"CREATE TABLE IF NOT EXISTS {table} (id INT PRIMARY KEY AUTO_INCREMENT UNIQUE, FirstName VARCHAR(255), LastName VARCHAR(255), PhoneNumber VARCHAR(255), AddressLine1 VARCHAR(255), AddressLine2 VARCHAR(255), City VARCHAR(255), State VARCHAR(255), ZipCode VARCHAR(255));")

# Don't allow duplicate records if the phone number is the same
mycursor.execute(f"ALTER TABLE {table} ADD UNIQUE INDEX(phonenumber);")
#mycursor.execute(f"ALTER TABLE {table} ADD UNIQUE INDEX(id, phonenumber);")

# Execute a query to insert a new row into the table.
mycursor.execute(f"INSERT IGNORE INTO {table} (FirstName, LastName, PhoneNumber, AddressLine1, AddressLine2, City, State, ZipCode) VALUES ('Bruno', 'Burke', '617-555-1357', '1234 Pine Street', '', 'Anytown', 'MA', '01888');")
mycursor.execute(f"INSERT IGNORE INTO {table} (FirstName, LastName, PhoneNumber, AddressLine1, AddressLine2, City, State, ZipCode) VALUES ('Stephen', 'Burke', '495-555-6174', '2468 Easy Street', '', 'Mill Valley', 'CA', '94941');")

# Duplicate phone number test
mycursor.execute(f"INSERT IGNORE INTO {table} (FirstName, LastName, PhoneNumber, AddressLine1, AddressLine2, City, State, ZipCode) VALUES ('Bruno', 'Burke', '617-555-1357', '1234 Pine Street', '', 'Anytown', 'MA', '01888');")

# Execute a query to select all rows from the table.
mycursor.execute(f"SELECT * FROM {table} WHERE lastname='Burke';")

# Fetch the results of the query.
results = mycursor.fetchall()

# Print the results.
for row in results:
    print(row)

# Commit to database    
db.commit()

# Close the cursor and the connection.
mycursor.close()
db.close()

sql-docker-stuff.sh

docker network create burke

docker run -d --name phpmyadmin --network burke -e PMA_HOST=mysqlvm  -p 8080:80 phpmyadmin

docker run -d --name mysqlvm --network burke -e MYSQL_ROOT_PASSWORD="password" -v mysql:/var/lib/mysql -p 3306:3306 mysql