'''
References
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.html
https://stackoverflow.com/questions/44933704/pandas-0-20-2-to-sql-using-mysql
https://pythontic.com/database/mysql/
Your AWS mysql instance data:
DB instance identifier: YOUR_DB_instance_identifier
username: YOUR_username
pass: YOUR_pass
host: YOUR_host_example.eu-west-1.rds.amazonaws.com
port: 3306 (typical)
Database Engine Name Default Port Number
Aurora/MySQL/MariaDB 3306
PostgreSQL 5432
Oracle 1521
SQL Server 1433
'''
# TO INSTALL requeriments
# !pip install PyMySQL
# !pip install sqlalchemy
# !pip install mysql-connector-python
'''
requirements.txt
PyMySQL
sqlalchemy
mysql-connector-python
'''
import pymysql
from sqlalchemy import create_engine
import mysql.connector
import pandas as pd
import sqlparse
# CREATE a database
# import the mysql client for python
import pymysql
# Create a connection object
# Host of the MySQL database server (or ip)
host = 'YOUR_host_example.eu-west-1.rds.amazonaws.com'
# User name of the database server
dbUserName = 'YOUR_username'
# Password for the database user
dbUserPassword = 'YOUR_pass'
# Name of the database that is to be Created
databaseForCreation = 'My_new_DataBase'
# Character set
charSet = "utf8mb4"
#cursor Type
cusrsorType = pymysql.cursors.DictCursor
#db Connection
conn = pymysql.connect(host=host,
user=dbUserName,
password=dbUserPassword,
charset=charSet,
cursorclass=cusrsorType)
try:
# Create a cursor object
dbCursor = conn.cursor()
# SQL Statement to delete a database
sql = "CREATE DATABASE "+ databaseForCreation
# Execute the create database SQL statment through the cursor instance
dbCursor.execute(sql)
# SQL query string
sqlQuery = "SHOW DATABASES"
# Execute the sqlQuery
dbCursor.execute(sqlQuery)
#Fetch all the rows
databaseCollection = dbCursor.fetchall()
for datatbase in databaseCollection:
print(datatbase)
except Exception as e:
print("Exeception occured:{}".format(e))
finally:
conn.close()
#LIST all databases
import pymysql
# Create a connection object
# Host of the MySQL database server (or ip)
host = 'YOUR_host_example.eu-west-1.rds.amazonaws.com'
# User name of the database server
dbUserName = 'YOUR_username'
# Password for the database user
dbUserPassword = 'YOUR_pass'
# Name of the database that is to be Created
databaseForCreation = 'My_new_DataBase'
# Character set
charSet = "utf8mb4"
#cursor Type
cusrsorType = pymysql.cursors.DictCursor
#db Connection
conn = pymysql.connect(host=host,
user=dbUserName,
password=dbUserPassword,
charset=charSet,
cursorclass=cusrsorType)
try:
# Create a cursor object
dbCursor = conn.cursor()
# SQL query string
sqlQuery = "SHOW DATABASES"
# Execute the sqlQuery
dbCursor.execute(sqlQuery)
#Fetch all the rows
databaseCollection = dbCursor.fetchall()
for datatbase in databaseCollection:
print(datatbase)
except Exception as e:
print("Exeception occured:{}".format(e))
finally:
conn.close()
# DELETE a database
# import the mysql client for python
import pymysql
# Create a connection object
# Host of the MySQL database server (or ip)
host = 'YOUR_host_example.eu-west-1.rds.amazonaws.com'
# User name of the database server
dbUserName = 'YOUR_username'
# Password for the database user
dbUserPassword = 'YOUR_pass'
# Name of the database that is to be Created
databaseForCreation = 'My_new_DataBase'
# Character set
charSet = "utf8mb4"
#cursor Type
cusrsorType = pymysql.cursors.DictCursor
#db Connection
conn = pymysql.connect(host=host,
user=dbUserName,
password=dbUserPassword,
charset=charSet,
cursorclass=cusrsorType)
try:
# Create a cursor object
dbCursor = conn.cursor()
# SQL Statement to delete a database
sql = "DROP DATABASE "+ databaseForDeletion
# Execute the create database SQL statment through the cursor instance
dbCursor.execute(sql)
# SQL query string
sqlQuery = "SHOW DATABASES"
# Execute the sqlQuery
dbCursor.execute(sqlQuery)
#Fetch all the rows
databaseCollection = dbCursor.fetchall()
for datatbase in databaseCollection:
print(datatbase)
except Exception as e:
print("Exeception occured:{}".format(e))
finally:
conn.close()
import pandas as pd
#Read and excel as template for new tabular table inside your database
df = pd.read_excel('My_table_template.xlsx')
from sqlalchemy import create_engine
import mysql.connector
# Create a connection object
# Host of the MySQL database server (or ip)
host = 'YOUR_host_example.eu-west-1.rds.amazonaws.com'
# User name of the database server
dbUserName = 'YOUR_username'
# Password for the database user
dbUserPassword = 'YOUR_pass'
# Name of the database
database = 'My_new_DataBase'
# Character set
charSet = "utf8mb4"
#cursor Type
cusrsorType = pymysql.cursors.DictCursor
#create_engine('mysql+mysqldb://root:password@localhost:3306/mydbname', echo = False)
engine = create_engine('mysql+mysqldb://' +
dbUserName + ':' +
dbUserPassword +
'@' + host + ':' + port +
'/' + database,
echo = False
)
# Pandas dataframe to SQL
df.to_sql(name = table, con = engine, if_exists = 'replace', index = False)
print('New Table: ' + table + ' ...created')
#Query all table values
import pymysql
# Create a connection object
# Host of the MySQL database server (or ip)
host = 'YOUR_host_example.eu-west-1.rds.amazonaws.com'
# User name of the database server
dbUserName = 'YOUR_username'
# Password for the database user
dbUserPassword = 'YOUR_pass'
# Name of the database
database = 'My_new_DataBase'
# Character set
charSet = "utf8mb4"
#cursor Type
cusrsorType = pymysql.cursors.DictCursor
#db Connection
conn = pymysql.connect(host=host,
user=dbUserName,
password=dbUserPassword,
db=database,
charset=charSet,
cursorclass=cusrsorType)
try:
# Create a cursor object
dbCursor = conn.cursor()
# SQL query string
sqlQuery = 'select * from ' + table
# Execute the sqlQuery
dbCursor.execute(sqlQuery)
#Fetch all the rows
rows = dbCursor.fetchall()
except Exception as e:
print("Exeception occured:{}".format(e))
finally:
conn.close()
pd.DataFrame(rows)