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