Python里面操作MySQL可以通过两个方式:
-
pymysql模块
-
ORM框架的SQLAchemey
本节先学习第一种方式。
学习Python模块之前，首先看看MySQL的基本安装和使用，具体语法可以参考豆子之前的博客http://beanxyz.blog.51cto.com/5570417/1609972
或者官方简介
https://mariadb.com/kb/en/mariadb/basic-sql-statements/
简单的回顾一下基本环境的搭建：
首先安装Mariadb（我的环境是CentOS7）
| 1 2 |
yum install mariadb*
systemctl start mariadb
|
配置防火墙
| 1 2 |
firewall-cmd --add-port=3306/tcp --permanent
systemctl restart firewalld
|
配置root密码
| 1 2 |
mysqladmin -u root password 'mysql'
mysql -uroot -p
|
创建一个测试用的数据库和表
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
MariaDB [(none)]> create database mydb;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use mydb
Database changed
MariaDB [mydb]> create table student(id int not null auto_increment,name varchar(10), primary key(id));
Query OK, 0 rows affected (0.04 sec)
MariaDB [mydb]> insert into student(name) values('Jay'),('Bob'),('Alex');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [mydb]> select * from student;
+----+------+
| id | name |
+----+------+
| 1 | Jay |
| 2 | Bob |
| 3 | Alex |
+----+------+
3 rows in set (0.00 sec)
|
创建一个远程访问的账户
| 1 2 3 4 5 6 |
MariaDB [(none)]> create user [email protected];
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> set password for [email protected]'10.2.100.60'=password('yli');
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> grant all privileges on mydb.* to [email protected];
Query OK, 0 rows affected (0.00 sec)
|
然后安装一个图形界面的工具Navicat,绑定数据库
这样一个基本的测试环境就搭建好了。
现在来看看pymysql的使用。
在我的客户端安装一下pymysql的模块
| 1 2 3 4 5 6 |
C:\WINDOWS\system32>pip install pymysql
Collecting pymysql
Downloading PyMySQL-0.7.9-py3-none-any.whl (78kB)
100% |################################| 81kB 610kB/s
Installing collected packages: pymysql
Successfully installed pymysql-0.7.9
|
Python源码演示
查询
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
#打开数据库连接
conn = pymysql.connect(host='sydnagios', port=3306, user='yli', passwd='yli', db='mydb')
#创建一个游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
#SQL查询
cursor.execute("select * from student")
# 获取第一行数据
# row_1 = cursor.fetchone()
# print(row_1)
# 获取前n行数据
# row_2 = cursor.fetchmany(3)
# 获取所有数据
row_3 = cursor.fetchall()
print(row_3)
#scroll可以使用相对位置或者绝对位置，这里相对位置（末尾）向上移动2行
cursor.scroll(-2,mode='relative')
row_3 = cursor.fetchall()
print(row_3)
#提交，不然无法保存新的数据
conn.commit()
#关闭游标
cursor.close()
#关闭连接
conn.close()
-----------
[{'id': 1, 'name': 'Jay'}, {'id': 2, 'name': 'Bob'}, {'id': 3, 'name': 'Alex'}]
[{'id': 2, 'name': 'Bob'}, {'id': 3, 'name': 'Alex'}]
|
修改
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='sydnagios', port=3306, user='yli', passwd='yli', db='mydb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute("Update student set name='BoB' where id=2")
cursor.execute("select * from student")
row_3 = cursor.fetchall()
print(row_3)
conn.commit()
cursor.close()
conn.close()
----------
[{'id': 1, 'name': 'Chris'}, {'id': 2, 'name': 'BoB'}, {'id': 3, 'name': 'Alex'}]
|
删除
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='sydnagios', port=3306, user='yli', passwd='yli', db='mydb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute("delete from student where id=2")
cursor.execute("select * from student")
row_3 = cursor.fetchall()
print(row_3)
conn.commit()
cursor.close()
conn.close()
----------
[{'id': 1, 'name': 'Chris'}, {'id': 2, 'name': 'BoB'}, {'id': 3, 'name': 'Alex'}]
|
添加
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='sydnagios', port=3306, user='yli', passwd='yli', db='mydb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute("insert into student(name) value ('ZhangSan'),('LiSi')")
cursor.execute("select * from student")
row_3 = cursor.fetchall()
print(row_3)
conn.commit()
cursor.close()
conn.close()
----------
[{'name': 'Chris', 'id': 1}, {'name': 'Alex', 'id': 3}, {'name': 'ZhangSan', 'id': 4}, {'name': 'LiSi', 'id': 5}]
|
登录乐搏学院官网http://www.learnbo.com/
或关注我们的官方微博微信，还有更多惊喜哦~
本文出自 “麻婆豆腐” 博客，请务必保留此出处http://beanxyz.blog.51cto.com/5570417/1871039
