时间戳

首页 > 解决方案 > 如何将 SQL 导入转换为 Django 导入？

python - 如何将 SQL 导入转换为 Django 导入？

问题描述

我正在尝试使用 Django 从数据库中导入数据。这是以前使用 SQL 完成的，但现在我尝试使用 Django 而不是 SQL。我真的不熟悉 SQL，并且无法将 SQL 导入转换为 Django 导入。

到目前为止，我已经转换con = psycopg2.connect("dbname='mydatabase' user='mydatabase_reader' host='xxx.xxx.xx.xx' password='test'")为，users = EventTransactions.objects.using('mydatabase').filter('profitcenter_id'=profitcenter_id, 'actualdatetime'=date).values('')但这就是我所得到的。现在，我正在使用 for 循环来获取每天的信息。

def handle(self, *args, **options):

        r = redis.Redis(host=REDIS_HOST, db=REDIS_DB)

        memberships = MEM_MAP_INV.keys()

        start_date = datetime.datetime.strptime(options['start_date'],'%Y-%m-%d').date()
        end_date = datetime.datetime.strptime(options['end_date'],'%Y-%m-%d').date()

        profitcenter_id = options['profitcenter_id']

        # number of days passed
        elapsed_days = (end_date - start_date).days

        dates = [start_date + datetime.timedelta(days=i) for i in range(elapsed_days)]

        con = psycopg2.connect("dbname='mydatabase' user='mydatabase_reader' host='xxx.xxx.xx.xx' password='test'")
        cur = con.cursor()

        for date in dates:
             counts = {}
             for m in memberships:
                membership = tuple(MEM_MAP_INV[m])

                 sql = cur.mogrify("""SELECT count(*) from eventtransactions WHERE profitcenter_id = %s AND date(actualdatetime) = %s""",
                                   (profitcenter_id, date))

                 # have to split into two cases due to how postgres queries for NULL values
                 if m == 'n/a':
                     sql = cur.mogrify(sql + """ AND membertype IS NULL""")
                 else:
                     sql = cur.mogrify(sql + """ AND membertype IN %s""",(membership,))

                 cur.execute(sql)
                 count = cur.fetchone()[0]

                 counts[m] = count

            # prepare for redis
            r_key = 'urec:counts:%s:%s' % (profitcenter_id, str(date))
            counts = json.dumps(counts)
            r.set(r_key,counts)

         cur.close()
         con.close()

以下是 EventTransactions 模型中的内容：

    eventtransactions_id = models.IntegerField()
    profitcenter_id = models.IntegerField()
    customer_gender = models.TextField()
    customer_firstname = models.TextField()
    customer_lastname = models.TextField()
    actualdatetime = models.DateTimeField(blank=True, null=True)
    custnum = models.BigIntegerField(blank=True, null=True)
    birthdate = models.DateField(blank=True, null=True)
    membertype = models.TextField(blank=True, null=True)
    eventname = models.TextField(blank=True, null=True)

标签： pythonsqldjangodatabase

解决方案

所以你可以像这样实现你的目标：

        from django.db.models import Count
        from django.db.models.functions import TruncDay

        start_date = datetime.date(2019, 7, 20) # We enter start date
        end_date = datetime.date(2019, 7, 26) # We enter end date
        values = list(TestModel.objects.annotate(day=TruncDay('actualdatetime')).values('day','membertype').annotate(count=Count('id')).filter(actualdatetime__gte=start_date).filter(actualdatetime__lte=end_date))

样本输出：

