如何使用import.py将数据从.csv文件导入到Django sqlite [英] how to import data from .csv file to django sqlite using import.py

问题描述

我想从csv文件中检索数据，但是我的硬件（RFID）输出为我提供了同一位员工的两个不同的输入时间超时
WorkNames.csv

i want to retrieve data from csv file but my hardware(RFID) output gives me two different entries of same employee for timein & timeout WorkNames.csv

在此有两次输入，我只希望Models.py雇员类中的一位雇员获得一行

In this there are double entries i want only one row for one employee in models.py Employee class

所以请在我的METHOD1中提供帮助建议一些可以将2行合并为1的代码，同时将数据传输到sqlite中

OR

通过建议一些代码从output.csv导入数据来帮助METHOD2

方法1：我使用了 import.py 导入数据。数据导入到sqlite但重复输入

METHOD 1: I used import.py to import data. Data is imported to sqlite but double entries

import os
import sys
import csv
from datetime import datetime

project_dir='catalog'
sys.path.append(project_dir)

os.environ['DJANGO_SETTINGS_MODULE']='locallibrary.settings'
import django
django.setup()

from catalog.models import employee1
data=csv.reader(open('locallibrary/WorkNames.csv'),delimiter=',')
i=1
from datetime import datetime

for row in data:
    if row[0]== '':
        break
    else:


    Employee1=employee1()
    Employee1.Date=row[0]
    Employee1.Name=row[1]
    Employee1.Number=row[2]
    if row[3] =="" or row[3]=='Time IN':
        Employee1.Time_IN='00:00:00 AM'
    else:
        Employee1.Time_IN=str(row[3])

    if row[4] =="" or row[4]=='Time OUT':

        Employee1.Time_OUT='00:00:00 AM'
    else:
        Employee1.Time_OUT=str(row[4])

    Employee1.save()

**方法2：但是要在行中获得超时，我生成了一个新文件** output.csv

**METHOD 2:but to get a timein timeout in row I generated a new file **output.csv

文件已正确生成，但数据未从csv导入到sqlite

the file is genrated properly but data is not importing from csv to sqlite

import os
import sys
import csv
from datetime import datetime

project_dir='catalog'
sys.path.append(project_dir)

os.environ['DJANGO_SETTINGS_MODULE']='locallibrary.settings'
import django

django.setup()

from catalog.models import employee1
data=csv.reader(open('locallibrary/WorkNames.csv'),delimiter=',')

from datetime import datetime
lines=list(data)

r=5
c=5

for i in range(r):
    for j in range(1,5):
        if lines[0][j]=="":
            break

        if lines[i][2] == lines[j][2]:
            lines[j][3]=lines[i][3]

r=5
c=5

for i in range(r):
    for j in range(1,5):
        if lines[i][3]!="" and lines[i][4]=="":
            lines[i][3]='00:00:00 AM'
            lines[i][4]='00:00:00 AM'
            break


writer = csv.writer(open('locallibrary/output.csv', 'w'))
for r in lines:
    writer.writerow(r)

open('locallibrary/output.csv').close()


data1=csv.reader(open('locallibrary/WorkNames.csv'),delimiter=',')
i=1

for row in data:
    if row['Date']=='':
            break
    else:
        Employee1=employee1()
        Employee1.Date=row['Date']
        Employee1.Name=row['Name']
        Employee1.Number=row['Number']
        Employee1.Time_IN=row['Time IN']
        Employee1.Time_OUT=row['Time OUT']
        Employee1.save()

所以请提供帮助我的METHOD1通过建议一些可以将2行合并为一个代码同时将数据传输到sqlite中的

OR

通过建议一些代码从output.csv导入数据来帮助METHOD2

推荐答案

方法1的建议

如果CSV文件中的行不多（可能少于10.000），则可以使用python dict 按员工编号和日期分组。

If there aren't to many rows in your CSV file (maybe less than 10.000), then you could use a python dict to group by employee number and date.

我修改了一部分代码：

... some code ...

record_dict = {}

for row in data:
    if row[0] == '':
        break

    record_date = row[0]
    emp_number = row[2]
    key = (emp_number, record_date)

    if key not in record_dict:
        record_dict[key] = employee1()
        record_dict[key].Date = row[0]
        record_dict[key].Name = row[1]
        record_dict[key].Number = row[2]
        record_dict[key].Time_IN = None
        record_dict[key].Time_OUT = None

    if row[3] not in ('', 'Time IN'):
        record_dict[key].Time_IN = row[3]
    if row[4] not in ('', 'Time OUT'):
        record_dict[key].Time_OUT = row[4]

for emp in record_dict.values():
    emp.save()

顺便说一句：您应该阅读 PEP8 ，这是python编码样式指南，显示了编写python代码的首选方法。

By the way: you should read PEP8, which is the python coding style guide showing the preferred way to write python code.

这篇关于如何使用import.py将数据从.csv文件导入到Django sqlite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！