python-----mysql

准备工作

首先安装mysql
[root@foundation53 yum.repos.d]# yum install mariadb-server -y
[root@foundation53 ~]# systemctl start mariadb.service 
[root@foundation53 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
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)]> 

[root@foundation53 ~]# yum install MySQL-python.x86_64 -y

可能还需要yum install gcc
[root@foundation53 ~]# pip install MySql-Python


完成效果为 PyCharm 可以导入MySQLdb包
import MySQLdb
首先创建python   ---userInfo        内写如下
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.37 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,'jerry');
Query OK, 1 row affected (0.36 sec)

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

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

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

MariaDB [python]> exit







[root@foundation53 Desktop]# ls
0725mysql  phpMyAdmin-3.4.0-all-languages
722        phpMyAdmin-3.4.0-all-languages.tar.bz2
724        pycharm-community-2016.3.2
day05      pycharm-community-2016.3.2.tar.gz
day06      王波.exam
ipython
[root@foundation53 Desktop]# mv phpMyAdmin-3.4.0-all-languages.tar.bz2 /var/www/html/
[root@foundation53 Desktop]# cd /var/www/html/
[root@foundation53 html]# ls
phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@foundation53 html]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 
[root@foundation53 html]# ls
phpMyAdmin-3.4.0-all-languages  phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@foundation53 html]# rm -rf *.bz2
[root@foundation53 html]# mv phpMyAdmin-3.4.0-all-languages/
Display all 105 possibilities? (y or n)
[root@foundation53 html]# mv phpMyAdmin-3.4.0-all-languages/ mysqlamdin
[root@foundation53 html]# ls
mysqlamdin
[root@foundation53 html]# cd mysqlamdin/
[root@foundation53 mysqlamdin]# cp config.sample.inc.php config.sample.inc.php 
cp: ‘config.sample.inc.php’ and ‘config.sample.inc.php’ are the same file
[root@foundation53 mysqlamdin]# cp config.sample.inc.php config.inc.php 
[root@foundation53 mysqlamdin]# cd ..
[root@foundation53 html]# ls
mysqlamdin

[root@foundation53 html]# ll
total 8
drwxr-xr-x 10 root root 4096 Jul 25 09:46 mysqlamdin
[root@foundation53 html]# yum search php
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-
              : manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
=============================== N/S matched: php ===============================
php.x86_64 : PHP scripting language for creating dynamic web sites
php-cli.x86_64 : Command-line interface for PHP
php-common.x86_64 : Common files for PHP
php-gd.x86_64 : A module for PHP applications for using the gd graphics library
php-ldap.x86_64 : A module for PHP applications that use LDAP
php-mysql.x86_64 : A module for PHP applications that use MySQL databases
php-odbc.x86_64 : A module for PHP applications that use ODBC databases
php-pdo.x86_64 : A database access abstraction module for PHP applications
php-pear.noarch : PHP Extension and Application Repository framework
php-pgsql.x86_64 : A PostgreSQL database module for PHP
php-process.x86_64 : Modules for PHP script using system process interfaces
php-recode.x86_64 : A module for PHP applications for using the recode library
php-soap.x86_64 : A module for PHP applications that use the SOAP protocol
php-xml.x86_64 : A module for PHP applications which use XML
php-xmlrpc.x86_64 : A module for PHP applications which use the XML-RPC protocol
php-pecl-memcache.x86_64 : Extension to work with the Memcached caching daemon

  Name and summary matches only, use "search all" for everything.
[root@foundation53 html]# yum install php-mysql.x86_64
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-
              : manager
This system is not registered to Red Hat Subscription Management. You can use subscription-manager to register.
Resolving Dependencies
--> Running transaction check
---> Package php-mysql.x86_64 0:5.4.16-42.el7 will be installed
--> Processing Dependency: php-pdo(x86-64) = 5.4.16-42.el7 for package: php-mysql-5.4.16-42.el7.x86_64
--> Running transaction check
---> Package php-pdo.x86_64 0:5.4.16-42.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================
 Package           Arch           Version                  Repository      Size
================================================================================
Installing:
 php-mysql         x86_64         5.4.16-42.el7            my_yum         101 k
Installing for dependencies:
 php-pdo           x86_64         5.4.16-42.el7            my_yum          98 k

Transaction Summary
================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 199 k
Installed size: 424 k
Is this ok [y/d/N]: y
Downloading packages:
--------------------------------------------------------------------------------
Total                                               47 MB/s | 199 kB  00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : php-pdo-5.4.16-42.el7.x86_64                                 1/2 
  Installing : php-mysql-5.4.16-42.el7.x86_64                               2/2 
  Verifying  : php-mysql-5.4.16-42.el7.x86_64                               1/2 
  Verifying  : php-pdo-5.4.16-42.el7.x86_64                                 2/2 

Installed:
  php-mysql.x86_64 0:5.4.16-42.el7                                              

