Where is the error with this db creation code & the 'conn' variable?

Where is the error with this db creation code & the 'conn' variable? - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Where is the error with this db creation code & the 'conn' variable? (/thread-14011.html)

Where is the error with this db creation code & the 'conn' variable? - pcsailor - Nov-11-2018

Hello,

I'm following the SQLite tutorial to create a database and tables and I'm pulling my hair out on this error. I don't see it. My linter is showing the 'conn' variable as the error, which means it may be that or the code line bofore that variable creation.

Do you see anything obvious?
I think the way I am calling for the db is the fault, but I'm unsure why.

Win10-64 bit
Python 3.7.0
Dell XPS-13

'''
[SqliteTutorial.net](http://www.sqlitetutorial.net/sqlite-python/creating-database/)
'''
import sqlite3
from sqlite3 import Error

# Create database
def create_connection(db_example):
    ''' create a db connection to a sqlite db '''
    try:
        conn = sqlite3.connect(db_example) # write to hard disk
        # conn = sqlite3.connect(':memory:') # write to RAM only
        print(sqlite3.version)
    except Error as err:
        print('Error: ', err)
    # finally:
        # conn.close()
if __name__ == '__main__':
    create_connection('D:\\Software\db\sqlite\example.db')
    # create_connection() # use when writing to RAM only

# create tables
def create_table(conn, create_table_sql):
    """ create a table from the create_table_sql statement
    :param conn: Connection object
    :param create_table_sql: a CREATE TABLE statement
    :return: 
    """
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
        print('connection to \'example.db\' established')
    except Error as err:
        print('Error: ', err)

# create main() function to create the named tables.
def main():
    database = 'D:\Software\db\sqlite\example.db'

    sql_create_csv01_table = '''CREATE TABLE IF NOT EXISTS csv01 (
        id integer PRIMARY KEY,
        name text NOT NULL,
        begin_date text,
        end-date text
    ); '''

    sql_create_csv02_table = ''' CREATE TABLE IF NOT EXISTS csv02 (
        id integer PRIMARY KEY,
        name text NOT NULL,
        priority integer,
        status_id integer NOT NULL,
        project_id integer NOT NULL,
        begin_date text NOT NULL
        end_date text NOT NULL
        FOREIGN KEY (project_id) REFERENCES csv01 (id)
    ); '''

# create a db connection
    conn = create_connection(database) # HERE IS THE LINTER ERROR
    if conn is not None:
        # create csv01 table
        create_table(conn, sql_create_csv01_table)
        # create csv02 table
        create_table(conn, sql_create_csv02_table)
    else:
        print('Error: Can not create the database connection')

if __name__ == '__main__':
    main()

Thanks,
phil

RE: Where is the error with this db creation code & the 'conn' variable? - Larz60+ - Nov-11-2018

you have multiple entry points (remove lines 18, 19)

RE: Where is the error with this db creation code & the 'conn' variable? - pcsailor - Nov-11-2018

Hi Larz,

Thanks for the time with your answer, again ;>

I made the changes and I'm still getting that same error:

2.6.0
2.6.0
Error: Can not create the database connection

Here is my updated code:

'''
[SqliteTutorial.net](http://www.sqlitetutorial.net/sqlite-python/creating-database/)
'''
import sqlite3
from sqlite3 import Error

# Create database
def create_connection(db_example):
    ''' create a db connection to a sqlite db '''
    try:
        conn = sqlite3.connect(db_example) # write to hard disk
        # conn = sqlite3.connect(':memory:') # write to RAM only
        print(sqlite3.version)
    except Error as err:
        print('Error: ', err)
    # finally:
        # conn.close()
# if __name__ == '__main__':
    # create_connection('D:\\Software\db\sqlite\example.db')
    # create_connection() # use when writing to RAM only

# create tables
def create_table(conn, create_table_sql):
    """ create a table from the create_table_sql statement
    :param conn: Connection object
    :param create_table_sql: a CREATE TABLE statement
    :return: 
    """
    try:
        c = conn.cursor()
        c.execute(create_table_sql)
        print('connection to \'example.db\' established')
    except Error as err:
        print('Error: ', err)

# create main() function to create the named tables.
def main():
    database = 'D:\Software\db\sqlite\example.db'

    sql_create_csv01_table = '''CREATE TABLE IF NOT EXISTS csv01 (
        id integer PRIMARY KEY,
        name text NOT NULL,
        begin_date text,
        end-date text
    ); '''

    sql_create_csv02_table = ''' CREATE TABLE IF NOT EXISTS csv02 (
        id integer PRIMARY KEY,
        name text NOT NULL,
        priority integer,
        status_id integer NOT NULL,
        project_id integer NOT NULL,
        begin_date text NOT NULL
        end_date text NOT NULL
        FOREIGN KEY (project_id) REFERENCES csv01 (id)
    ); '''

# create a db connection
    conn = create_connection(database)
    if conn is not None:
        # create csv01 table
        create_table(conn, sql_create_csv01_table)
        # create csv02 table
        create_table(conn, sql_create_csv02_table)
    else:
        print('Error: Can not create the database connection')

if __name__ == '__main__':
    create_connection('D:\\Software\db\sqlite\example.db')
    main()

RE: Where is the error with this db creation code & the 'conn' variable? - Larz60+ - Nov-11-2018

try
database = 'D:\\Software\\db\\sqlite\\example.db'

or better:
database = 'D:/Software/db/sqlite/example.db'

RE: Where is the error with this db creation code & the 'conn' variable? - pcsailor - Nov-11-2018

No, still the same error.

I am creating the db correctly. I've been deleting it and when I run the code, it's recreated.

The error is with one of these two lines:

FOREIGN KEY (project_id) REFERENCES csv01 (id)
    ); '''
 
# create a db connection
    conn = create_connection(database)

Linter red-underlines the 'conn' and with the mouse-hover, the error states: '[pylint]E1111"Assigning to function call which doesn't return'

What does this mean? The conn var is used in the if statement.

RE: Where is the error with this db creation code & the 'conn' variable? - Larz60+ - Nov-11-2018

please (always) post error traceback without a trace of modification.

which IDE do you use?
does it have a debugger?
I'm in the middle of something else, so can't run your code at the moment.
If you can wait, I'll do it later, but if you run with debugger, the error will become obvious to you.
suggest VSCode for IDE, it has an excellent and simple to use debugger.

RE: Where is the error with this db creation code & the 'conn' variable? - pcsailor - Nov-11-2018

Yes, I'm using VSCode. I forgot about the debugger. I'll try it now.

Thanks Larz.

phil