不得不说python是一门很强大的语言
下载安装MySQL库,linux装好后即可使用,windows系统装好注意配置环境变量。
先在我的数据库中创建好DNS_MySQL数据库,再用python对其操作。
代码截图如下:
输出结果如下图所示:
代码如下:
import MySQLdb
def connectdb():
print('Connecting MySQL...')
db = MySQLdb.connect("localhost","root","949501","DNS_MySQL")
print('Conected!')
return db
def createtable(db):
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS DNS_basic")
sql = """CREATE TABLE DNS_basic(
ID CHAR(10) NOT NULL,
A CHAR(20),
NS CHAR(40),
MX CHAR(30),
SOA CHAR(40));"""
# create TABLE DNS_basic
cursor.execute(sql)
def insertdb(db):
cursor = db.cursor()
# SQL INSERT DATA
sql = """INSERT INTO DNS_basic
VALUES ('001','202.102.144.56', 'dns1.hitwh.edu.cn dns2.hitwh.edu.cn', '10,mail.hit.edu.cn','dns1.hitwh.edu.cn root.hitwh.edu.cn')
"""
try:
cursor.execute(sql)
db.commit()
except:
print 'INSERT FIELD!'
db.rollback()
def querydb(db):
cursor = db.cursor()
sql = "SELECT * FROM DNS_basic"
try:
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
ID = row[0]
A = row[1]
NS= row[2]
MX= row[3]
SOA= row[4]
print "\nID:\n %s\n A:\n %s\n NS:\n %s\n MX:\n %s\n SOA:\n%s \n" % (ID, A, NS, MX, SOA)
except:
print "Error: unable to fecth data"
def deletedb(db):
cursor = db.cursor()
sql = "DELETE FROM DNS_basic WHERE ID = 001"
try:
cursor.execute(sql)
db.commit()
except:
print 'DELETED FIELD!'
db.rollback()
def closedb(db):
db.close()
def main():
db = connectdb() # CONNECT MYSQL
createtable(db) # CREATE TABLE
insertdb(db) # INSERT DATA
print '\nINSERT DATA:'
querydb(db)
deletedb(db) # DELETE DATA
print '\nDELETE DATA:'
print 'DELETED SUCCESSFULLY!'
querydb(db)
closedb(db) # CLOSE DATA
if __name__ == '__main__':
main()
如果要更新表单使用代码:
sql ="UPDATE DNS_basic SET NS = NS + dns3.hitwh.edu.cn WHERE ID = '%s' % ('001')
try:
cursor.execute(sql)
db.commit()
即在ID=001的NS记录上加上dns3.hitwh.edu.cn(当然这是一个不存在的记录)
输出则变为
ID:
001
A:
202.102.144.56
NS:
dns1.hitwh.edu.cn dns2.hitwh.edu.cn dns3.hitwh.edu.cn
MX:
10,mail.hit.edu.cn
SOA:
dns1.hitwh.edu.cn root.hitwh.edu.cn