[
  {
    'membertype':'A',
    'day':datetime.datetime(2019,
    7,
    20,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'A',
    'day':datetime.datetime(2019,
    7,
    25,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'B',
    'day':datetime.datetime(2019,
    7,
    20,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'B',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'B',
    'day':datetime.datetime(2019,
    7,
    22,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'B',
    'day':datetime.datetime(2019,
    7,
    24,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'C',
    'day':datetime.datetime(2019,
    7,
    24,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'D',
    'day':datetime.datetime(2019,
    7,
    20,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'D',
    'day':datetime.datetime(2019,
    7,
    24,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'D',
    'day':datetime.datetime(2019,
    7,
    25,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'E',
    'day':datetime.datetime(2019,
    7,
    26,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'F',
    'day':datetime.datetime(2019,
    7,
    23,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'G',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'G',
    'day':datetime.datetime(2019,
    7,
    24,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'G',
    'day':datetime.datetime(2019,
    7,
    25,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'H',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'H',
    'day':datetime.datetime(2019,
    7,
    23,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'H',
    'day':datetime.datetime(2019,
    7,
    26,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'I',
    'day':datetime.datetime(2019,
    7,
    25,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'K',
    'day':datetime.datetime(2019,
    7,
    20,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'K',
    'day':datetime.datetime(2019,
    7,
    23,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'K',
    'day':datetime.datetime(2019,
    7,
    25,
    0,
    0,
    tzinfo=<UTC>),
    'count':2
  },
  {
    'membertype':'K',
    'day':datetime.datetime(2019,
    7,
    26,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'L',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'L',
    'day':datetime.datetime(2019,
    7,
    22,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'M',
    'day':datetime.datetime(2019,
    7,
    23,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'O',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'O',
    'day':datetime.datetime(2019,
    7,
    23,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'P',
    'day':datetime.datetime(2019,
    7,
    20,
    0,
    0,
    tzinfo=<UTC>),
    'count':2
  },
  {
    'membertype':'Q',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'Q',
    'day':datetime.datetime(2019,
    7,
    25,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'R',
    'day':datetime.datetime(2019,
    7,
    26,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'S',
    'day':datetime.datetime(2019,
    7,
    25,
    0,
    0,
    tzinfo=<UTC>),
    'count':2
  },
  {
    'membertype':'T',
    'day':datetime.datetime(2019,
    7,
    24,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'U',
    'day':datetime.datetime(2019,
    7,
    23,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'V',
    'day':datetime.datetime(2019,
    7,
    20,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'W',
    'day':datetime.datetime(2019,
    7,
    20,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'W',
    'day':datetime.datetime(2019,
    7,
    22,
    0,
    0,
    tzinfo=<UTC>),
    'count':2
  },
  {
    'membertype':'W',
    'day':datetime.datetime(2019,
    7,
    23,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'X',
    'day':datetime.datetime(2019,
    7,
    20,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'Y',
    'day':datetime.datetime(2019,
    7,
    26,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'a',
    'day':datetime.datetime(2019,
    7,
    25,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'c',
    'day':datetime.datetime(2019,
    7,
    20,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'c',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':2
  },
  {
    'membertype':'c',
    'day':datetime.datetime(2019,
    7,
    22,
    0,
    0,
    tzinfo=<UTC>),
    'count':2
  },
  {
    'membertype':'c',
    'day':datetime.datetime(2019,
    7,
    25,
    0,
    0,
    tzinfo=<UTC>),
    'count':2
  },
  {
    'membertype':'c',
    'day':datetime.datetime(2019,
    7,
    26,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'e',
    'day':datetime.datetime(2019,
    7,
    20,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'e',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'f',
    'day':datetime.datetime(2019,
    7,
    24,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'f',
    'day':datetime.datetime(2019,
    7,
    26,
    0,
    0,
    tzinfo=<UTC>),
    'count':3
  },
  {
    'membertype':'g',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'g',
    'day':datetime.datetime(2019,
    7,
    22,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'h',
    'day':datetime.datetime(2019,
    7,
    26,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'i',
    'day':datetime.datetime(2019,
    7,
    20,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'j',
    'day':datetime.datetime(2019,
    7,
    24,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'j',
    'day':datetime.datetime(2019,
    7,
    26,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'l',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':2
  },
  {
    'membertype':'m',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'m',
    'day':datetime.datetime(2019,
    7,
    24,
    0,
    0,
    tzinfo=<UTC>),
    'count':2
  },
  {
    'membertype':'m',
    'day':datetime.datetime(2019,
    7,
    25,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'m',
    'day':datetime.datetime(2019,
    7,
    26,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'n',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'n',
    'day':datetime.datetime(2019,
    7,
    26,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'o',
    'day':datetime.datetime(2019,
    7,
    20,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'o',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'o',
    'day':datetime.datetime(2019,
    7,
    23,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'o',
    'day':datetime.datetime(2019,
    7,
    25,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'q',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'r',
    'day':datetime.datetime(2019,
    7,
    22,
    0,
    0,
    tzinfo=<UTC>),
    'count':2
  },
  {
    'membertype':'r',
    'day':datetime.datetime(2019,
    7,
    25,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'s',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'s',
    'day':datetime.datetime(2019,
    7,
    23,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'s',
    'day':datetime.datetime(2019,
    7,
    24,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'t',
    'day':datetime.datetime(2019,
    7,
    20,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'t',
    'day':datetime.datetime(2019,
    7,
    26,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'u',
    'day':datetime.datetime(2019,
    7,
    24,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'v',
    'day':datetime.datetime(2019,
    7,
    25,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'v',
    'day':datetime.datetime(2019,
    7,
    26,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'w',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'w',
    'day':datetime.datetime(2019,
    7,
    24,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'w',
    'day':datetime.datetime(2019,
    7,
    26,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'x',
    'day':datetime.datetime(2019,
    7,
    23,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'y',
    'day':datetime.datetime(2019,
    7,
    24,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'y',
    'day':datetime.datetime(2019,
    7,
    25,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'z',
    'day':datetime.datetime(2019,
    7,
    21,
    0,
    0,
    tzinfo=<UTC>),
    'count':1
  },
  {
    'membertype':'z',
    'day':datetime.datetime(2019,
    7,
    23,
    0,
    0,
    tzinfo=<UTC>),
    'count':2
  }
]

当您在模型中使用 DateTimeField 时，此片段TestModel.objects.annotate(day=TruncDay('actualdatetime'))用一天注释我们的模型，因此我们需要截断时间并仅保留日期。

接下来.values('day','membertype')这个片段按新添加的列day和membertype

.annotate(count=Count('id'))这是计算我们组中的实例

最后但并非最不重要的是，我们按日期过滤结果： .filter(actualdatetime__gte=start_date).filter(actualdatetime__lte=end_date))

另请注意，在您的模型中，您允许许多字段为空，因此此查询不会获取所有结果。作为旁注，我会改变

membertype = models.TextField(blank=True, null=True)

至：

membertype = models.CharField(max_length=10)

至于TextField存储我猜membertype不是的很长的字符串。另外我想它不应该为空

推荐阅读