Checking for custodians with the get_or_add_custodian() function

At this point, the database is initialized and ready for further interaction. The get_or_add_custodian() function is called to check for the existence of the custodian and to pass along the ID if it is found. If the custodian does not exist, the function will add the custodian to the Custodians table. On line 120, we call the get_custodian() function to check and see whether the custodian exists. On line 122, we use a conditional to check whether id is not empty, and if so, assign the ID of the custodian to the cust_id variable. The SQLite library returns tuples for backward compatibility, the first element of which will be our ID of interest:

111 def get_or_add_custodian(conn, custodian):
112     """
113     The get_or_add_custodian function checks the database for a
114         custodian and returns the ID if present;
115         Or otherwise creates the custodian
116     :param conn: The sqlite3 database connection object
117     :param custodian: The name of the custodian
118     :return: The custodian ID or None
119     """
120     cust_id = get_custodian(conn, custodian)
121     cur = conn.cursor()
122     if cust_id:
123         cust_id = cust_id[0]

If the custodian is not found, we insert it into the table for future use. In lines 125-126, we craft a SQL statement to insert the custodian into the Custodians table. Note the null string in the VALUES section; this is interpreted by SQLite as a NoneType object. SQLite converts NoneType objects in our primary key field to an auto-incrementing integer. Following the null value is our custodian string. SQLite requires that string values be wrapped in quotes, similar to Python.

We must use double quotes to wrap our query that contains single quotes. This prevents any issues with a string breaking due to an error with the quotes. If you see a syntax error in this section of the code, be sure to check the quotes used on lines 125-126.

Finally, we execute this statement and return the empty cust_id variable so that the main() function will have to check for the custodian in the database again and rerun this function. The next pass should detect our inserted value and allow the main() function to proceed. We have the following code:

124     else:
125         sql = """INSERT INTO Custodians (cust_id, name) VALUES
126             (null, '{}') ;""".format(custodian)
127         cur.execute(sql)
128         conn.commit()
129     return cust_id

Although we could call the get_custodian() function here (or grab the ID after the insert) for validation purposes, we have the main() function check for the custodian again. Feel free to implement one of these alternative solutions and see in what ways it impacts the performance and stability of the code.