0

im new to python and Im trying to define a function in python that calls a stored procedure in python using mysql.connector and write the result into a csv file. i have defined a function to call the stored procedure and print the result. But couldn't figure out how to write my result into a csv file instead of printing. Tried some methods found in web but didn't take me anywhere

so far my codes are:

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()

and

if __name__ == '__main__':
    call_accessionsByaffliction()

Any help? Much appreciated

Dusha.9
  • 15
  • 2
  • 8

3 Answers3

1

If you're working with data, I strongly recommend looking into Pandas. Also, for forums like this, it's best to leave out __main__ checks. Here's an untested code which does the things you're trying to do in 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')
chthonicdaemon
  • 19,180
  • 2
  • 52
  • 66
1

if you are able to find SP, for calling you can use following code Please give more formation that you want code only for storing result in 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()
v8-E
  • 1,077
  • 2
  • 14
  • 21
  • yeah. im trying to save the results in a csv file. but still couldnt figure out a way – Dusha.9 Oct 26 '17 at 05:27
  • okay so you are able to find stored procedure only concern is storing result in CSV file will give you code, or you can try solution given by @chthonicdaemon – v8-E Oct 26 '17 at 05:48
  • yes, my stored procedure is "AccessionsBycauseOfAffliction_year()", im looking for the code to store my result set in a csv file. i prefer using same method instead of using pandas. Any help? – Dusha.9 Oct 26 '17 at 05:56
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())

I hope this answers your question.

Mahesh Karia
  • 2,045
  • 1
  • 12
  • 23
  • This is the code i was looking for. but im still getting an error saying: No result set to fetch from. Traceback (most recent call last): File "", line 2, in call_accessionsByaffliction() File "", line 42, in call_accessionsByaffliction for row in result: UnboundLocalError: local variable 'result' referenced before assignment. Any help to fix this? couldnt figure it out – Dusha.9 Oct 26 '17 at 04:37
  • @Dusha.9 I have made some changes in code can you try again ? – Mahesh Karia Oct 26 '17 at 04:39
  • im still getting an error message saying: Traceback (most recent call last): File "", line 2, in call_accessionsByaffliction() File "", line 25, in call_accessionsByaffliction writer.writerow(row) TypeError: 'str' does not support the buffer interface – Dusha.9 Oct 26 '17 at 04:43
  • Or instead of writing the csv file inside the "call_accessionsByaffliction()" function, can we write the result into a csv file after executing the function? any help? much appreciated – Dusha.9 Oct 26 '17 at 05:26
  • can you confirm if call_accessionsByafflication() is returning chunk of string and pass it to cs_writer() – Mahesh Karia Oct 26 '17 at 05:36
  • this is the result set i got when i printed my results: [('Fell out of tree', datetime.date(2009, 4, 23), 1), ('Orphaned/Dependent Young', datetime.date(2009, 8, 10), 11), ('Surrendered/confiscated', datetime.date(2009, 5, 28), 28)] – Dusha.9 Oct 26 '17 at 05:40
  • can you also check main() if cs_writer() is being called or not? I see csv generation and data population. – Mahesh Karia Oct 26 '17 at 06:07
  • Thanks heaps. this is working. and one more question, can i have headers in my csv file – Dusha.9 Oct 26 '17 at 06:43
  • yes you can refer https://stackoverflow.com/questions/5010042/mysql-get-column-name-or-alias-from-query and get header and append it to result[0] location – Mahesh Karia Oct 26 '17 at 07:02