代码之家 › 专栏 › 技术社区 › Houy Narun

如何正确写入CSV文件?

csv python

Houy Narun · 技术社区 · 7 年前

为了给出存储在db中的模型及其数据,我想将其写入csv文件。然而,我生成的csv文件的第一行顺序不正确。

型号.py

class BRANCH():
    ID                  =   db.Column(db.String(6), primary_key=True)
    Branch              =   db.Column(db.String(20))
    Status              =   db.Column(db.String(20))
    Curr                =   db.Column(db.String(5))
    Inputter            =   db.Column(db.String(35))
    Createdon           =   db.Column(db.String(20))
    Authorizer          =   db.Column(db.String(35))
    Authorizeon         =   db.Column(db.String(20))
    Description         =   db.Column(db.String(50))
    LocalDescription    =   db.Column(db.String(50))
    BranchManagerName   =   db.Column(db.String(35))
    LocalBranchManagerName = db.Column(db.String(100))
    ContactNumber       =   db.Column(db.String(35))
    Address             =   db.Column(db.String(100))
    LocalAddress        =   db.Column(db.String(100))
    District            =   db.Column(db.String(9))
    Province            =   db.Column(db.String(3))
    ReportLocked        =   db.Column(db.String(1))
    RegisteredDate      =   db.Column(db.String(20))
    PrevMonthAmount     =   db.Column(db.Numeric(25, 9))
    PrevYearAmount      =   db.Column(db.Numeric(25, 9))

 Branch | Status | Curr | Inputter  |      Createdon      | Authorizer |     Authorizeon     | ID  |       Description       | BranchManagerName | ContactNumber |                                         Address                                         | District | Province | ReportLocked | RegisteredDate | PrevYearAmount | PrevMonthAmount | LocalBranchManagerName | LocalDescription |                                LocalAddress                                 
--------+--------+------+-----------+---------------------+------------+---------------------+-----+-------------------------+-------------------+---------------+-----------------------------------------------------------------------------------------+----------+----------+--------------+----------------+----------------+-----------------+------------------------+------------------+-----------------------------------------------------------------------------
 HO     | AUTH   | 9    | MINAL | 2018-09-10 21:48:13 | MORAKOT.V  | 2018-09-10 21:48:13 | HO  | Head Office             | Chariya Sambeth   | N/A           | #3722,  Sihanou Blv, Veal Vonging, 7 Makara, Phnom Penh City.                           |          | 12       | 10000        | 2015-12-16     |    0.000000000 |     0.000000000 | ááá·áá¶áááááááá·              | áá¶áá·áá¶áááááááá¶á     | áááááááá¥á§á¡ áááá»ááá¸.á¥ áááá¼áá¥á§á¤ áá¼áá·á. áá»á-ááááá¶áááá¶áááá áááá»á-áááá»á-ááááá§áááá¶ ááááá-áá¶ááá¶áá¸ááááááá
 HO     | AUTH   | 6    | MINAL | 2018-09-10 21:49:26 | MORAKOT.V  | 2018-09-10 21:49:26 | KPS | Preah Sihanouk Province | Lipop Heang       | N/A           | Group 1110 Village 1112 Commune 1112 Preah Sihanouk Preah Sihanouk                      |          | 18       | 10000        | 2016-07-28     |    0.000000000 |     0.000000000 | á áá áá¸áááá               | ááááááááááá¸á áá»       | áááá»á á¡á¡á¡á  áá¼áá· á á¢ ááááá¶áá á¡á¡á¡á¢ áááááááááá¸á áá» ááááááááááá¸á áá»
 HO     | AUTH   | 2    | MINAL | 2018-09-10 21:50:58 | MORAKOT.V  | 2018-09-10 21:50:58 | SRP | Siemreap Province       | Vicheka Kong      | NA            | House 222A, Street 222A, Knar Village Chreav Commune Siem Reap City Siem Reap Province. | 1710     | 17       | 10000        | 2017-11-24     |    0.000000000 |     0.000000000 | ááá áá·ááááá¶á              | áááááááááá¶á       | áááááááá¢á¢á¢á¢á¶,<U+200B>áááá¼áááá<U+200B>á¢á¢á¢á¢á¶,<U+200B>áá¼áá·áááá¶á ááááá¶áááááá¶á áááá»áááááá¶á áááááááááá¶á

下面是我将这些数据写入csv文件的脚本:

from app.Branch.models import *
from sqlalchemy.orm import aliased

TableName = 'BRANCH'

Columns = BRANCH.__table__.columns.keys()

def writeCSV(ID):
    f= open("%s.csv"%TableName,"a+")

    obj = BRANCH.query.order_by(BRANCH.ID).filter(BRANCH.ID==ID).first()

    string = ''

    # set body data
    for col in Columns:
        try:
            string = string + "," + getattr(obj,col).encode('utf-8')
        except Exception as e:
            print col
            string = string + "," + str(getattr(obj,col))

    f.write(string[1:]+'\n')

    f.close

string = ''

f= open("%s.csv"%TableName,"w+")
# set header
for col_header in Columns:
    string = string + "," + col_header
