数据库
数据库的连接
python2中:MySQLdb模块
python3中:pymysql模块
# 首先,导入pymysql
import pymysql
# 连接数据库
conn = pymysql.connect(host='localhost',user='root',passwd='redhat',db='westos',charset='utf8')
# 创建游标,给数据库发送sql指令
cur = conn.cursor()
# 执行sql语句
try:
insert_sqli = 'insert into aa values('%s','%s')' %('user23','123') # aa为表的名字
cur.execute(insert_sqli)
except Exception as e:
print ('sql excute failed:%s' %(insert_sqli))
else:
print ('sql excute success:%s' %(insert_sqli))
# 提交sql语句,作用于数据库
conn.commit()
# 先关闭游标
cur.close()
# 关闭数据库连接
conn,close()
批量插入多条数据
import pymysql
def create_user_data(n):
users = []
for i in range(n):
username = 'user' + str(i+1)
users.append(username,'000000')
return users
# 1.数据库的连接
conn = pymysql.connect(host='localhost',user='root',passwd='redhat',db='westos',charset='utf8')
# 2.创建游标,给数据库发送sql指令
cur = conn.cursor()
# 3.执行sql语句:插入多条数据
try:
# 执行的插入语句;
insert_sqli = 'insert into userinfo values(%s,%s)'
# 执行函数,随即生成n条用户数据;列表里面嵌套元组;[('user1','000000'),('user2','000000')]
users = create_user_data(90)
# 'insert into userinfo values(%s,%s)' %('user1','000000')
cur.executemany(insert_sqli,users)
except Exception as e:
print ('sql execute failed')
else:
print ('sql execute success')
# 4.提交sql语句,作用以数据库
conn.commit()
# 5.先关闭游标
cur.close()
# 6.关闭数据库的连接
conn.close()
查看数据库表的内容
import pymysql
# 1.连接数据库
conn = pymysql.connect(host='localhost',user='root',passwd='redhat',db='westos',charset='utf8')
# 2.创建游标,给数据库发送sql指令
cur = conn.cursor()
# 3.执行sql语句:查看数据
res = cur.execute('select * from userinfo;')
# 每一次查看表中的一条数据
print (cur.fetchone())
# 如何移动游标;relative:相对的,相对当前游标向前还是向后移动;
# absolute,默认移动到最开始;
cur.scroll(2,'relative')
print(cur.fetchone())
cur.scroll(5,'absolute')
print(cur.fetchone())
# 查看指定条表的内容,元组里面嵌套元组;
print(cur.fetchmany(5))
# 显示所有数据
print(cur.fetchall())
# 依次将数据保存到文件中;
try:
with open('/tmp/users.txt','w') as f:
for userinfo in cur.fetchall():
info = ':'.join(userinfo)
f.write(info+'\n')
except Exception as e:
print(e)
else:
print('success')
# 4.先关闭游标
cur.close()
# 5.关闭数据库连接
conn.close()
封装数据库
import pymysql
db_config = {'host':'localhost','user':'root','pqsswd':'redhat','db':'westos','charset':'utf8'}
class Mysql(object):
def __init__(self,db_config):
try:
self.db_config = db_config
self.conn = pymysql.connect(**self.db_config)
self.sur = self.conn.cursor()
except Exception as e:
print('连接数据库失败:', e)
else:
print ('连接数据库成功!')
def __str__(self):
return "Connect(%s user=%s db=%s)" %(self.db_config['host'],self.db_config['user'],self.db_config['db'])
def __del__(self):
self.conn.commit()
self.cur.close()
self.conn,close()
test_sql = Mysql(db_config)
print (str(test_sql))
获取单个表的字段名和信息
import pymysql
conn = pymysql.connect(host='localhost',user='root',passwd='redhat',db='westos',charset='utf8')
cur = conn.cursor()
res = cur.execute('select * from userinfo;')
desc = cur.description
print('表的描述信息:',desc)
table_header = [item[0] for item in desc]
print ('表头:',table_header)
print ('\t',join(table_header))
for row in cur.fetchall():
print('\t'.join(row))
cur.close()
conn.close()
使用with安全上下文管理器
import pymysql
conn = pymysql.connect(host='localhost',user='root',passwd='redhat',db='westos',charset='utf8')
with conn:
cur = conn.cursor()
多台mysql主机的远程备份
import os
import time
mysql_list = [{'host':'172.25.254.250','user':'root','passwd':'redhat','db':'users'}]
BACKUP_DIR = '/opt/BACKUP/'
if not os.path.exists(BACKUP_DIR):
os.mkdir(BACKUP_DIR)
print('%s craete success!' %(BACKUP_DIR))
else:
try:
for host in mysql_list:
shell_sen = 'mysqldump -h %s -u%s -p%s %s>%s'
host_ip = host['host']
user = host['user']
passwd = host['passwd']
db = hsot['db']
backup_filename = BACKUP_DIR + '%s_%s.dump' %(host_ip,db)
print(backup_filename)
print(shell_sen %(host_ip,user,passwd,db,backup_filename))
os.system(shell_sen %(host_ip,user,passwd,db,backup_filename))
print('backup %s host' %(host_ip))
except Exception as e:
print (e)
else:
print ('success')
os.mknod(time.ctime())
# os.mknod() 方法用于创建一个指定文件名的文件系统节点(文件,设备特别文件或者命名pipe)。