python+mysql，python

一、数据库配置

1.安装数据库

[root@localhost ~]# yum install mariadb-server -y        #安装mariadb
[root@localhost ~]# systemctl start mariadb              #开启mariadb服务

2.mysql初始化

[root@localhost ~]# mysql_secure_installation            #设置密码，其余全部回车

3.数据库的网页管理工具

[root@localhost ~]# yum install http php -y         #下载http 和 php服务器
[root@localhost ~]#systemctl start httpd            #开启http服务
[root@localhost ~]# systemctl stop firewalld        #关闭火墙
根据php、mariadb的版本，选择phpmyadmin版本
[root@localhost ~]# cd /var/www/html/
[root@localhost html]# ls
phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@localhost html]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2   #解压
[root@localhost html]# mv phpMyAdmin-3.4.0-all-languages mysqladmin     #修改文件名
[root@localhost html]# cd mysqladmin
[root@localhost mysqladmin]# cp config.sample.inc.php config.inc.php
[root@localhost mysqladmin]# yum install php-mysql.x86_64 -y   #安装php支持的mysql软件
[root@localhost mysqladmin]# systemctl restart httpd

浏览器测试：
http://172.25.254.8/mysqladmin/

4.安装python-mysql软件

[kiosk@foundation8 ~]$ yum install gcc -y
[kiosk@foundation8 ~]$ yum install MySQL-python.x86_64 -y
[kiosk@foundation8 ~]$ pip install MySQL-python -y         #联网下载安装

完成后,在pycharm中调用数据库时输入M会提示MySQLdb

5.在数据库中建立一个表

[kiosk@foundation8 ~]$ mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]> create database python;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| python             |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use python;
Database changed
MariaDB [python]> create table userInfo(id int,name varchar(10));
Query OK, 0 rows affected (0.10 sec)

MariaDB [python]> show tables;
+------------------+
| Tables_in_python |
+------------------+
| userInfo         |
+------------------+
1 row in set (0.00 sec)

MariaDB [python]> select * from userInfo;
Empty set (0.00 sec)

MariaDB [python]> insert into userInfo(id,name)value(1,'tom');
Query OK, 1 row affected (0.32 sec)

MariaDB [python]> select * from userInfo;
+------+------+
| id   | name |
+------+------+
|    1 | tom  |
+------+------+
1 row in set (0.00 sec)

MariaDB [python]> update userInfo set name = 'zmy';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [python]> select * from userInfo;
+------+------+
| id   | name |
+------+------+
|    1 | zmy  |
+------+------+
1 row in set (0.00 sec)

MariaDB [python]> update userInfo set name = 'wwy' where id = 1;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [python]> select name from userInfo;
+------+
| name |
+------+
| wwy  |
+------+
1 row in set (0.00 sec)

数据库详细操作请点击：linux系统mariadb数据库管理

二、python数据库

查找

查_1.py

# _*_ coding:utf-8 _*_
"""
file:查_1.py
date:2018-07-25 10:20 AM
author:wwy
desc:

"""


import MySQLdb
# 打开门
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='123',db='python')

# 伸出手
cur = conn.cursor()

# 拿东西
recont = cur.execute('select * from userInfo')

# 把手伸回来
cur.close()

# 把门关上
conn.close()

print recont

运行结果：

显示内容只有一行

查_2.py

# _*_ coding:utf-8 _*_
"""
file:查_2.py
date:2018-07-25 10:27 AM
author:wwy
desc:

"""


import MySQLdb
# 打开门（连接数据库）
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='123',db='python')

# 伸出手（创建可以执行查询的指针，指针指向数据库）
#cur = conn.cursor()     #创建一个手（指针）

# 拿东西（执行）这个东西影响了多少行
recont = cur.execute('select * from userInfo')
# 指针指向数据
data = cur.fetchall()

# 把手伸回来（关闭指针）
cur.close()

# 把门关上（关闭链接）
conn.close()

print recont
print data

运行结果：

