1

I am trying to import mysql data into a .txt file using python 3.x but it look like I'm missing something.The expectation is, data should be imported to a file in tabular/columns format. I tried my level best to get solution but I'm not getting what I need.

Below is my code :

import pymysql.cursors
import pymysql
import sys
import os

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='root',
                             password="",
                             db='jmeterdb',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # Select all records
        sql = "select * from emp"
        cursor.execute(sql)

    # connection is not autocommit by default. So you must commit to save
    # your changes.
    result = cursor.fetchall()
    newfile = open("db-data.txt","a+")
    for row in result:
        newfile.writelines(row)

    print(result)
    newfile.close()

finally:
    connection.close()

On terminal python shows me data when print(result) is executed but in the db-data.txt file, it shows column-names only.

Expected result :

Column_Name1 Column_Name2 Column_Name3
data1        data2        data3
data1        data2        data3
Sohil Shivani
  • 336
  • 4
  • 19
  • 1
    `writelines()` assumes there is an array of lines, you have to use `write(row + "\n")` – Michael Apr 26 '17 at 10:54
  • 1
    See http://stackoverflow.com/questions/12377473/python-write-versus-writelines-and-concatenated-strings Use write instead of writelines. – Gustav Rasmussen Apr 26 '17 at 11:01
  • I just got the table structure by using this code : `for row in result: newfile.write("\t".join(row))` but no data from sql. – Sohil Shivani Apr 26 '17 at 11:14

1 Answers1

0

This code is producing expected output for above question is as below :

import pymysql.cursors
import pymysql
import sys
import os

# Open database connection
connection = pymysql.connect(host='localhost',
                             user='root',
                             password="",
                             db='jmeterdb',
                             cursorclass=pymysql.cursors.DictCursor)
# prepare a cursor object using cursor() method
with connection.cursor() as cursor:
# Prepare SQL query to select a record into the database.
    try:

        sql = "SELECT * FROM EMP order by ename asc"
# Execute the SQL command
        cursor.execute(sql)
# Fetch all the rows in a list of lists.
        results = cursor.fetchall()
        # print(results)
        if results:
            newfile = open("db-data.txt","a+")
            newfile.write('ename'+"\t"+'jobs'+"\t"+"salary"+"\t"+'comm'+"\t"+'manager'+"\t"+'hiredate'+"\t"+'deptno'+"\t"+'empno'+"\n")

        for index in results:
            ltr=[]
            ltr.append(index['ename'])
            ltr.append(index['job'])
            ltr.append(index['sal'])
            ltr.append(index['comm'])
            ltr.append(index['mgr'])
            ltr.append(index['hiredate'])
            ltr.append(index['deptno'])
            ltr.append(index['empno'])
            lenltr=len(ltr)
            for i in range(lenltr):
                newfile.write('{}'.format(ltr[i]))
                newfile.write("\t")
                print(ltr[i])
            newfile.write("\n")


# # Now print fetched result
        #print("ename=%s,empno=%d,job=%d,hiredate=%d,comm=%s,sal=%d,deptno=%d,mgr=%d" %(ename, empno, job, hiredate, comm, sal, deptno, mgr))
        # print(index)
    except:
        print ("Error: unable to fecth data")
# disconnect from server
connection.close()
newfile.close()
Sohil Shivani
  • 336
  • 4
  • 19