Python accesses the MySQL database and supports addition, deletion, modification, and query.

Python accesses the MySQL database and supports addition, deletion, modification, and query.

Overview:
I want to know more about accessing the MySQL database. However, since I recently learned Python, I will use Python to implement it below. These include creating databases and data tables, inserting records, deleting records, modifying record data, querying data, deleting data tables, and deleting databases. Another point is that we 'd better use a newly defined class to deal with it. This will make it easier to use in the future (only import is required to avoid duplicate manufacturing wheels ).

Implementation features:

1. encapsulate a DB class
2. Database Operations: create databases and data tables
3. Database Operations: insert records
4. Database Operation: insert multiple records at a time
5. Database Operations: delete records
6. Database Operations: Modify Record Data
7. Database Operation: Modify multiple records at a time
8. Database Operations: query data
9. Database Operation: delete a data table
10. Database Operation: delete a database

Database Class Definition:
HeroDB. py

#! /Usr/bin/env python

Import MySQLdb

DATABASE_NAME = 'hero'

Class HeroDB:
# Init class and create a database
Def _ init _ (self, name, conn, cur ):
Self. name = name
Self. conn = conn
Self. cur = cur
Try:
Cur.exe cute ('create database if not exists' + name)
Conn. select_db (name)
Conn. commit ()
Counter t MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

# Create a table
Def createTable (self, name ):
Try:
Ex = self.cur.exe cute
If ex ('show tables ') = 0:
Ex ('create table' + name + '(id int, name varchar (20), sex int, age int, info varchar (50 ))')
Self. conn. commit ()
Counter t MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

# Insert single record
Def insert (self, name, value ):
Try:
Self.cur.exe cute ('insert into '+ name + 'values (% s, % s)', value)
Counter t MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

# Insert more records
Def insertMore (self, name, values ):
Try:
Self.cur.exe cute.pdf ('insert into '+ name + 'values (% s, % s)', values)
Counter t MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

# Update single record from table
# Name: table name
# Values: waiting to update data
Def updateSingle (self, name, value ):
Try:
# Self.cur.exe cute ('update' + name + 'set name = '+ str (values [1]) +', sex = '+ str (values [2]) + ', age = '+ str (values [3]) +', info = '+ str (values [4]) + 'where id =' + str (values [0]) + ';')
Self.cur.exe cute ('update' + name + 'set name = % s, sex = % s, age = % s, info = % s where id = % s; ', value)
Counter t MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

# Update some record from table
Def update (self, name, values ):
Try:
Self.cur.exe cute.pdf ('update' + name + 'set name = % s, sex = % s, age = % s, info = % s where id = % s; ', values)
Counter t MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

# Get record count from db table
Def getCount (self, name ):
Try:
Count = self.cur.exe cute ('select * from' + name)
Return count
Counter t MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

# Select first record from database
Def selectFirst (self, name ):
Try:
Self.cur.exe cute ('select * from '+ name + ';')
Result = self. cur. fetchone ()
Return result
Counter t MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

# Select last record from database
Def selectLast (self, name ):
Try:
Self.cur.exe cute ('select * FROM '+ name + 'order BY id DESC ;')
Result = self. cur. fetchone ()
Return result
Counter t MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

# Select next n records from database
Def selectNRecord (self, name, n ):
Try:
Self.cur.exe cute ('select * from '+ name + ';')
Results = self. cur. fetchtasks (n)
Return results
Counter t MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

# Select all records
Def selectAll (self, name ):
Try:
Self.cur.exe cute ('select * from '+ name + ';')
Self. cur. scroll (0, mode = 'absolute ') # reset cursor location (mode = absolute | relative)
Results = self. cur. fetchall ()
Return results
Counter t MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

# Delete a record
Def deleteByID (self, name, id ):
Try:
Self.cur.exe cute ('delete from' + name + 'where id = % s; ', id)
Counter t MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

# Delete some record
Def deleteSome (self, name ):
Pass

# Drop the table
Def dropTable (self, name ):
Try:
Self.cur.exe cute ('drop table' + name + ';')
Counter t MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

# Drop the database
Def dropDB (self, name ):
Try:
Self.cur.exe cute ('drop database' + name + ';')
Counter t MySQLdb. Error, e:
Print "Mysql Error % d: % s" % (e. args [0], e. args [1])

Def _ del _ (self ):
If self. cur! = None:
Self. cur. close ()
If self. conn! = None:
Self. conn. close ()

Example:
TestHeroDB. py

#! /Usr/bin/env python

Import MySQLdb
From heroDB import HeroDB

Def main ():
Conn = MySQLdb. connect (host = 'localhost', user = 'root', passwd = '000000', db = 'hero', port = 260606, charset = 'utf8 ')
Cur = conn. cursor ()

# ------------------------------------------- Create -----------------------------------------------------
Hero = HeroDB ('hero', conn, cur)
Hero. createTable ('heros ')

# ------------------------------------------- Insert -----------------------------------------------------
Hero. insert ('heros ', [3, 'prophet', 0, 2000, 'the hero who in fairy tale. '])

# ------------------------------------------- Select -----------------------------------------------------
Print '-' * 60
Print 'first record'
Result = hero. selectFirst ('heros ')
Print result

Print '-' * 60
Print 'last record'
Result = hero. selectLast ('heros ')
Print result

Print '-' * 60
Print 'more records'
Results = hero. selectNRecord ('heros ', 3)
For item in results:
Print item

Print '-' * 60
Print 'all records'
Results = hero. selectAll ('heros ')
For item in results:
Print item

# ------------------------------------------- Update -----------------------------------------------------
Hero. updateSingle ('heros ', ['zeus', 1, 22000, 'The god.', 2])

Values = []
Values. append (['sunwukong ', 1, 1300, 'the hero who in fairy tale.', 1])
Values. append (['zeus', 1, 50000, 'the king who in The Quartet myth. ', 2])
Values. append (['phet ', 1, 20000, 'the hero who in fairy tale.3', 3])
Hero. update ('heros ', values)

# ------------------------------------------- Delete -----------------------------------------------------
Hero. deleteByID ('heros ', 1)

Hero. dropTable ('heros ')

Hero. dropDB ('hero ')

If _ name _ = '_ main __':
Main ()

Note: Please do not use them without thinking. If you want to implement a function, it is best to comment out other function points so as to comply with the unit test specifications.

Download source code:

------------------------------------------ Split line ------------------------------------------

Free in http://linux.bkjia.com/

The username and password are both www.bkjia.com

The specific download directory is in the/June/Python directory to access the MySQL database and implement its add, delete, modify, and query functions/

For the download method, see

------------------------------------------ Split line ------------------------------------------

This article permanently updates the link address: