python mysql 分号_python操作mysql

1.安装pymysql

pip install PyMySQL

2.开启记录log，在mysqlserver的my_cnf中添加如下

general_log=1

general_log_file=/tmp/log_sql_query.log #此处要指定目录有可写权限，如果指定目录无可写权限，则会写入失败

重启mysql服务

systemctl restart mysqld

3.创建表

create database test #创建test库

CREATE TABLE `users` ( #创建users表

`id` int(11) NOT NULL AUTO_INCREMENT,

`email` varchar(255) COLLATE utf8_bin NOT NULL,

`password` varchar(255) COLLATE utf8_bin NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

AUTO_INCREMENT=1 ;

4.CRUD代码如下

#!/usr/bin/env python

# -*- encoding:utf-8 -*-

import pymysql.cursors

# Connect to the database

connection = pymysql.connect(host='192.168.152.137',

user='root',

password='MyNewPass4!',

db='test',

charset='utf8mb4',

cursorclass=pymysql.cursors.DictCursor)

try:

# 插入4条数据

with connection.cursor() as cursor:

# Create a new record

sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"

cursor.execute(sql, ('webmaster@python.org', u'中华人民共和国'))

sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"

cursor.execute(sql, ('webmaster@python.org', 'Owen is a good man!'))

sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"

cursor.execute(sql, ('webmaster@python.org', u'丽江，大丽，我想要去的地方'))

sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"

cursor.execute(sql, ('webmaster@python.org', u'Hello,World!'))

connection.commit()

# 打印出所有添加的数据

with connection.cursor() as cursor:

sql = "select id,password from users"

cursor.execute(sql)

result = cursor.fetchall()

print result

# 更新所有数据都为Hello

with connection.cursor() as cursor:

sql = "update users set password = %s;"

cursor.execute(sql, ('Hello',))

connection.commit()

# 打印出所有更新的数据

with connection.cursor() as cursor:

sql = "select id,password from users"

cursor.execute(sql)

result = cursor.fetchall()

print result

# 删除id=1的数据

with connection.cursor() as cursor:

sql = "delete from users where id = %s"

cursor.execute(sql, (1,))

connection.commit()

# 打印出所有更新的数据

with connection.cursor() as cursor:

sql = "select count(1) from users"

cursor.execute(sql)

result = cursor.fetchone()

print result

finally:

connection.close()

5.控制台输出信息

[{u'password': u'\u4e2d\u534e\u4eba\u6c11\u5171\u548c\u56fd', u'id': 1}, {u'password': u'Owen is a good man!', u'id': 2}, {u'password': u'\u4e3d\u6c5f\uff0c\u5927\u4e3d\uff0c\u6211\u60f3\u8981\u53bb\u7684\u5730\u65b9', u'id': 3}, {u'password': u'Hello,World!', u'id': 4}]

[{u'password': u'Hello', u'id': 1}, {u'password': u'Hello', u'id': 2}, {u'password': u'Hello', u'id': 3}, {u'password': u'Hello', u'id': 4}]

{u'count(1)': 3}

6.查看我们执行的sql

cat /tmp/log_sql_query.log # 查看sql log

2016-12-09T13:17:50.161104Z 2 Statistics

2016-12-09T13:18:23.192378Z 2 Query #general_logselect @@global.general_log

use test

2016-12-09T13:18:23.219917Z 2 Query CREATE TABLE `users` ( #创建users表

`id` int(11) NOT NULL AUTO_INCREMENT,

`email` varchar(255) COLLATE utf8_bin NOT NULL,

`password` varchar(255) COLLATE utf8_bin NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

AUTO_INCREMENT=1

2016-12-09T13:18:27.916975Z 2 Query SHOW DATABASES

2016-12-09T13:18:28.965329Z 2 Query SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='test'

2016-12-09T13:18:28.977337Z 2 Query SHOW TABLE STATUS FROM `test`

2016-12-09T13:18:28.989998Z 2 Query SHOW FUNCTION STATUS WHERE `Db`='test'

2016-12-09T13:18:29.008819Z 2 Query SHOW PROCEDURE STATUS WHERE `Db`='test'

2016-12-09T13:18:29.024187Z 2 Query SHOW TRIGGERS FROM `test`

2016-12-09T13:18:29.037922Z 2 Query SELECT *, EVENT_SCHEMA AS `Db`, EVENT_NAME AS `Name` FROM information_schema.`EVENTS` WHERE `EVENT_SCHEMA`='test'

2016-12-09T13:18:43.282916Z 5 Connect root@192.168.152.1 on test using TCP/IP

2016-12-09T13:18:43.283691Z 5 Query SET AUTOCOMMIT = 0

2016-12-09T13:18:43.285891Z 5 Query INSERT INTO `users` (`email`, `password`) VALUES ('webmaster@python.org', '中华人民共和国')

2016-12-09T13:18:43.287053Z 5 Query INSERT INTO `users` (`email`, `password`) VALUES ('webmaster@python.org', 'Owen is a good man!')

2016-12-09T13:18:43.288674Z 5 Query INSERT INTO `users` (`email`, `password`) VALUES ('webmaster@python.org', '丽江，大丽，我想要去的地方')

2016-12-09T13:18:43.289810Z 5 Query INSERT INTO `users` (`email`, `password`) VALUES ('webmaster@python.org', 'Hello,World!')

2016-12-09T13:18:43.291124Z 5 Query COMMIT

2016-12-09T13:18:43.294547Z 5 Query select id,password from users

2016-12-09T13:18:43.297104Z 5 Query update users set password = 'Hello'

2016-12-09T13:18:43.298150Z 5 Query COMMIT

2016-12-09T13:18:43.300400Z 5 Query select id,password from users

2016-12-09T13:18:43.302220Z 5 Query delete from users where id = 1

2016-12-09T13:18:43.303092Z 5 Query COMMIT

2016-12-09T13:18:43.306127Z 5 Query select count(1) from users

2016-12-09T13:18:43.307315Z 5 Quit