2

【丢弃】【Python】pymysql

 3 years ago
source link: https://www.guofei.site/2017/08/23/pymysql.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

【丢弃】【Python】pymysql

2017年08月23日

Author: Guofei

文章归类: ，文章编号:

版权声明：本文作者是郭飞。转载随意，但需要标明原文链接，并通知本人
原文链接：https://www.guofei.site/2017/08/23/pymysql.html

Edit

回收原因：sqlalchemy对sqlite3和pymysql进行了良好的封装，因此在操作sql时，无需再过多关注底层的sqlite3和pymysql了

pymysql是用Python调用MySQL的包

另一篇文章中讲了pysqlite的操作，pymysql的用法也类似。

连接数据库

import pymysql.cursors

config = {
          'host':'127.0.0.1',
          'port':3306,
          'user':'root',
          'password':'zhyea.com',
          'db':'employees',
          'charset':'utf8mb4',
          'cursorclass':pymysql.cursors.DictCursor,
          }

# Connect to the database
connection = pymysql.connect(**config)

插入数据

from datetime import date, datetime, timedelta
# 获取明天的时间
tomorrow = datetime.now().date() + timedelta(days=1)

# 执行sql语句
try:
    with connection.cursor() as cursor:
        # 执行sql语句，插入记录
        sql = 'INSERT INTO employees (first_name, last_name, hire_date, gender, birth_date) VALUES (%s, %s, %s, %s, %s)'
        cursor.execute(sql, ('Robin', 'Zhyea', tomorrow, 'M', date(1989, 6, 14)));
    # 没有设置默认自动提交，需要主动提交，以保存所执行的语句
    connection.commit()

finally:
    connection.close();

查询

# 获取雇佣日期
hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(2016, 12, 31)

# 执行sql语句
try:
    with connection.cursor() as cursor:
        # 执行sql语句，进行查询
        sql = 'SELECT first_name, last_name, hire_date FROM employees WHERE hire_date BETWEEN %s AND %s'
        cursor.execute(sql, (hire_start, hire_end))
        # 获取查询结果
        result = cursor.fetchone()#dict
        #result = cursor.fetchmany(2)
        #result = cursor.fetchall()#list
        print(result)
    # 没有设置默认自动提交，需要主动提交，以保存所执行的语句
    connection.commit()

finally:
    connection.close();

其它

pymysql有个小问题，字段顺序是乱的。
还有mysql官方的Python包：mysql-connector-python，用处差不多，

您的支持将鼓励我继续创作！

report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK