Pymysql基本使用

Pymysql基本使用（1）

调用顺序：
（1）连接数据库
conn = connect(host=‘数据库地址’, port=端口, database=‘数据库名’, user=‘用户’, password=‘密码’,charset=‘utf8’)
（2）获取游标
cur = conn.cursor()
（3）执行SQL语句
row_count = cur.execute(sql_str)
（4）关闭游标
cur.close()
注意：因为在获取数据时，游标是移动的，所以前面取过的数据，后面不会再取了。
（5）关闭数据库
conn.close()

官方代码调用链接

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='user',
                             password='passwd',
                             db='db',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
#此过程执行完会自动关闭游标，所以不用代码再去关闭游标
    with connection.cursor() as cursor:
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('webmaster@python.org', 'very-secret'))

    connection.commit()

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('webmaster@python.org',))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

实践
（1）安装模块pymysql

pip install pymysql

（2）创建person表

  CREATE TABLE `person` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) COLLATE utf8_bin NOT NULL,
  `age` tinyint(3) unsigned DEFAULT '0',
  `gender` set('男','女') COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin 

mysql> select * from person;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 张三   |   33 | 女     |
|  2 | 李四   |   34 | 男     |
|  3 | 王五   |   24 | 女     |
+----+--------+------+--------+
3 rows in set (0.00 sec)

（3）连接查询操作

import pymysql
# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='Admin@123456',
                             db='pymysqldatabase',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT * FROM `person` WHERE `id`=%s"
        cursor.execute(sql, ('1'))
        result = cursor.fetchone()
        #对应fetchone，fetchall，fetchmany使用方法
        print(result)
finally:
    connection.close()

执行结果

(py3) [root@yunwei_ceshi pymysql]#python pymysqltest1.py 
{'id': 1, 'name': '张三', 'age': 33, 'gender': '女'}

（4）保存数据

import pymysql
# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='Admin@123456',
                             db='pymysqldatabase',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Read a single record
        # Create a new record
        sql = "INSERT INTO `person` (`id`, `name`,`age`,`gender`) VALUES (%s,%s,%s,%s)"
        cursor.execute(sql, ('4','赵六','72','男'))

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    connection.commit()
        		
finally:
    connection.close()

执行结果

mysql> select * from person;
+----+--------+------+--------+
| id | name   | age  | gender |
+----+--------+------+--------+
|  1 | 张三   |   33 | 女     |
|  2 | 李四   |   34 | 男     |
|  3 | 王五   |   24 | 女     |
|  4 | 赵六   |   72 | 男     |
+----+--------+------+--------+
4 rows in set (0.00 sec)

（5）事务回滚

    connection.rollback()