!pip install pymysql
# import pymysql connector module
import pymysql
# Create a connection object using connect() method with parameters IP Address, user name, password, database name, character set and cursor type.
connection = pymysql.connect(host='localhost', # IP address of the MySQL database server
user='root', # user name
password='root', # password
db='emp', # database name
charset='utf8mb4', # character set
cursorclass=pymysql.cursors.DictCursor) # cursor type
try:
with connection.cursor() as cur:
# Inject a record in database
sql_query = "INSERT INTO `emp` (`eid`, `salary`) VALUES (%s, %s)"
cur.execute(sql_query, (104,43000))
# Execution will not commit records automatically. We need to commit the record insertion explicitly.
connection.commit()
with connection.cursor() as cur:
# Read records from employee table
sql_query = "SELECT * FROM `emp`"
cur.execute(sql_query )
table_data = cur.fetchall()
print(table_data)
except:
print("Exception Occurred")
finally:
connection.close()
!pip install mysql-connector-python
# Import the required connector
import mysql.connector
import pandas as pd
# Establish a database connection to mysql
connection=mysql.connector.connect(user='root',password='root',host='localhost',database='emp')
# Create a cursor
cur=connection.cursor()
# Running sql query
cur.execute("select * from emp")
# Fetch all the records and print it one by one using for loop
records=cur.fetchall()
for i in records:
print(i)
# Create a DataFrame from fetched records.
df = pd.DataFrame(records)
# Assign column names to DataFrame
df.columns = [i[0] for i in cur.description]
# close the connection
connection.close()
df.head()
# Import the sqlalchemy engine
from sqlalchemy import create_engine
# Instantiate engine object
en = create_engine("mysql+pymysql://{user}:{pw}@localhost/{db}"
.format(user="root",
pw="root",
db="emp"))
# Insert the whole dataframe into the database using to_sql() with the table name, if_exists and chunksize parameter
df.to_sql('emp', con=en, if_exists='append',chunksize=1000, index= False)
!pip install pony
# Import pony module
from pony.orm import *
# Create database
db = Database()
# Define entities
class Emp(db.Entity):
eid = PrimaryKey(int,auto=True)
salary = Required(int)
# Check entity definition
show(Emp)
# Bind entities to MySQL database
db.bind('mysql', host='localhost', user='root', passwd='root', db='emp')
# Generate required mappings for entities
db.generate_mapping(create_tables=True)
# turn on the debug mode
sql_debug(True)
# Select the records from Emp entities or emp table
select(e for e in Emp)[:]
# Show the values of all the attribute
select(e for e in Emp)[:].show()