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.编写代码连接并操作

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

import MySQLdb

def connect_mysql():
    db_config = {
        "host": "10.89.1.10",
        "port": 3306,
        "user": "demo",
        "passwd": "123qaz",
        "db": "python",
        "charset": "utf8"
    }
    try:
        cnx = MySQLdb.connect(**db_config)
    except Exception as e:
        raise e
    return cnx


if __name__ == '__main__':
    sql = '''create table test(id int not null); insert into test(id) values(100);'''
    cnx = connect_mysql()
    cus = cnx.cursor()
    # print "ok"
    try:
        cus.execute(sql)
        cus.close()
        cnx.commit()
    except Exception as e:
        raise e
        cnx.rollback()
    finally:
        cnx.close()

执行结果：