提问者:小点点

如何将mysql数据导入到。txt文件使用python 3.5?


我试图导入mysql数据到一个. txt文件使用python 3. x,但它看起来像我错过了什么。期望数据应该以表格/列格式导入到文件中。我尽了最大努力得到解决方案,但我没有得到我需要的。

下面是我的代码:

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

在终端上,python在执行print(result)时向我显示数据,但在db数据中。txt文件,仅显示列名。

预期结果:

Column_Name1 Column_Name2 Column_Name3
data1        data2        data3
data1        data2        data3

共1个答案

匿名用户

此代码为上述问题生成预期输出,如下所示:

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