Help: Python & SQLite

[stepper]

I have the following code:

fo = open('/home/stepper/logs/beetsimport.txt', "r")
var = fo.readline().split(',')
artist_mbid = var[2]
album_mbid = var[1]
album_path = (var[0][2:][:-1])
artist_path = os.path.dirname(album_path)
fo.close()
#last_api_key = 

#tracks = album_tracks()

con = lite.connect('/home/stepper/.config/beets/library.blb')

def artist_info():
    with con:
        cur = con.cursor()
        cur.execute("SELECT albumartist,albumartist_sort,genre FROM albums where mb_albumartistid='%s'" % (var[2]),)
        data = cur.fetchone()
         cur.close()
        f = open(artist_path + "/test.nfo","w+")
        f.write("<?xml version=1.0 encoding=UTF-8 standalone=yes ?>\n")
        f.write("<artist>\n")
        f.write("    <name>%s</name>\n" % (data[0]),)
        f.write("    <musicBrainzArtistID>%s/musicBrainzArtistID>\n"  % (artist_mbid,))
        f.write("    <sortname>%s</sortname>"  % (data[1]))
        f.close()
        albums = artist_albums()
        with open(artist_path + "/test.nfo","a+") as file:
            file.write("%s" % (albums,))
            file.write("</artist>")
            file.close()
      
def artist_albums():
    with con:    
        cur1 = con.cursor()
        cur1.execute("SELECT year,album FROM albums where mb_albumartistid='%s'" % (artist_mbid,))
        rows = cur1.fetchall()
        for row in rows:
            album = """
    <album>
        <title>%s</title> 
        <year>%s</year>
    </album>
            """ % (row[1], row[0])
            return album

It returns the error, 'TypeError: 'NoneType' object is not subscriptable' which points out to sql returning variable data as None. But running from python console it works. Where am I going wrong?

Python 3.6.0 (default, Jan 16 2017, 12:12:55)
[GCC 6.3.1 20170109] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3 as lite
>>> import os
>>> con = lite.connect('/home/stepper/.config/beets/library.blb')
>>> cur = con.cursor()
>>> cur.execute("SELECT albumartist,albumartist_sort,genre FROM albums where mb_albumartistid='9132d515-dc0e-4494-85ae-20f06eed14f9'")
<sqlite3.Cursor object at 0x7f8c5e6a1d50>
>>> data = cur.fetchone()
>>> print(data[2])
Soul

 

[XennoX]

<snip>

It looks like you haven't declared what con (and var) is in context of the local scope of the functions artist_info() and artist_albums(). I am surprised Python didn't throw a syntax error of con not being defined.

You have 3 options:

1. Pass the connection variable to the functions.
2. Establish the connection from within the functions.
3. Make use of a global variable. (Really not recommended)

I would probably use the first one. Example:

from sqlalchemy import create_engine
import pandas as pd

# Establish a connection to the sqllite database
engine = create_engine('sqlite:///mydatabase.sqllite')

def artist_info(sqlengine_connection):
	with engine.connect() as con:
	    recordSet = con.execute("SELECT albumartist, albumartist_sort, genre FROM albums")
	    dataframe = pd.DataFrame(recordSet.fetchmany(3)) # Returns 3 records as a dictionary.
	    dataframe.columns = recordSet.keys()
	return dataframe

 

[stepper]

Darn. I see I used var[2] instead of artist_mbid. I'll correct and will try your approach as well.
Thanks XennoX