CODEWITHSUNDEEP
pythonpython-3.xsqlite
saqlain saleem
saqlain saleem

Reputation: 11

how to search for a date by year in sqlite database using python?

if we want to search a column or record from table by year and format like year-month-date then how can retrieve. I have a table that have 23 column related to weather i want to access all data 2010 the which query will b used.

i'll try it but it gives no output:

c.execute("select * from lahore_weather_2009_May where substr(PKT, 1, 4) = '2011'")

and if i applied this code to acess by month then also give no output:

validMonth = False
while not validMonth:
    lookForMonth = input('What month, please? (number from 1 through 12):')
    try:
        validMonth = 1<=int(lookForMonth)<=12
    except:
        pass

        sqlCmd = 'SELECT PKT FROM lahore_weather_2009_May WHERE SUBSTR(PKT,4,2)="%.2i"' % int(lookForMonth)
        for row in conn.execute(sqlCmd):
            print (row)
            c.commit()

table already created in database

Upvotes: 0

Views: 726

Answers (1)

forpas
forpas

Reputation: 164089

SQLite does not have a DATE data type, so dates are stored as TEXT.
Assuming that your date column has the format YYYY-MM-DD you can use the function strftime():

select * from tablename
where strftime('%Y', datecolumn) = '2010'

the pattern '%Y' extracts the year of the date.
Or use substr() to get the starting 4 characters from the date:

select * from tablename
where substr(datecolumn, 1, 4) = '2010'

Upvotes: 1

Related Questions