Writing column names to file

Question

I have an SQLite DB file and I am parsing the data from each column in a table of the db to a .txt file. At the moment it is writing the column contents to the file but it won't pull the column names and write those. How can I go about it as I have tried to use this guide Is there a way to get a list of column names in sqlite? but i cannot seem to get it to work. Here is my code with an attempt at pulling the column names from the table.

import sqlite3
from sqlite3 import Error


# create a database connection to the SQLite database specified by the db_file
def create_connection(db_file,detect_types=sqlite3.PARSE_DECLTYPES):
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)

    return None

# Query specific rows in the sms table
def select_data(conn):
    cur = conn.cursor()

    cur.execute("SELECT _id, address, strftime('%d-%m-%Y', date / 1000, 'unixepoch'),read, type, body, seen FROM sms")
    print("Writing the contents of the sms table to an evidence file")
    print("\t")

# Trying to pull out column names from db table
def get_col_names():
    conn = sqlite3.connect("mmssms.db")
    c = conn.cursor()
    c.execute("SELECT _id, address, strftime('%d-%m-%Y', date / 1000, 'unixepoch'),read, type, body, seen FROM sms")
    return [member[0] for member in c.description]



    # Write the data to a smsEvidence.txt file
    with open('EvidenceExtractionFiles/smsInfo.txt', 'a+') as f:
        rows = cur.fetchall()
        for row in rows:
            #print(row)

            f.write("%s\n" % str(row))
        print("SMS Data is written to the evidence File")



# path to where the db files are stored
def main():
    database = "H:\College Fourth Year\Development Project\Final Year Project 2018\mmssms.db"

     # create a database connection
    conn = create_connection(database)
    with conn:

        # print("Query specific columns")
        select_data(conn)
    # close db connection
    if(conn):
        conn.close()
        print("Database closed")


if __name__ == '__main__':
    main()

What is the output of `cursor.description` that you are getting now? — Maciej Gol, Feb 05 '18 at 10:27
There is no output at all and it is now NOT writing to the file anymore — GreenCoder90, Feb 05 '18 at 10:41
You are not calling `get_col_names` anywhere, thus you have no output. Your file writing code is now after the `return` statement of `get_col_names`, thus it's never reached, thus no file is ever written to. — Maciej Gol, Feb 05 '18 at 10:43

AnythingIsFine · Accepted Answer · 2018-02-05T10:40:41.433

0

You may use cursor.description which holds info about the column names:

[ ... ]

cur = cursor.execute('SELECT * FROM test_table LIMIT 100')
col_names = [ name[0] for name in cur.description ]
print (col_names)

[ ... ]

edited Feb 05 '18 at 10:40

answered Feb 05 '18 at 10:32

AnythingIsFine

1,777
13
11

It is working now. I managed to pull each column name from the DB table and write it to the file. I then was able to pull column data and write it to the file after that. Its working now, thank you. – GreenCoder90 Feb 05 '18 at 11:19

Writing column names to file

1 Answers1