python数据库编程

一、

yum install mariadb-server -y

对数据库安全初始化（设置登陆密码）

二、数据库可视化

下载phpMyAdmin-3.4.0-all-languages.tar.bz2安装包

2.安装PHP MySQL连接数据库

3.重启httpd
 4.将安装包复制到/var/www/html/默认发布目录下，便于测试
    cd /var/www/html
    cp /root/Desktop/phpMyAdmin/Admin-3.4.0-all-languages.tar.br2./
    ls
     yum install php php-mysql.x86_64 -y

5.解压安装包
   tar jxf phpMyAdmin/Admin-3.4.0-all-languages.tar.br2.
   ls
  6.重命名数据库管理软件目录 方便管理
    mv phpMyAdmin/Admin-3.4.0-all-languages mysqladmin
    cd mysqladmin
    ls
  7.测试
     172.25.254.45/mysqladmin/

三、 yum install gcc -y

          yum  install MySQL-python -y

         pip install Mysql-python           #这里要连接网络

查看python3数据库中的userinfo表

import MySQLdb
# 打开门
conn = MySQLdb.Connect(host ='127.0.0.1',user='root',passwd='dd',db='python3')
# 伸出手
cur = conn.cursor() # 创建一个手
# 拿东西
# 这个操作影响了多少行(有多少行被操作了)
reCount = cur.execute('select * from userInfo')
# 把手伸回来
cur.close()
# 把门观赏
conn.close()
print reCount

给python3数据库中的表count更新两行数据

import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='dd',db='python3')
cur = conn.cursor()
sql = 'update count set money = %s where id =1'
params = (0,)
reCount = cur.execute(sql,params)
sql = 'update count set money = %s where id = 2'
params = (100,)
reCount = cur.execute(sql,params)
conn.commit()
cur.close()
conn.close()

增

import MySQLdb
# 打开门
conn = MySQLdb.connect(host='127.0.0.1', user='root', passwd='dd', db='python3')
# 伸出手
cur = conn.cursor()
sql = 'delete from usermg where id = %s'
params = (1,)
reCount = cur.execute(sql,params)
# 提交请求
conn.commit()
# 把手伸回来
cur.close()
# 把门关上
conn.close()

删

import MySQLdb
# 打开门
conn = MySQLdb.connect(host='127.0.0.1', user='root', passwd='dd', db='python3')
# 伸出手
cur = conn.cursor()
sql = 'delete from usermg where id = %s'
params = (1,)
reCount = cur.execute(sql,params)
# 提交请求
conn.commit()
# 把手伸回来
cur.close()
# 把门关上
conn.close()

 

改

import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='dd',db='python3')
cur = conn.cursor()
sql = 'update usermg set name = %s where id = %s '
params = ('pp','1')
reCount = cur.execute(sql,params)
conn.commit()
cur.close()
conn.close()

查

import MySQLdb
# 打开门
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='dd',db='python2')
# 伸出手
#cur = conn.cursor() #创建一个手
cur = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
# 拿东西
# 这个操作影响了多少行(有多少行被操作)
reCount = cur.execute('select * from userInfo')
data = cur.fetchall()
# 把手伸回来
cur.close()
# 把门关上
conn.close()
print reCount
print data

处理多条数据

import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='dd',db='python2')
cur = conn.cursor()
li = [
    ('2','haha','www'),
    ('3','kiki','qqq')
]
# 打开一次数据库，执行多条数据操作
reCount = cur.executemany('insert into usermg(id,name,address) values(%s,%s,%s)',li)
# 提交请求
conn.commit()
# 把手伸回来
cur.close()
# 把门关上
conn.close()
print reCount

提交和回滚

import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1', user='root', passwd='dd', db='python2')
cur = conn.cursor()
sql = 'update money set money = %s where id =1'
params = (0,)
reCount = cur.execute(sql,params)
#conn.commit()
sql = 'updateee money set money = %s where id =2'
params = (300,)
reCount = cur.execute(sql,params)
conn.commit()
# 把手伸回来
cur.close()
# 把门关上
conn.close()