f.write(string[1:]+'\n')
f.close

branchObj = BRANCH.query.all()

for item in branchObj:
    writeCSV(item.ID)

但是,最后的csv文件很好,除了第一行如下所示:

Branch  Status  Curr    Inputter    Createdon   Authorizer  Authorizeon ID  Description LocalDescription    BranchManagerName   LocalBranchManagerName  ContactNumber   Address LocalAddress    District    Province    ReportLocked    RegisteredDate  PrevMonthAmount PrevYearAmount              
 City.  <!> áááááááá¥á§á¡ áááá»ááá¸.á¥ áááá¼áá¥á§á¤ áá¼áá·á. áá»á-ááááá¶áááá¶áááá áááá»á-áááá»á-ááááá§áááá¶ ááááá-áá¶ááá¶áá¸ááááááá       12  10000   2015-12-16  0   0                                                                   
HO  AUTH    6   MORAKOT.V   2018-09-10 21:49:26 MORAKOT.V   2018-09-10 21:49:26 KPS Preah Sihanouk Province ááááááááááá¸á áá»  Lipop Heang á áá áá¸áááá  N/A Group 1110 Village 1112 Commune 1112 Preah Sihanouk Preah Sihanouk  áááá»á á¡á¡á¡á  áá¼áá· á á¢ ááááá¶áá á¡á¡á¡á¢ áááááááááá¸á áá» ááááááááááá¸á áá»        18  10000   2016-07-28  0   0               
HO  AUTH    2   MORAKOT.V   2018-09-10 21:50:58 MORAKOT.V   2018-09-10 21:50:58 SRP Siemreap Province   áááááááááá¶á Vicheka Kong    ááá áá·ááááá¶á    NA  House 222A   Street 222A     Knar Village Chreav Commune Siem Reap City Siem Reap Province. áááááááá¢á¢á¢á¢á¶    âáááá¼ááááâá¢á¢á¢á¢á¶ âáá¼áá·áááá¶á ááááá¶áááááá¶á áááá»áááááá¶á áááááááááá¶á 1710    17  10000   2017-11-24  0   0

截断数据的位置标记为 <!>

怎么了?我该怎么修?谢谢。

2 回复 | 直到 7 年前

James Lim 7 年前

我可以用模拟的数据源在本地复制这个。

看起来你必须关闭文件以确保它被刷新。

f.close()   # instead of f.close

f.close 返回功能 ,但是 f.close() 实际上是调用了它。我们可以使用

def writeCSV(ID):

    f= open("%s.csv"%TableName,"a+")

    obj = BRANCH.query.order_by(BRANCH.ID).filter(BRANCH.ID==ID).first()

    string = ''

    # set body data
    for col in Columns:
        try:
            string = string + "," + getattr(obj,col).encode('utf-8')
        except Exception as e:
            print col
            string = string + "," + str(getattr(obj,col))

    f.write(string[1:]+'\n')
    f.close()
    if ID == 0:
        # here we simulate what would happen if the GC decides
        # to release the first open handle at this point
        g.close()

string = ''

g= open("%s.csv"%TableName,"w+")  # note the rename for demonstration purposes
# set header
for col_header in Columns:
    string = string + "," + col_header
g.write(string[1:]+'\n')
g.close

branchObj = BRANCH.query.all()

for item in branchObj:
    writeCSV(item.ID)

close 明确地说,您将由GC来决定何时关闭文件,这可能会导致类似这样的错误。看到了吗 http://blog.lerner.co.il/dont-use-python-close-files-answer-depends/

一旦 ,然后经过 f 进入之内 writeCSV 写入CSV with 语法:

with open("%s.csv"%TableName,"w+") as f:
    writeHeader(f)
    for item in branchObj:
        writeCSV(f, item.id)

具有 文件退出后立即关闭。

-1

anoop.babu 7 年前

请改用内置的CSV模块。下面是一个例子。

#!/usr/bin/env python3
import csv

inbuilt CSV module . 您可以参考文档来调整它。

sample = [("Branch", "Status" ,"Curr"), ("HO","AUTH"), ("HO","AUTH",9)]

与您的数据相似的示例输入。让我们考虑第一个元组作为头。

csv.register_dialect(
            'test', 'unix', delimiter=' ', quoting=csv.QUOTE_NONNUMERIC,
            skipinitialspace=True
        )
test_dialect = csv.get_dialect('test')

在这里,我们创建一个名为“test”的自定义方言。我们将分隔符指定为一个空格。

现在让我们打开一个文件并将数据写入其中。在with子句中打开文件将自动关闭它。这里我们正在创建一个csv.writer文件对象testwriter,使用方言作为我们的自定义测试方言。testwriter.writerow文件()将从元组中写入一整行,空格作为分隔符。

filename = "test.csv"
with open(filename, 'w', newline='') as csvfile:
            testwriter = csv.writer(csvfile, test_dialect)
            for datas in sample:
                testwriter.writerow(datas)

这个测试.csv文件如下:,

"Branch" "Status" "Curr"
"HO" "AUTH"
"HO" "AUTH" 9