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

Source: Internet
Author: User
I want to know more about accessing the MySQL database. However, since I recently learned Python, I will use Python to implement it below. This includes creating databases.

I want to know more about accessing the MySQL database. However, since I recently learned Python, I will use Python to implement it below. This includes creating databases.

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, sex = '+ str (values [2]) +', age = '+ str (values [3]) + ', info =' + str (values [4]) + 'where ;')
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.