Python connection MySQL

#!/usr/bin/env python
# Coding=utf-8

Import MySQLdb

Def connectdb ():
Print (' Connect to MySQL server ... ')
# Open Database connection
# Username: root, Password: root, username and password need to be changed to your own MySQL username and password, and to create a database test, and create a good table in the test database student
db = MySQLdb.connect ("localhost", "root", "root", "test")
Print (' Connected! ')
Return db

#删除表
def dropteble (Db,sql):
# get an action cursor using the cursor () method
cursor = Db.cursor ()
# If Table sutdent is present, delete first
Cursor.execute (SQL)

#创建表
def createtable (Db,sql):
# get an action cursor using the cursor () method
cursor = Db.cursor ()
Cursor.execute (SQL)

#插入数据
def insertdb (Db,sql):
# get an action cursor using the cursor () method
cursor = Db.cursor ()

Try
# Execute SQL statement
Cursor.execute (SQL)
# Commit to Database execution
Db.commit ()
print ' Insert data successfully! '
Except
# Rollback In case there are any error
print ' failed to insert data! '
Db.rollback ()

#查询
def querydb (Db,sql):
# get an action cursor using the cursor () method
cursor = Db.cursor ()
Try
# Execute SQL statement
Cursor.execute (SQL)
# Get a list of all records
Results = Cursor.fetchall ()
For row in results:
Print row
Except
Print "error:unable to FECTH data"

#删除
def deletedb (Db,sql):
# get an action cursor using the cursor () method
cursor = Db.cursor ()
Try
# Execute SQL statement
Cursor.execute (SQL)
# Commit Changes
Db.commit ()
print ' Delete data successfully! '
Except
print ' Delete data failed! '
# Roll Back when an error occurs
Db.rollback ()

#修改
def updatedb (Db,sql):
# get an action cursor using the cursor () method
cursor = Db.cursor ()
Try
# Execute SQL statement
Cursor.execute (SQL)
# Commit to Database execution
Db.commit ()
print ' Modify data successfully! '
Except
print ' failed to update data! '
# Roll Back when an error occurs
Db.rollback ()

#关闭资源
def closedb (db):
Db.close ()



def main ():
Dropteble_sql= ' DROP TABLE IF EXISTS Student '

Createtable_sql= "" "CREATE TABLE Student (
ID CHAR (Ten) is not NULL,
Name CHAR (8),
Grade INT) "" "

Insertdb_sql= "" "INSERT into Student
VALUES (' 001 ', ' czq ', 70),
(' 002 ', ' LHQ ', 80),
(' 003 ', ' MQ ', 90),
(' 004 ', ' WH ', 80),
(' 005 ', ' HP ', 70),
(' 006 ', ' YF ', 66),
(' 007 ', ' TEST ', 100) ' ""

Querydb_sql= "SELECT * from Student"
Deletedb_sql= "DELETE from Student WHERE Grade = '%d '"% (100)
Updatedb_sql= "UPDATE Student SET Grade = Grade + 3 WHERE ID = '%s '"% (' 003 ')

db = Connectdb () # Connect to MySQL Database
Dropteble (Db,dropteble_sql) #删除表
CreateTable (db,createtable_sql) # CREATE TABLE
Insertdb (db,insertdb_sql) # Inserting data
print ' \ n after inserting data: '
Querydb (Db,querydb_sql)
Deletedb (db,deletedb_sql) # Delete data
print ' \ n Delete data: '
Querydb (Db,querydb_sql)
UpdateDB (db,updatedb_sql) # Update data
print ' \ n Update data: '
Querydb (Db,querydb_sql)

Closedb (DB) # Close Database


if __name__ = = ' __main__ ':
Main ()

Python connection MySQL