sqlite3 table structure and db tables

[pythonNoob]

I'm working on a small program to keep track of library books and I'm doing the program in python3 and sqlite3. I'm not very far into the program at all and at the moment I just need to validate my sqlite3 table creation code is working. How can can I see a list of table for my sqlite3 database using python3, and I'd like to be able to see that table sctructure as well.

main.py

import db

db.setup_db()

db.conn

input("Press any key to continue")

db.py

import sqlite3
import os


def setup_db():
    directory = "C:\\Users\\me\\librarydb"

    if not os.path.exists(directory):
        print("directory does not exist")
        os.mkdir(directory)
        #conn = sqlite3.connect(r"C:\\Users\\me\\librarydb\\booksdb")
        build_db()

    else:
        print("directory exists")
        dbfile = "C:\\Users\\me\\librarydb\\booksdb"
        if not os.path.exists(dbfile):
            #conn = sqlite3.connect(r"C:\\Users\\me\\librarydb\\booksdb")
            build_db()

def build_db():
    conn = sqlite3.connect(r"C:\\Users\\me\\librarydb\\booksdb")
    c = conn.cursor()
    c.execute('''CREATE TABLE Books
                (book_id int,
                author_id int,
                book_title varchar(100),
                genre varchar(50),
                type varchar(30),
                cover char,
                price real)''')

    conn.commit()
    conn.close()

[woooee]

"conn=" should return an error if the table does not exist. Note that both conn and c (cursor) are garbage collected when the function exits so return them if you want to use them elsewhere http://www.tutorialspoint.com/python/pyt...ctions.htm
To print the table structure use PRAGMA

import sqlite3 as sqlite

table_name="C:/Users/me/librarydb/booksdb"
con = sqlite.connect(table_name)
cur = con.cursor()

meta = cur.execute("PRAGMA table_info(%s)" % (table_name))
for r in meta:
    print(r) 

[Larz60+]

The query:

select name from sqlite_master where type = 'table';

will shoq LL Tables

[pythonNoob]

"conn=" should return an error if the table does not exist. Note that both conn and c (cursor) are garbage collected when the function exits so return them if you want to use them elsewhere http://www.tutorialspoint.com/python/pyt...ctions.htm
To print the table structure use PRAGMA

import sqlite3 as sqlite

table_name="C:/Users/me/librarydb/booksdb"
con = sqlite.connect(table_name)
cur = con.cursor()

meta = cur.execute("PRAGMA table_info(%s)" % (table_name))
for r in meta:
    print(r) 

I thought conn = would open the database sqlite file if it exist or create the file if it doesn't?

[Larz60+]

slight error on database open:
open database with:

con = sqlite3.connect(table_name)

To get list of tables add following on line 7:

def show_tables(cur):
    sqlstr = "select name from sqlite_master where type = 'table';"
    tables = cur.execute(sqlstr)
    for row in tables:
        print(row)
show_tables(cur)

[woooee]

I thought conn = would open the database sqlite file if it exist or create the file if it doesn't?

I was referring to a table, not a file, my mistake. To create a table if it doesn't exist, use

cur.execute("CREATE TABLE IF NOT EXISTS " etc. 

---

slight error on database open:
open database with:
con = sqlite3.connect(table_name)

I import sqliteX as sqlite. Then, if there is a version change I only have to update the import statement.

[Larz60+]

I import sqliteX as sqlite. Then, if there is a version change I only have to update the import statement.
IP Address: Logged

OK, that's fine.
This function will still show all tables

[pythonNoob]

SOLVED