运维人

python链接mysql数据库的一些操作

一、实验规划

1、首先实验之前,我们需要提前创建一个数据库TESTDB,然后授一个用户test管理该数据库,这里我设置的密码是test123;

2、要用python链接数据库需要用到第三方法模块MySQLdb;

3、利用python脚本对数据库做一些简单操作;

二、安装第三方模块MySQLdb.

1、获取模块

wget https://pypi.python.org/packages/source/M/MySQL-python/MySQL-python-1.2.5.zip

2、解压,安装

unzip MySQL-python-1.2.5.zip
cd MySQL-python-1.2.5
python setup.py build
python setup.py install

3、检查安装情况,进入python命令行执行 import,如果不报出任何信息说明成功安装并且导入成功

demo@demo:~$ python
Python 2.7.9 (default, Apr  2 2015, 15:33:21) 
[GCC 4.9.2] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
>>>

三、对mysql数据库的一些操作

1、连接数据库TESTDB并查看数据库的版本

#!/usr/bin/env python
#-*- coding: UTF-8 -*-
import MySQLdb

db = MySQLdb.connect("localhost","testuser","test123","TESTDB")

cursor = db.cursor()

cursor.execute("SELECT VERSION()")

data = cursor.fetchone()

print "Database version: %s " % data

db.close()

执行结果如下:

demo@demo:~/python_learn$ python mysql_connect.py 
Database version: 5.6.25-0ubuntu0.15.04.1 
demo@demo:~/python_learn$ 

2、链接到数据库TESTDB并且创建一个张表

#!/usr/bin/env python
#-*- coding: UTF-8 -*-
import MySQLdb

db = MySQLdb.connect("localhost","testuser","test123","TESTDB")

cursor = db.cursor()

cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

sql = """CREATE TABLE `EMPLOYEE` (
  `FIRST_NAME` varchar(20) DEFAULT NULL,
  `LAST_NAME` varchar(20) DEFAULT NULL,
  `AGE` int(11) DEFAULT NULL,
  `SEX` varchar(20) DEFAULT NULL,
  `INCOME` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 """

cursor.execute(sql)

db.close()

执行结果我们通过链接到数据库去查看

mysql> use TESTDB;
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_TESTDB |
+------------------+
| EMPLOYEE         |
+------------------+
1 row in set (0.00 sec)

mysql> 
mysql> DESC EMPLOYEE;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| FIRST_NAME | varchar(20) | YES  |     | NULL    |       |
| LAST_NAME  | varchar(20) | YES  |     | NULL    |       |
| AGE        | int(11)     | YES  |     | NULL    |       |
| SEX        | varchar(20) | YES  |     | NULL    |       |
| INCOME     | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> select * from EMPLOYEE;   #目前该表没有任何数据
Empty set (0.00 sec)

mysql>

3、连接到数据库,并在EMPLOYEE表中插入数据

#!/usr/bin/env python
#-*- coding: UTF-8 -*-
import MySQLdb

db = MySQLdb.connect("localhost","testuser","test123","TESTDB")

cursor = db.cursor()

sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
	 LAST_NAME,AGE,SEX,INCOME)
	 VALUES ('Guo','Maoqiuo','24','M','1991')"""

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

db.close()

执行结果如下

mysql> select * from EMPLOYEE;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE  | SEX  | INCOME |
+------------+-----------+------+------+--------+
| Guo        | Maoqiuo   |   24 | M    | 1991   |
+------------+-----------+------+------+--------+
1 row in set (0.00 sec)

mysql> 

4、链接数据库使用select语句进行查询,这里实验使用条件查询,那我需要多一点数据才行,那我修改上面的那个脚本然后多执行几遍即可,看到的结果如下

mysql> select * from EMPLOYEE;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE  | SEX  | INCOME |
+------------+-----------+------+------+--------+
| Guo        | Maoqiuo   |   24 | M    | 1991   |
| Bruce      | Li        |   25 | M    | 1989   |
| Fan        | bingbing  |   28 | F    | 1988   |
| Jack       | cheng     |   45 | M    | 1968   |
| joy        | jiang     |   26 | F    | 1977   |
+------------+-----------+------+------+--------+
5 rows in set (0.00 sec)

使用条件查询

#!/usr/bin/env python
#-*- coding: UTF-8 -*-
import MySQLdb

db = MySQLdb.connect("localhost","testuser","test123","TESTDB")

cursor = db.cursor()

sql = "SELECT * FROM EMPLOYEE WHERE INCOME > '%d'" % (1988)

try:
    cursor.execute(sql)
    results = cursor.fetchall()

    for row in results:
        fname = row[0]
        lname = row[1]
        age = row[2]
        sex = row[3]
        income = row[4]
        print "Fname=%s, Lanme=%s, Age=%d, Sex=%s, Income=%s\n"  % (fname,lname,age,sex,income)
except:
    print "Error: unable to fecth data"

db.close()

执行结果如下

deamon@deamon:~/python_learn$ python mysql_select.py 
Fname=Guo, Lanme=Maoqiuo, Age=24, Sex=M, Income=1991

Fname=Bruce, Lanme=Li, Age=25, Sex=M, Income=1989


4、连接到数据库,并更新在EMPLOYEE表的数据,将性别为M的人员在年龄上加2

#!/usr/bin/env python
#-*- coding: UTF-8 -*-
import MySQLdb

db = MySQLdb.connect("localhost","testuser","test123","TESTDB")

cursor = db.cursor()

sql = "UPDATE EMPLOYEE SET AGE = AGE +2  WHERE  SEX = '%c' " % ('M')

try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
    print "Error"

db.close()

执行后的结果

mysql> select * from TESTDB.EMPLOYEE;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE  | SEX  | INCOME |
+------------+-----------+------+------+--------+
| Guo        | Maoqiuo   |   26 | M    | 1991   |
| Bruce      | Li        |   27 | M    | 1989   |
| Fan        | bingbing  |   28 | F    | 1988   |
| Jack       | cheng     |   47 | M    | 1968   |
| joy        | jiang     |   26 | F    | 1977   |
+------------+-----------+------+------+--------+
5 rows in set (0.00 sec)

mysql>
    分享到:
码字很辛苦,转载请注明来自运维人《python链接mysql数据库的一些操作》

评论