Python - 将 MySQL 选择查询结果导出到 CSV

Question

我正在尝试将 MySQL 选择查询结果导出到 CSV 文件。

我为此使用 lambda，我的数据库位于 RDS 中。

我可以连接并运行查询。

但是当我尝试以 CSV 格式保存选择查询结果时，它不起作用。

需要 python 开发人员帮助编写此脚本。

注意:Unicodewriter 不适用于 lambda。

Lambda 函数:

import sys
import logging
import rds_config
import pymysql
#rds settings
rds_host  = "connection_link"
name = rds_config.db_username
password = rds_config.db_password
db_name = rds_config.db_name


logger = logging.getLogger()
logger.setLevel(logging.INFO)

try:
    conn = pymysql.connect(rds_host, user=name, passwd=password, db=db_name, connect_timeout=5)
except:
    logger.error("ERROR: Unexpected error: Could not connect to MySql instance.")
    sys.exit()

logger.info("SUCCESS: Connection to RDS mysql instance succeeded")

def handler(event, context):
    """
    This function fetches content from mysql RDS instance
    """

    item_count = 0

    with conn.cursor() as cur:
        cur.execute("create table Employee3 ( EmpID  int NOT NULL, Name varchar(255) NOT NULL, PRIMARY KEY (EmpID))")  
        cur.execute('insert into Employee3 (EmpID, Name) values(1, "Joe")')
        cur.execute('insert into Employee3 (EmpID, Name) values(2, "Bob")')
        cur.execute('insert into Employee3 (EmpID, Name) values(3, "Mary")')
        conn.commit()
        cur.execute("select * from Employee3")

        for row in cur:
            item_count += 1
            logger.info(row)
            #print(row)

Answer 1

以下标准方法应该可以写入所有行:

with conn.cursor() as cur:
    cur.execute("create table Employee3 ( EmpID  int NOT NULL, Name varchar(255) NOT NULL, PRIMARY KEY (EmpID))")  
    cur.execute('insert into Employee3 (EmpID, Name) values(1, "Joe")')
    cur.execute('insert into Employee3 (EmpID, Name) values(2, "Bob")')
    cur.execute('insert into Employee3 (EmpID, Name) values(3, "Mary")')
    conn.commit()
    cur.execute("select * from Employee3")

    res = cur.fetchall()
    with open('output.csv','w') as fileout:
        writer = csv.writer(fileout)
        writer.writerows(res)        

如果您需要逐行处理行，您可以使用writerow来代替:

    cur.execute("select * from Employee3")

    with open('output.csv','w') as fileout:
        writer = csv.writer(fileout)
        for row in cur:
            writer.writerow(row)