CODEWITHSUNDEEP
pythonsqlpython-3.xsqlite
Adam Liam
Adam Liam

Reputation: 90

calling variables in function by another function with database

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?

Upvotes: 3

Views: 1797

Answers (1)

johnson lai
johnson lai

Reputation: 1036

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

Upvotes: 1

Related Questions