Python Forum

I've started to experiment with python database and have the simple demo below working.

BUT, I'm concerned about the date column in the table... it seems to print OK but is the data "type" really a date type?

I ask because I read here http://www.cs.toronto.edu/~nn/csc309/gui...nsFIN.html

that the sql date data type format should be yyyy-mm-dd
BUT, if you notice in the code I inadvertently entered the date as month/day/year...

SO, the question is did the date go in the record as a date or as a string or what?

How do I have Python print out the data type for each column in a table?

This is important later when you attempt to sort by or search for a date or whatever.

Thanks for any help.

import sqlite3
connection = sqlite3.connect('demo.db')
cursor = connection.cursor()
cursor.execute ("CREATE TABLE tableA(id integer primary key not null, Name varchar2(25), Date date, notes varchar2(200))")
connection.commit()
cursor.execute ("INSERT INTO tableA values(1,'Elvis','8/29/17','bla bla bla')")
cursor.execute ("INSERT INTO tableA values(2,'Sting','8/31/17','do da do da day')")
connection.commit()
cursor.execute ("SELECT * FROM tableA")
while True:
        row = cursor.fetchone()
        if row == None:
            break
        print (row[0],row[1],row[2])

Most databases are smart enough to know what a date looks like in various local areas.  For most of the USA, for example, dates are frequently mm/dd/yy (a lot of Europe is dd/mm/yyyy, as well as US military), so most databases accept that as input.  It'll still be formatted as yyyy-mm-dd when you select the data back out of it, though.

Furthermore, the data type for the column is "date".  If you tried to insert something that the database didn't recognize as a real date, it'd throw an error and nothing would be inserted.

What is the database management system?
In postgresSQL, you would use:

 select column_name, data_type from information_schema.columns where where table_name = 'tablename';

Other systems should have something similar.

It looks like sqlite, which means you might be able to use the much simpler describe table_name;