# 伸出手（创建可以执行查询的指针，指针指向数据库）
#cur = conn.cursor()     #创建一个手（指针）
# 以字典的方式显示
cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)

运行结果：

在python数据库中添加一个表,命名为usermg（可任意命名）

增添

增.py

# _*_ coding:utf-8 _*_
"""
file:增.py
date:2018-07-25 11:02 AM
author:wwy
desc:

"""


import MySQLdb

# 打开门
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='123',db='python')

# 伸出手
cur = conn.cursor()

# 操作数据
sql = 'insert into usermg(id,name,address) values(%s,%s,%s)'
params = ('1','zmy','usa')
recount = cur.execute(sql,params)

# 提交请求
conn.commit()

# 把手伸回来
cur.close()

# 把门关上
conn.close()
print recount

运行结果：

显示操作了一行
打开网页数据库查看

修改

改.py

# _*_ coding:utf-8 _*_
"""
file:改.py
date:2018-07-25 11:23 AM
author:wwy
desc:

"""


import MySQLdb

# 打开门
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='123',db='python')

# 伸出手
cur = conn.cursor()

# 操作数据
sql = 'update usermg set name = %s where id = %s'
params = ('xmm','1',)
recount = cur.execute(sql,params)

# 提交请求
conn.commit()

# 把手伸回来
cur.close()

# 把门关上
conn.close()
print recount

运行结果：

已更改一行内容
网页数据库管理器

name已改为xmm

删除

删.py

# _*_ coding:utf-8 _*_
"""
file:删.py
date:2018-07-25 11:18 AM
author:wwy
desc:

"""


import MySQLdb

# 打开门
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='123',db='python')

# 伸出手
cur = conn.cursor()

# 操作数据
sql = 'delete from usermg where id = %s'
params = (1,)         #这里params是一个元组,如果不加后面那个逗号会默认params为int型
recount = cur.execute(sql,params)

# 提交请求
conn.commit()

# 把手伸回来
cur.close()

# 把门关上
conn.close()
print recount

运行结果：

网页数据库：

表中已经没有内容

插入多条数据

插入多条数据.py

# _*_ coding:utf-8 _*_
"""
file:插入多条数据.py
date:2018-07-25 11:38 AM
author:wwy
desc:

"""


import MySQLdb

# 打开门
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='123',db='python')

# 伸出手
cur = conn.cursor()

# 操作数据
recount = 'insert into usermg(id,name,address) values(%s,%s,%s)'
li =[
    ('2','xls','china'),
    ('3','jcl','china'),
]
recount = cur.executemany(recount,li)

# 提交请求
conn.commit()

# 把手伸回来
cur.close()

# 把门关上
conn.close()
print recount

运行结果：

网页数据库管理器：

已插入两行

练习
需求：去超市买东西,向收银员支付100元
首先在python库中创建一张表

利用插入多条数据,顾客有100元,收银员还没有收到钱

# _*_ coding:utf-8 _*_
"""
file:插入多条数据.py
date:2018-07-25 11:38 AM
author:wwy
desc:

"""


import MySQLdb

# 打开门
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='123',db='python')

# 伸出手
cur = conn.cursor()

# 操作数据
recount = 'insert into money(id,money) values(%s,%s)'
li =[
    ('1','100'),
    ('2','0'),
]
recount = cur.executemany(recount,li)

# 提交请求
conn.commit()

# 把手伸回来
cur.close()

# 把门关上
conn.close()
print recount

运行结果：


commit.py

# _*_ coding:utf-8 _*_
"""
file:commit.py
date:2018-07-25 1:52 PM
author:wwy
desc:
提交和回滚    在数据库李叫事务操作
"""

import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='123',db='python')
cur = conn.cursor()

sql = 'update money set money = %s where id =1'
params = ('0',)
recount = cur.execute(sql,params)

sql = 'update money set money = %s where id =2'
params = ('100',)
recount = cur.execute(sql,params)
conn.commit()

运行结果：