calling variables in function by another function with database

Question

I try to apply Don't Repeat Yourself concept in python.

import sqlite3



# Start connection and create cursor
def startdb():
    # 1. Create connection
    conn = sqlite3.connect("books.db")
    # 2. Create a cursor object
    cur = conn.cursor()

# Commit and close db
def closedb():
    # 4. Commit changes
    conn.commit()
    # 5. Close connections
    conn.close()

# Connect python to db
def connect():
    startdb()
    # 3. Create table if does not exist
    cur.execute("CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY, title text, author text, year integer, isbn integer)")
    closedb()

# Insert data to db
def insert(title,author,year,isbn):
    startdb()
    # SQL queries to insert
    cur.execute("INSERT INTO book VALUES (NULL,?,?,?,?)",(title,author,year,isbn))
    closedb()

# View all datas
def view():
    startdb()
    cur.execute("SELECT * FROM book")
    rows=cur.fetchall()
    conn.close()
    return rows



connect()
insert("The sea","John Tablet",1983,913123132)
print(view())

and apparently I got a Name Error

Traceback (most recent call last):
  File "backend.py", line 45, in <module>
    connect()
  File "backend.py", line 25, in connect
    cur.execute("CREATE TABLE IF NOT EXISTS b
ook (id INTEGER PRIMARY KEY, title text, auth
or text, isbn integer)")

NameError: name 'cur' is not defined

Based on my understanding, this means that startdb() function does not pass in the variable conn and cur

Based on what I search, I need to use a class with a __init__ function in it, Is there a better solution to use the startdb() and closedb() function?

Yes, you need to put these functions into a class. The `__init__` function would help in starting the db and recording the `cur` as an attribute of the current instance. If you are unfamiliar with all this, I would recommend reading up on classes / OOPS first. — shad0w_wa1k3r, Mar 08 '17 at 07:22
You don't need a class to do this, although it is a good use-case for a class. For the experience, I would implement this using functions and either passing around `curr` and `conn` objects or using global `curr` and `conn` objects. And then after you get that working, refractor the code into a class. — juanpa.arrivillaga, Mar 08 '17 at 07:27
@juanpa.arrivillaga u mean create something like `global curr = conn.cursor` ? — Adam Liam, Mar 08 '17 at 07:29
check this [Global variables](http://stackoverflow.com/questions/423379/using-global-variables-in-a-function-other-than-the-one-that-created-them#423596) — johnson lai, Mar 08 '17 at 07:31
@AdamLiam that isn't how you use the `global` statement. Really, I think you could get aways with simply putting a single line at the top of `startdb`: `global conn, cur`. Using global variables is usually not the best approach. It leads to hard to debug and reason about code. It is better to pass things as arguments to functions and return them from functions. A class would allow you to encapsulate all of this into one object. — juanpa.arrivillaga, Mar 08 '17 at 07:31

score 1 · Accepted Answer · answered Mar 08 '17 at 07:36

As stated by @juanpa.arrivillaga , you need a global statement. and you made a mistakes in your sqlite3 query, which is you forgot about the year column in the SQL create table query

import sqlite3



# Start connection and create cursor
def startdb():
    global conn, cur
    # 1. Create connection
    conn = sqlite3.connect("books.db")
    # 2. Create a cursor object
    cur = conn.cursor()

# Commit and close db
def closedb():
    # 4. Commit changes
    conn.commit()
    # 5. Close connections
    conn.close()

# Connect python to db
def connect():
    startdb()
    # 3. Create table if does not exist
    cur.execute("CREATE TABLE IF NOT EXISTS book (id INTEGER PRIMARY KEY, title text, author text,year integer, isbn integer)")
    closedb()

# Insert data to db
def insert(title,author,year,isbn):
    startdb()
    # SQL queries to insert
    cur.execute("INSERT INTO book VALUES (NULL,?,?,?,?)",(title,author,year,isbn))
    closedb()

# View all datas
def view():
    startdb()
    cur.execute("SELECT * FROM book")
    rows=cur.fetchall()
    conn.close()
    return rows



connect()
insert("The sea","John Tablet",1983,913123132)
print(view())

calling variables in function by another function with database

1 Answers1