Python is connected to the mysql database example of of to add delete and change operations
- 2020-04-02 13:20:40
- OfStack
I. relevant code
The database configuration class MysqlDBConn. Py
#encoding=utf-8
'''
Created on 2012-11-12
Mysql Conn The connection class
'''
import MySQLdb
class DBConn:
conn = None
# Establish a connection to the database system
def connect(self):
self.conn = MySQLdb.connect(host="localhost",port=3306,user="house", passwd="house" ,db="house",charset="utf8")
# Get operation cursor
def cursor(self):
try:
return self.conn.cursor()
except (AttributeError, MySQLdb.OperationalError):
self.connect()
return self.conn.cursor()
def commit(self):
return self.conn.commit()
# Close the connection
def close(self):
return self.conn.close()
MysqlDemo. Py class
#encoding=utf-8
'''
Created on 2012-11-12
@author: Steven
Mysql operation Demo
Done: Create table, delete table, data add, delete, change, batch insert
'''
import MysqlDBConn
dbconn = MysqlDBConn.DBConn()
def process():
# Establish a connection
dbconn.connect()
# Delete table
dropTable()
# Create a table
createTable()
# Batch insert data
insertDatas()
# A single insert
insertData()
# Update the data
updateData()
# Delete the data
deleteData()
# Query data
queryData()
# Release the connection
dbconn.close()
def insertDatas():
sql = "insert into lifeba_users(name, realname, age) values(%s, %s, %s)"
tmp = (('steven1', ' test 1',26), ('steven2', ' test 2',25))
executemany(sql, tmp)
def updateData():
sql = "update lifeba_users set realname = '%s' where name ='steven1'"%(" test 1 Modify the ")
execute(sql)
def deleteData():
sql = "delete from lifeba_users where id=2"
execute(sql)
def queryData():
sql = "select * from lifeba_users"
rows = query(sql)
printResult(rows)
def insertData():
sql = "insert into lifeba_users(name, realname, age) values('%s', '%s', %s)"%("steven3"," test 3","26")
print sql
execute(sql)
def executemany(sql, tmp):
''' Insert multiple pieces of data '''
conn=dbconn.cursor()
conn.executemany(sql, tmp)
def execute(sql):
''' perform sql'''
conn=dbconn.cursor()
conn.execute(sql)
def query(sql):
''' The query sql'''
conn=dbconn.cursor()
conn.execute(sql)
rows = conn.fetchmany(10)
return rows
def createTable():
''' Create a table '''
conn=dbconn.cursor()
conn.execute('''
CREATE TABLE `lifeba_users` (
`ID` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`realName` varchar(50) default NULL,
`age` int(11) default NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
''')
# dbconn.commit()
def dropTable():
''' Delete table '''
conn=dbconn.cursor()
conn.execute('''
DROP TABLE IF EXISTS `lifeba_users`
''')
# dbconn.commit()
def printResult(rows):
for row in rows:
for i in range(0,len(row)):# Through the array
print row[i], # add , Print without breaking lines
print ''
if __name__ == '__main__':
process()