Dependency Installed:
  php-pdo.x86_64 0:5.4.16-42.el7                                                

Complete!
[root@foundation53 html]# systemctl restart httpd

# _*_ coding:utf-8 _*_              
"""
file: 01.py
date: 2018-07_25 10:27 AM
desc:
如果用不了就是包没导入正确，,host='127.0.0.1'也可试下host='localhost'
"""
import MySQLdb
# 打开门
conn = MySQLdb.connect(host='localhost',user='root',passwd='westos',db='python')
# 伸出手
cur = conn.cursor()
# 拿东西
recont = cur.execute('select * from userInfo')
# 把手伸回来
cur.close()
# 把门关上
conn.close()
print recont

# _*_ coding:utf-8 _*_              
"""
file: 02.py
date: 2018-07_25 10:37 AM
desc:
"""
import MySQLdb
# 打开门
conn = MySQLdb.connect(host='localhost',user='root',passwd='westos',db='python')
# 伸出手
cur = conn.cursor() # 创建了一个“手”
# 拿东西
#这个操作影响了多少行数（有多少行被操作了）
recont = cur.execute('select * from userInfo')
data = cur.fetchall()
# 把手伸回来
cur.close()
# 把门关上
conn.close()
print recont
print data

# _*_ coding:utf-8 _*_              
"""
file: 03.py
date: 2018-07_25 10:48 AM
desc:
查
"""
import MySQLdb
# 打开门
conn = MySQLdb.connect(host='localhost',user='root',passwd='westos',db='python')
# 伸出手
# cur = conn.cursor() # 创建了一个“手”
cur = conn.cursor(cursorclass = MySQLdb.cursors.DictCursor)
# 拿东西
#这个操作影响了多少行数（有多少行被操作了）
recont = cur.execute('select * from userInfo')
data = cur.fetchall()
# 把手伸回来
cur.close()
# 把门关上
conn.close()
print recont
print data

# _*_ coding:utf-8 _*_              
"""
file: 增.py
date: 2018-07_25 11:07 AM
desc:
"""
import MySQLdb
conn = MySQLdb.connect(host='localhost',user='root',passwd='westos',db='python')
# 伸出手
cur =conn.cursor()
# 操作数据 
sql = 'insert into hello(id,name,address) values(%s,%s,%s)'
params = ('1','dd','usa')
recount = cur.execute(sql,params)
# 提交请求
conn.commit()
# 把手伸回来
cur.close()
# 把门关上
conn.close()
print recount

# _*_ coding:utf-8 _*_              
"""
file: 删.py
date: 2018-07_25 11:35 AM
desc:
通过php图形界面创建一个hello表，内有id，name，adress，
    1   id          int(10)
    2   name        varchar(30)
    3   address     varchar(50)
"""
import MySQLdb
conn = MySQLdb.connect(host='localhost',user='root',passwd='westos',db='python')
# 伸出手
cur =conn.cursor()
# 操作数据
sql = 'delete from hello where id = %s'
params = (1,)
recount = cur.execute(sql,params)
# 提交请求
conn.commit()
# 把手伸回来
cur.close()
# 把门关上
conn.close()
print recount

# _*_ coding:utf-8 _*_              
"""
file: 改.py
date: 2018-07_25 11:37 AM
desc:
"""
import MySQLdb
conn = MySQLdb.connect(host='localhost',user='root',passwd='westos',db='python')
# 伸出手
cur =conn.cursor()
# 操作数据
sql = 'update hello set name = %s where id = %s'

params = ('uu','1',)
recount = cur.execute(sql,params)
# 提交请求
conn.commit()
# 把手伸回来
cur.close()
# 把门关上
conn.close()
print recount

# _*_ coding:utf-8 _*_              
"""
file: 插入多行数据.py
date: 2018-07_25 12:56 PM
desc:
"""
import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',user='root',passwd='westos',db='python')
# 伸出手
cur =conn.cursor()
# 操作数据
# sql = 'insert into hello(id,name,address) values(%s,%s,%s)'
# params = ('1','dd','usa')
li = [
    ('2','haha','asd'),
    ('3','lalala','qwe'),
]
recount = cur.executemany('insert into hello(id,name,address) values(%s,%s,%s)',li)
# 提交请求
conn.commit()
# 把手伸回来
cur.close()
# 把门关上
conn.close()
print recount

# _*_ coding:utf-8 _*_              
"""
file: commit.py
date: 2018-07_25 1:55 PM
desc:
提交和回滚，在数据库里交事物操作
"""
import MySQLdb
conn = MySQLdb.connect(host='localhost',user='root',passwd='westos',db='python')
cur = conn.cursor()
sql ='update money set name =%s where id = 1'
params =('100',)
recount = cur.execute(sql,params)
sql ='update money set name =%s where id = 2'
params =('0',)
recount = cur.execute(sql,params)
conn.commit()