In [1]:
!pip install pymysql
Collecting pymysql
  Using cached PyMySQL-0.10.1-py2.py3-none-any.whl (47 kB)
Installing collected packages: pymysql
Successfully installed pymysql-0.10.1
In [15]:
# 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()
[{'eid': 104, 'salary': 43000}]
In [ ]:
 
In [6]:
!pip install mysql-connector-python
Collecting mysql-connector-python
  Downloading mysql_connector_python-8.0.22-cp37-cp37m-win_amd64.whl (842 kB)
Requirement already satisfied: protobuf>=3.0.0 in c:\programdata\anaconda3\lib\site-packages (from mysql-connector-python) (3.14.0)
Requirement already satisfied: six>=1.9 in c:\programdata\anaconda3\lib\site-packages (from protobuf>=3.0.0->mysql-connector-python) (1.14.0)
Installing collected packages: mysql-connector-python
Successfully installed mysql-connector-python-8.0.22
In [16]:
# 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()
(104, 43000)
In [17]:
df.head()
Out[17]:
eid salary
0 104 43000
In [18]:
# 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)
In [20]:
!pip install pony
Collecting pony
  Downloading pony-0.7.14.tar.gz (290 kB)
Building wheels for collected packages: pony
  Building wheel for pony (setup.py): started
  Building wheel for pony (setup.py): finished with status 'done'
  Created wheel for pony: filename=pony-0.7.14-py3-none-any.whl size=351283 sha256=494135b08ca2882061a268394e578454cd4cf68d953a5cf623b4361ff329e2d7
  Stored in directory: c:\users\avinash.navlani\appdata\local\pip\cache\wheels\de\7a\8b\82a3b5a0135fc6bad188f62e1bf6c4843142bc22c144879014
Successfully built pony
Installing collected packages: pony
Successfully installed pony-0.7.14
In [23]:
# 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()
class Emp(Entity):
    eid = PrimaryKey(int, auto=True)
    salary = Required(int)
GET NEW CONNECTION
SELECT `e`.`eid`, `e`.`salary`
FROM `emp` `e`

SELECT `e`.`eid`, `e`.`salary`
FROM `emp` `e`

eid|salary
---+------
104|43000 
104|43000 
In [ ]: