- Current version: 5.7.19
- Vendor: SUN Micro Systems/Oracle Corporation
- Open Source and Freeware
- Default Port: 3306
- Default user: root
Note
- In MySQL, everything we have to work with our own databases, which are also known as Logical Databases.
- The following are 4 default databases available in mysql.
- information_schema
- mysql
- performance_schema
- test
- Diagram
- In the above diagram only one physical database is available and 4 logical databases are available.
Commonly used commands in MySql
- To know available databases:
mysql> show databases; - To create our own logical database
mysql> create database durgadb; - To drop our own database:
mysql> drop database durgadb; - To use a particular logical database
mysql> use durgadb; OR mysql> connect durgadb; - To create a table:
create table employees(eno int(5) primary key,ename varchar(10),esal double(10,2),eaddr
varchar(10)); - To insert data:
insert into employees values(100,’Durga’,1000,’Hyd’);
insert into employees values(200,’Ravi’,2000,’Mumbai’); - In MySQL instead of single quotes we can use double quotes also.
Driver/Connector Information
- From Python program if we want to communicates with MySql database,compulsory some
- translator is required to convert python specific calls into mysql database specific calls and mysql
- database specific calls into python specific calls. This translator is nothing but Driver or Connector.
- Diagram
- We have to download connector seperately from mysql database.
- https://dev.mysql.com/downloads/connector/python/2.1.html
How to check installation
- From python console we have to use
- help(“modules”)
- In the list of modules,compulsory mysql should be there.
Note
- In the case of Python3.4 we have to set PATH and PYTHONPATH explicitly
- PATH=C:\Python34
- PYTHONPATH=C:\Python34\Lib\site-packages
Q. Write a Program to create table,insert data and display data by using mysql database.
Python
import mysql.connector
try:
con=mysql.connector.connect(host='localhost',database='durgadb',user='root',password='root')
cursor=con.cursor()
cursor.execute("create table employees(eno int(5) primary key,ename varchar(10),esal double(10,2),eaddr varchar(
)")
print("Table Created...")
sql = "insert into employees(eno, ename, esal, eaddr) VALUES(%s, %s, %s, %s)"
records=[(100,'Sachin',1000,'Mumbai'),
(200,'Dhoni',2000,'Ranchi'),
(300,'Kohli',3000,'Delhi')]
cursor.executemany(sql,records)
con.commit()
print("Records Inserted Successfully...")
cursor.execute("select * from employees")
data=cursor.fetchall()
for row in data:
print("Employee Number:",row[0])
print("Employee Name:",row[1])
print("Employee Salary:",row[2])
print("Employee Address:",row[3])
print()
print()
except mysql.connector.DatabaseError as e:
if con:
con.rollback()
print("There is a problem with sql :",e)
finally:
if cursor:
cursor.close()
if con:
con.close()Q. Write a Program to copy data present in employees table of mysql database into Oracle database.
Python
import mysql.connector
import cx_Oracle
try:
con=mysql.connector.connect(host='localhost',database='durgadb',user='root',password='root')
cursor=con.cursor()
cursor.execute("select * from employees")
data=cursor.fetchall()
list=[]
for row in data:
t=(row[0],row[1],row[2],row[3])
list.append(t)
except mysql.connector.DatabaseError as e:
if con:
con.rollback()
print("There is a problem with MySql :",e)
finally:
if cursor:
cursor.close()
if con:
con.close()
try:
con=cx_Oracle.connect('scott/tiger@localhost')
cursor=con.cursor()
sql="insert into employees values(:eno,:ename,:esal,:eaddr)"
cursor.executemany(sql,list)
con.commit()
print("Records Copied from MySQL Database to Oracle Database Successfully")
except cx_Oracle.DatabaseError as e:
if con:
con.rollback()
print("There is a problem with sql",e)
finally:
if cursor:
cursor.close()
if con:
con.close() https://dev.mysql.com/downloads/connector/python/2.1.html
Python
create table employees(eno int(5) primary key,ename varchar(10),esal double(10,2),eaddr varchar(10));
insert into employees values(100,'Durga',1000,'Hyd');
insert into employees values(200,'Ravi',2000,'Mumbai');
insert into employees values(300,'Shiva',3000,'Hyd');