Select in Sqlite3

Question

I am using the select in sqlite3 statement to get rows from django db (default db)

p=connection.cursor().execute("SELECT * FROM webapp_information WHERE dat  BETWEEN date('now', 'start of day') AND date('now', 'localtime');")
a=p.fetchall()
for i in a:
  print(i)`

i get the dat(date field) from db but it returns nothing.

I tried in different way-

In [206]: p=connection.cursor().execute("SELECT * FROM webapp_information WHERE dat='2018-08-28';")

It returns with a empty list, however I pretty sure the date are stored in the it. Any help? thank you in advance. Modules.py

from __future__ import unicode_literals
from django.db import models 
import datetime

# Create your models here.

class information(models.Model):
login=models.CharField(max_length=100,)
idd=models.CharField(max_length=100,)
avatar_url=models.CharField(max_length=100,default='nll',null=True)
gravatar_id=models.CharField(max_length=100,default='nll',null=True)
url=models.CharField(max_length=100, default='nll',null=True)
html_url=models.CharField(max_length=100,default='nll',null=True)
followers_url=models.CharField(max_length=100,default='nll',null=True)
following_url=models.CharField(max_length=100,default='nll',null=True)
gists_url=models.CharField(max_length=100,default='nll',null=True)
starred_url=models.CharField(max_length=100,default='nll',null=True)
subscriptions_url=models.CharField(max_length=100,default='nll',null=True)
organizations_url=models.CharField(max_length=100,default='nll',null=True)
repos_url=models.CharField(max_length=100,default='nll',null=True)
events_url=models.CharField(max_length=100,default='nll',null=True)
received_events=models.CharField(max_length=100,default='nll',null=True)
typ=models.CharField(max_length=100,default='nll',null=True)
site_admin=models.CharField(max_length=100,default='nll',null=True)
name=models.CharField(max_length=100,default='nll',null=True)
company=models.CharField(max_length=100,default='nll',null=True)
blog=models.CharField(max_length=100,default='nll',null=True)
location=models.CharField(max_length=100,default='nll',null=True)
email=models.CharField(max_length=100,default='nll',null=True)
hireable=models.CharField(max_length=100,default='nll',null=True)
bio=models.CharField(max_length=100,default='nll',null=True)
public_repos=models.CharField(max_length=100,default='nll',null=True)
public_gists=models.CharField(max_length=100,default='nll',null=True)
followers=models.CharField(max_length=100,default='nll',null=True)
following=models.CharField(max_length=100,default='nll',null=True)
created_at=models.CharField(max_length=100,default='nll',null=True)
updated_at=models.CharField(max_length=100,default='nll',null=True)
dat = models.DateField()
class sapi(models.Model):
    dat = models.DateField()

Use [Django ORM](https://docs.djangoproject.com/en/2.1/ref/models/querysets/) — Marcos Schroh, Aug 28 '18 at 09:58
I thought this way is easier and understandable quickly.. If you know how to do it please let me know thanks — Vishak Raj, Aug 28 '18 at 10:25
please post a proper MVC if you hope to get a serious answer. This being said, I can only second Daniel Roseman and Marcos Schroh: if you're using Django with a relational database, not using the ORM is a complete WTF. — bruno desthuilliers, Aug 28 '18 at 10:32
Oh and yes: sqlite is really a poor substitute for a proper SQL database, and can breaks in the most surprising ways... specially if you use it directly instead of using the ORM, it's really easy to insert invalid data. — bruno desthuilliers, Aug 28 '18 at 10:37

score 1 · Accepted Answer · answered Aug 28 '18 at 10:43

1

I think you're looking to do something like this, with date ranges:

from webapp.models import Information

start_of_day_date = ...
now_date ...

p = Information.objects.filter(
    dat__range=(start_of_day_date, now_date),
)

for i in p:
    print(p)

For a good explanation on how to set start_of_day_date and now_date, see this post: Determine start and end time of current day (UTC -> EST -> UTC) ; Python

Good luck!

answered Aug 28 '18 at 10:43

FlipperPA

13,607
4
39
71

sir, i tried this method too.. the querryset is empty..Out[3]: In modules.py, i declared dat = models.DateField() I do not know the reason..thanks – Vishak Raj Aug 28 '18 at 13:36
I checked the admin panel, there are many data that has dat values.. I use the search panel to search the dat field.. I registered the dat field in search_fields() method..in that also no result only empty – Vishak Raj Aug 28 '18 at 13:38
Have you tried it with a large date range, like `start_of_day_day = datetime.date(1800, 1, 1)` and `now_date = datetime.date(2200, 1, 1)`? – FlipperPA Aug 28 '18 at 16:04
in [28]: p = information.objects.filter( dat__range=(datetime.date(2018, 8, 22), datetime.date(2018, 8, 28)), ) In [29]: p Out[29]: --- is that any mistake in configuring the modules.py. I will make uplaod the modules.py in the question. could you please tell me what is worng.. thanks – Vishak Raj Aug 28 '18 at 17:00

Select in Sqlite3

1 Answers1