Python Database Connectivity2|Manohar Papasani

How to instal MySQLDB module in python

pip install --upgrade pip
pip install mysql-connector-python
pip install mysqlclient.

The Python DB API implementation for
MySQL -- MySQLdb.
PostgreSQL-- psycopg, PyGresQL and pyPgSQL modules.
Oracle---dc_oracle2 and cx_oracle.
DB2--Pydb2

connecting to mysql database

step1:
import MySQLdb

step2:
connecting to database
we have to use a function called connect which is present in
MySQLdb module.

syntax
------
varname=MySQLdb.connect("ipaddress","user","password","databasename")

ex:
conn=MySQLdb.connect("localhost","root","root","test")

step3:
open a cursor
cur=conn.cursor()

step4:
call execute function
res=cur.execute("sqlquery");

step5:
close conn
conn.close()

C:\Users\user python
Python 3.6.3 (v3.6.3:2c5fed8, Oct 3 2017, 17:26:49) [MSC v.1900 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
import MySQLdb
conn=MySQLdb.connect("localhost","root","root","test")
cursor=conn.cursor()
sql="create table employee420(id int,name varchar(30))"
cursor.execute(sql)
conn.close()



Inserting a record into a table
--------------------------------
import MySQLdb
conn=MySQLdb.connect("localhost","root","root","test")
cursor=conn.cursor()
sql="insert into emp1 values(1,'manohar')"
cursor.execute(sql)
cursor.execute("commit")
conn.close()


updating a table in python
--------------------------
import MySQLdb
conn=MySQLdb.connect("localhost","root","root","test")
cursor=conn.cursor()
sql="update emp1 set name='manohar1' where id=1"
cursor.execute(sql)
cursor.execute("commit")
conn.close()

deleting a row in a table
-------------------------
import MySQLdb
conn=MySQLdb.connect("localhost","root","root","test")
cursor=conn.cursor()
sql="delete from emp1 where id=1"
cursor.execute(sql)
cursor.execute("commit")
conn.close()

select data from a table
------------------------
import MySQLdb
conn=MySQLdb.connect("localhost","root","root","test")
cursor=conn.cursor()
sql="delete from emp1 where id=1"
cursor.execute(sql)
cursor.execute("commit")
conn.close()

import MySQLdb
conn=MySQLdb.connect("localhost","root","root","test")
cursor=conn.cursor()
sql="select *from emp1 "
cursor.execute(sql)
res=cursor.fetchall()
#cursor.execute("commit")
print(res)
conn.close()