django数据库导入csv文件的一个例子

 

目标：导入networksolution域名下载的csv文件, 这个脚本文件要放在和manage.py同级目录下

import os, random, string, csv
from datetime import datetime
import pytz

os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'OpsManage.settings')

import django
django.setup()
from asset.models import DomainName


def gen_random_id():
    mystring = string.digits + string.ascii_letters
    random_list = random.sample(mystring, 8)
    random_string = "N" + "".join(random_list)
    return random_string

# 生成一个数据库中不存在的主键id, 避免重复
def target_id():
    domain_id_lists = DomainName.objects.all().values("id")
    list1 = []
    for x in domain_id_lists:
        list1.append(x.get("id"))
    while True:
        myid = gen_random_id()
        if myid not in list1:
            break
    return myid


f = open("/data/apps/opsmanage/apps/asset/domainsList.csv", encoding="utf-8")
next(f) # 跳过第一行标题
reader = csv.reader(f)
for row in reader:
    privacy = row[3]
    if privacy == "Yes":
        privacy = bool(privacy)
    else:
        privacy = bool(0)
    if not DomainName.objects.filter(name=row[0]):
        DomainName.objects.create(
            id=target_id(),
            name=row[0],
            registerTime=datetime.strptime("2020-01-01", "%Y-%m-%d").replace(tzinfo=pytz.utc),
            expiredTime=datetime.strptime(row[6], "%m/%d/%Y").replace(tzinfo=pytz.utc),
            autoRenew=1,
            account_id="8488c869-a4b6-4c9b-ad16-2ea2d04f"
        )
    else:
        DomainName.objects.filter(name=row[0]).update(
            expiredTime=datetime.strptime(row[6], "%m/%d/%Y").replace(tzinfo=pytz.utc), proxyService=privacy)

f.close()

 

1. 注意代码中时间的处理

# 字符串转UTC时间
expiredTime = datetime.strptime(row[6],"%m/%d/%Y").replace(tzinfo=pytz.utc), 加上repalce可以避免下面警告， 因为django配置中开启了时区。

RuntimeWarning: DateTimeField DomainName.expiredTime received a naive datetime (2021-07-24 00:00:00) while time zone support is active.

>>> import pytz
>>> import datetime
>>> datetime.datetime.now()
datetime.datetime(2009, 5, 26, 20, 23, 12, 109000)
>>> datetime.datetime.now(pytz.utc)
datetime.datetime(2009, 5, 26, 12, 23, 23, 500000, tzinfo=<UTC>)
>>> datetime.datetime.now(pytz.timezone(pytz.country_timezones('cn')[2]))
datetime.datetime(2009, 5, 26, 20, 24, 9, 296000, tzinfo=<DstTzInfo 'Asia/Chongqing' CST+8:00:00 STD>)
>>> pytz.country_timezones('cn')
['Asia/Shanghai', 'Asia/Harbin', 'Asia/Chongqing', 'Asia/Urumqi', 'Asia/Kashgar']

>>> datetime.now().strftime("%F %H:%M:%S")
'2021-04-30 14:48:13'

 

2. tzinfo的一个链接：https://www.cnblogs.com/goodspeed/archive/2011/11/07/python_tzinfo.html