python 操作mysql 数据库

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. 
   
7. import MySQLdb
   
8. 
   
9. def connect_mysql():
   
10.     db_config = {
    
11.         "host": "10.89.1.10",
    
12.         "port": 3306,
    
13.         "user": "demo",
    
14.         "passwd": "123qaz",
    
15.         "db": "python",
    
16.         "charset": "utf8"
    
17.     }
    
18.     try:
    
19.         cnx = MySQLdb.connect(**db_config)
    
20.     except Exception as e:
    
21.         raise e
    
22.     return cnx
    
23. 
    
24. 
    
25. if __name__ == '__main__':
    
26.     sql = '''create table test(id int not null); insert into test(id) values(100);'''
    
27.     cnx = connect_mysql()
    
28.     cus = cnx.cursor()
    
29.     # print "ok"
    
30.     try:
    
31.         cus.execute(sql)
    
32.         cus.close()
    
33.         cnx.commit()
    
34.     except Exception as e:
    
35.         raise e
    
36.         cnx.rollback()
    
37.     finally:
    
38.         cnx.close()

执行结果：