Chinaunix首页 | 论坛 | 博客
  • 博客访问: 229775
  • 博文数量: 57
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 557
  • 用 户 组: 普通用户
  • 注册时间: 2015-10-01 18:05
文章分类

全部博文(57)

文章存档

2017年(57)

我的朋友

分类: Python/Ruby

2017-11-21 08:59:13

python 操作mysql 数据库,一般需要授权其他机器访问(数据库和客户机不在同一台机器上)
1.授权
mysql> select user,host from mysql.user;
+------+-----------+
| user | host      |
+------+-----------+
| root | 127.0.0.1 |
| demo | localhost |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)


mysql> grant select on db.table1 to demo@'10.89.3.%' identified by '123qaz';
ERROR 1146 (42S02): Table 'db.table1' doesn't exist
mysql> grant all privileges on *.* to demo@'10.89.3.%' identified by '123qaz';         
Query OK, 0 rows affected (0.00 sec)


mysql> select user,host from mysql.user;                                    
+------+-----------+
| user | host      |
+------+-----------+
| demo | 10.89.3.% |
| root | 127.0.0.1 |
| demo | localhost |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)
授权 10.89.3.x  这个网段的机器连接
2. 创建python数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.04 sec)


mysql> create database python;
Query OK, 1 row affected (0.00 sec)


mysql> show databases;        
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| python             |
| test               |
+--------------------+
5 rows in set (0.00 sec)

3.编写代码连接并操作

点击(此处)折叠或打开

  1. #!/usr/bin/env python
  2. # -*- coding:utf-8 -*-
  3. # Author :Alvin.xie
  4. # @Time :2017-11-20 14:34
  5. # @file :mysqltest.py

  6. import MySQLdb

  7. def connect_mysql():
  8.     db_config = {
  9.         "host": "10.89.1.10",
  10.         "port": 3306,
  11.         "user": "demo",
  12.         "passwd": "123qaz",
  13.         "db": "python",
  14.         "charset": "utf8"
  15.     }
  16.     try:
  17.         cnx = MySQLdb.connect(**db_config)
  18.     except Exception as e:
  19.         raise e
  20.     return cnx


  21. if __name__ == '__main__':
  22.     sql = '''create table test(id int not null); insert into test(id) values(100);'''
  23.     cnx = connect_mysql()
  24.     cus = cnx.cursor()
  25.     # print "ok"
  26.     try:
  27.         cus.execute(sql)
  28.         cus.close()
  29.         cnx.commit()
  30.     except Exception as e:
  31.         raise e
  32.         cnx.rollback()
  33.     finally:
  34.         cnx.close()
执行结果:


阅读(1485) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~