Avoid None in cursor.description?

Admin 1
Viewed 862

I'm using sqlite3 in Python.

My table :

cursor.execute("""create table student(rno int, name char(20), grade char, gender char, avg decimal(5,2), dob date);""")

Where cursor is the name of my cursor object ...

I've used cursor.description to display the column names. But there are seven strings in each tuple in which six are None

print(cursor.description)
Output : (('rno', None, None, None, None, None, None), ('name', None, None, None, None, None, None), ('grade', None, None, None, None, None, None), ('gender', None, None, None, None, None, None), ('avg', None, None, None, None, None, None), ('dob', None, None, None, None, None, None))

From API, it is clear that first two elements of each tuple is must. But it's not for me. Why?

Also what changes shall I make in my table to get the values for the other elements which are set as None???

Any relavant help is appreciated....

database-cursor sqlite python
4 Answers
user_68663090

Apparently type_code being returned as None on sqllite 3 is a bug,Refer the link for more details.

bug Details

user_22367260

This is one approach. Still making use of the cursor.description in your original code.

  1. Create a function to show all the records in the database
def show_all():
    # connect to db
    conn = sqlite3.connect('student.db')

    # create cursor
    c = conn.cursor()

    # query the db
    c.execute("SELECT rowid, * FROM student")

    items = c.fetchall()

    for item in items:
         print(item)
  1. Then before closing the connection for the table/database you are working with
    # column names
    col_desc = []
    columns = c.description
    for column in columns:
        col_desc.append(column[0])
    print(col_desc)
  1. In a separate file you could then import the database you are working with and execute the function
import student

students.show_all()
user_114720020

In their answer, user VN'sCorner observes that the second element of the tuple - the type code - being None was a bug. In fact it was reported as a bug, but closed as a duplicate of another bug report which was in turn closed as "Won't Fix" by Gerhard Häring (link):

There is no guarantee that all any column in a SQlite resultset always has the same type. That's why I decided to err on the side of setting the type code to "undefined".

SQLite's flexible typing is what the resolution is referring to, demonstrable by inserting some sample data from Python:

>>> cur.execute('create table t (col text)')
<sqlite3.Cursor object at 0x7f00525c3f40>
>>> cur.execute('insert into t values (?)', ('spam',))
<sqlite3.Cursor object at 0x7f00525c3f40>
>>> cur.execute('insert into t values (?)', (b'spam',))
<sqlite3.Cursor object at 0x7f00525c3f40>
>>> conn.commit()

and viewing the types in the SQLite3 shell:

sqlite> select col, typeof(col) from t;
spam|text
spam|blob
user_53209060
Related