小贝子编程

如何将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_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)

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

        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
  }
]

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

接下来.values('day','membertype')通过新添加的列day和membertype来构建此片段

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

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

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

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

自：

membertype = models.CharField(max_length=10)

因为TextField用于存储很长的字符串，我想membertype不是。我也猜它不应该是空的

我相信

mrangry777的答案包含了很多不必要的细节。您说您希望在给定日期内按成员类型进行事件事务计数。您所要做的就是将查询集筛选为要查找的内容，然后获取查询集的计数。没有必要截断时间;如果您的模型具有日期时间字段，则可以为其提供 DateTime.DateTime 对象作为筛选器，而不是进行不必要的截断。例如：

#Midnight on July 25th
start_date = datetime(2019, 7, 25, 0, 0, 0)
#Midnight on July 26th
end_date = datetime(2019, 7, 26, 0, 0, 0)
count_totals = {}
for member in membertype:
    daily_event_transactions = MyModel.objects.filter(actualdatetime__gte=start_date).filter(actualdatetime__lt=end_date).filter(membertype=member)
    count_totals[member] = daily_event_transactions.count()

此解决方案的另一个好处是，使用 count() 不会计算查询集，而使用 list() 可以计算查询集。这意味着代码将运行得更快。

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium