调用python中的mysql存储过程并将结果写入csv文件

Question

我是python的新手，我试图在python中定义一个函数，该函数使用mysql.connector调用python中的存储过程，并将结果写入csv文件。我定义了一个函数来调用存储过程并打印结果。但不知道如何将我的结果写入csv文件而不是打印。尝试了一些在网上找到的方法，但没有带我去任何地方

到目前为止，我的代码是：

def call_accessionsByaffliction():
    try:
        conn = mysql.connector.connect( host="localhost", user="root", 
password="1232580", database="db")
        cur = conn.cursor()
        cur.callproc('AccessionsBycauseOfAffliction_year')
        for result in cur.stored_results():
            print(result.fetchall())
    except Error as e:
        print(e)
    finally:
        cur.close()
        conn.close() 

和

if __name__ == '__main__':
    call_accessionsByaffliction()

有什么帮助吗？非常感谢

Answer 1

import csv

def cs_writer(string_chunk):
    # define desired output file name      
    file_name = "output.csv"
    try:
        #open file in write mode and hold object 
        out_file = open(file_name, "wt")

        # create csv write object
        writer = csv.writer(out_file, delimiter=',', quotechar='"', quoting=csv.QUOTE_ALL)
        for line in string_chunk:
            writer.writerow(line)
    except Exception as e:
        print(e)
    finally:
        # close file
        out_file.close()

def call_accessionsByaffliction():
    try:
        conn = mysql.connector.connect( host="localhost", user="root", password="1232580", database="db")
        cur = conn.cursor()

        # execute stored procedure
        cur.callproc('AccessionsBycauseOfAffliction_year')

        # fetch all results or use fetchone()
        for res in cur.stored_results():
            result = res.fetchall()

        print(result)
        return result
    except Error as e:
        print(e)
    finally:
        cur.close()
        conn.close() 

if __name__ == '__main__':
    cs_writer(call_accessionsByaffliction())

我希望这能回答你的问题。

Answer 2

如果您正在处理数据，我强烈建议您查看Pandas。另外，对于这样的论坛，最好省略__main__检查。下面是一段未经测试的代码，它完成了您在Pandas中要做的事情。

import pandas

connection_string = "'mysql://root:1232580@localhost/db'"
df = pandas.read_sql_query('EXEC AccessionsBycauseOfAffliction_year', connection_string)
df.to_csv('AccessionsBycauseOfAffliction_year.csv')

Answer 3

如果您能够找到SP，对于调用您可以使用下面的代码请提供更多的格式，您希望代码只存储在csv中的结果？？

from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config


    def call_find_by_isbn():
        try:
            db_config = read_db_config()
            conn = MySQLConnection(**db_config)
            cursor = conn.cursor()

            args = ['1236400967773', 0]
            result_args = cursor.callproc('AccessionsBycauseOfAffliction_year', args)

            print(result_args[1])

        except Error as e:
            print(e)

        finally:
            cursor.close()
            conn.close()

    if __name__ == '__main__':
        call_find_by_isbn()