Python accesses MySQL database and implements its additions and deletions function

Overview:

For the operation of accessing the database, I think we all have some understanding. However, because of the recent learning of Python, the following python is used to implement it. These include creating databases and data tables, inserting records, deleting records, modifying record data, querying data, deleting data tables, and deleting databases. Another thing is that we'd better use a newly defined class to handle this. Because this will make it more convenient to use in the future (only need to import, avoid the repetition of manufacturing wheels).

Introduction to Implementing Features:

1. Encapsulation of a DB class

2. Database operations: Creating databases and Data tables

3. Database operations: Inserting records

4. Database operations: Deleting records

5. Database operations: Modifying record Data

6. Database operations: Querying data

7. Database operations: Deleting data tables

8. Database operations: Deleting a database

definition of the database class:

herodb.py

#!/usr/bin/env pythonimport mysqldbdatabase_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:CU R.execute (' Create database if not exists ' + name) conn.select_db (name) conn.commit () except Mysqldb.error, E:print "Mysql Error%d:%s"% (E.args[0], e.args[1]) # Create a tab                Le def createtable (self, name): Try:ex = Self.cur.execute if EX (' show tables ') = = 0: Ex (' CREATE table ' + name + ' (id int, name varchar (+), sex int, age int, info varchar ()) ') SE                Lf.conn.commit () except 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.execute (' insert INTO ' + Nam E + ' VALUES (%s,%s,%s,%s,%s) ', value ' except 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.executemany (' INSERT INTO ' + name + ' VALUES (%s,%s,%s,%s,%s) ', values) except Mysqldb.error, E:print "Mysql Error%d:%s"% (E.arg S[0], e.args[1]) # Update single record from table # name:table name # values:waiting to update dat A def updatesingle (self, Name, value): Try: # self.cur.execute (' update ' + name + ' Set name= ' + str ( VALUES[1]) + ', sex= ' + str (values[2]) + ', age= ' + str (values[3]) + ', info= ' + str (values[4]) + ' where id= ' + str (value            S[0]) + '; ') Self.cur.execute (' Update ' + name + ' Set name=%s, sex=%s, age=%s, info=%s where id=%s; ', value ' except Mysqldb.err Or, 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.executemany (' update ' + name + ' Set name=%s, sex=%s, age=%s , info=%s where id=%s; ', values ') except Mysqldb.error, E:print "Mysql Error%d:%s"% (E.args[0], E.arg S[1]) # Get record count from DB table Def getcount (self, name): Try:count = Self.cur . Execute (' select * from ' + name) return count except Mysqldb.error, E:print "Mysql Error%d :%s "% (E.args[0], e.args[1]) # Select first record from Database Def selectfirst (self, name): t            Ry:self.cur.execute (' select * from ' + name + '; ') result = Self.cur.fetchone () return result except 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.execute (' SELECT * from ' + name + ' ORDER by ID DESC; 'result = Self.cur.fetchone () return result except Mysqldb.error, E:print "Mysql Error%d:%s "% (E.args[0], e.args[1]) # Select Next N records from Database Def selectnrecord (self, NA            Me, N): Try:self.cur.execute (' select * from ' + name + '; ') results = Self.cur.fetchmany (n) return results except Mysqldb.error, e:print "Mysql Error%d :%s "% (E.args[0], e.args[1]) # Select All Records def selectall (self, name): Try:se            Lf.cur.execute (' select * from ' + name + '; ') Self.cur.scroll (0, mode= ' absolute ') # Reset cursor location (mode = Absolute | relative) results = Self.cur.fet Chall () return results except Mysqldb.error, e:print "Mysql Error%d:%s"% (E.args[0], e.ar GS[1]) # Delete a record def deletebyid (self, Name, id): Try:self.cur.execute (' DeletE from ' + name + ' where id=%s; ', id) except 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 droptabl        E (self, Name): Try:self.cur.execute (' drop table ' + name + '; ')     Except Mysqldb.error, E:print "Mysql Error%d:%s"% (E.args[0], e.args[1]) # Drop the database        def dropdb (self, Name): Try:self.cur.execute (' drop database ' + name + '; ')        Except 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 ()

Examples of use:

testherodb.py

#!/usr/bin/env pythonimport mysqldbfrom herodb import herodbdef Main (): conn = MySQLdb.connect (host= ' localhost ', user= ' Root ', passwd= ' 260606 ', db= ' hero ', port=3306, charset= ' utf8 ') cur = conn.cursor () #---------------------------- ---------------Create-----------------------------------------------------Hero = Herodb (' hero ', Conn, cur) hero.c Reatetable (' heros ') #-------------------------------------------Insert-----------------------------------------    ------------Hero.insert (' heros ', [3, ' Prophet ', 0, +, ' the hero who in fairy tale. ') #-------------------------------------------Select-----------------------------------------------------print '- ' * print ' first record ' result = Hero.selectfirst (' heros ') print result print '-' * print ' last Record ' result = Hero.selectlast (' heros ') print result print '-' * print ' more record ' results = her    O.selectnrecord (' Heros ', 3) for item in results:    Print item print '-' * print ' all record ' results = Hero.selectall (' heros ') for item in results: Print Item #-------------------------------------------update------------------------------------------ -----------hero.updatesingle (' heros ', [' Zeus ', 1, 22000, ' The God ', 2]) values = [] values.append ([' Sunwuko Ng ', 1, 1300, ' The hero who in fairy tale. ', 1]) values.append ([' Zeus ', 1, 50000, ' The King of the Quartet Myth. '), 2]) values.append ([' Prophet ', 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__ ': Mai N ()

Source Download:

http://download.csdn.net/detail/u013761665/8615981

Python accesses MySQL database and implements its additions and deletions function