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
– shad0w_wa1k3r, Commented Mar 8, 2017 at 7: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
– juanpa.arrivillaga, Commented Mar 8, 2017 at 7:27
@juanpa.arrivillaga u mean create something like global curr = conn.cursor ? — Adam Liam
– Adam Liam, Commented Mar 8, 2017 at 7:29
@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
– juanpa.arrivillaga, Commented Mar 8, 2017 at 7:31

johnson lai · Accepted Answer · 2017-03-08 07:36:58Z

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())

Collectives™ on Stack Overflow

calling variables in function by another function with database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related