Question

Python using sqllite3 package

In this exercise, your task is to inspect the given database, which is called 'chinook.db', as you can see from the testing code below in the example. We first would like to know how the logical schema of the database looks like, in order to work with it later in terms of reading from and writing to the database. Please also note that a software tool like "DB Browser for SQLite" can be used to inspect and modify an SQLite database file like 'chinook.db'.

To solve this exercise you have to write a function get_table_information_from_database(database_filename), which returns a dictionary that has all the available table names as its keys, and a list of the column names for the respective table as its values. The column names are the second entries in the record returned from the "PRAGMA table_info" SQL statement given below.

Regarding the structure of this code, you have to make sure that the Python sqlite3 package is first imported. Using the connect method from this package, a database connection can be opened. This connection uses the concept of a cursor object to send SQL statements to the database in the form of strings. In case the SQL string represents a query, the cursor.execute method returns a result containing the result of this query. This result is a list and can be inspected here in this question for the logical schema information.

The necessary SQL statements you need are:

• "SELECT name FROM sqlite_master WHERE type='table'"
• and for each table, identified by a string 'table_name': "PRAGMA table_info('table_name')"

Attention: Please ignore table names that start with the substring "sqlite" from the query result, since these are internal tables, which in general should not be used by an application!

Don't forget to close the database connection after you are finished accessing the database!

Please have a look at the examples in the lecture slides for lecture 15 to see examples of SQLite being used. Additionally, you may want to look up more information on the web page for Python's SQLite API here. SQL statements are simply strings, which can be concatenated using string literals and string variables.

Note: As always, it is a good idea to work on this example using your favourite IDE first (download the database file), before you enter it into coderunner. This way you can more easily debug your code!

For example:

Test

Result

database_filename = 'chinook.db' metadata_dictionary = get_table_information_from_database(database_filename) for table in sorted(metadata_dictionary.keys()): print("{}: {}".format(table, metadata_dictionary[table]))

albums: ['AlbumId', 'Title', 'ArtistId'] artists: ['ArtistId', 'Name'] customers: ['CustomerId', 'FirstName', 'LastName', 'Company', 'Address', 'City', 'State', 'Country', 'PostalCode', 'Phone', 'Fax', 'Email', 'SupportRepId'] employees: ['EmployeeId', 'LastName', 'FirstName', 'Title', 'ReportsTo', 'BirthDate', 'HireDate', 'Address', 'City', 'State', 'Country', 'PostalCode', 'Phone', 'Fax', 'Email'] genres: ['GenreId', 'Name'] invoice_items: ['InvoiceLineId', 'InvoiceId', 'TrackId', 'UnitPrice', 'Quantity'] invoices: ['InvoiceId', 'CustomerId', 'InvoiceDate', 'BillingAddress', 'BillingCity', 'BillingState', 'BillingCountry', 'BillingPostalCode', 'Total'] media_types: ['MediaTypeId', 'Name'] playlist_track: ['PlaylistId', 'TrackId'] playlists: ['PlaylistId', 'Name'] tracks: ['TrackId', 'Name', 'AlbumId', 'MediaTypeId', 'GenreId', 'Composer', 'Milliseconds', 'Bytes', 'UnitPrice']

given start code

import sqlite3

def get_table_information_from_database(database_filename):
pass

Answer 1

I have writtend relevant comments for each line in code:

I am providing the outputs for my own database because I dont have the chinook.db file

So you can check the output format for any database doesn't change:

You can change your database name while passing it to the function get_table_information_from_database

import sqlite3

def get_table_information_from_database(database_filename):
    """Given The Database Name it will Return Database 
       Tables and There Keys in Dictionary Format Keys as
       Table Names and List of Column Names as it Values.
    """
    metadata_dictionary = {} # Creating a Empty Dictionary 
    conn = sqlite3.connect(database_filename) # Connect to Sqlite Database for a given database file name
    print("Opened database successfully") # Printing for Database Successuflly opened
    cur = conn.cursor() # Creating a cursor object using the cursor() method
    cur.execute("SELECT name FROM sqlite_master WHERE type='table'") # Executing query using cursor getting all tables present in the database
    table_names = [] # Empty list for storing all table names
    for name in cur.fetchall(): # Looping over the results which are fetched from executing the above query
        if not name[0].startswith("sqlite"): # only collecting tables which doesn't startswith "sqlite"
            table_names.append(name[0])
    for table_name in table_names: # Looping over each table name and querying on that
        cur.execute("PRAGMA table_info({})".format(table_name)) # query for each table information with same cursor
        # collecting only the table column names into a single list from the fetched results and placing into the dictionary
        metadata_dictionary[table_name] = [info[1] for info in cur.fetchall()] 
    conn.close() # closing connection to database
    return metadata_dictionary # return the created dictionary


database_filename = 'VSAPT.db' # change the database name as per your requirements
metadata_dictionary = get_table_information_from_database(database_filename) # calling the function
for table in sorted(metadata_dictionary.keys()):
    print("{}: {}".format(table, metadata_dictionary[table]))

Code screenshort with output

You can see the output is Table_name and the Column Names as list for that key

If any doubt further please drop a comment