+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: sqlite3 table structure and db tables (/thread-10161.html)
sqlite3 table structure and db tables - pythonNoob - May-15-2018
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.pyimport 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()
RE: sqlite3 table structure and db tables - woooee - May-15-2018
"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/python_functions.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)
RE: sqlite3 table structure and db tables - Larz60+ - May-15-2018
The query:
select name from sqlite_master where type = 'table';will shoq LL Tables
RE: sqlite3 table structure and db tables - pythonNoob - May-15-2018
(May-15-2018, 06:43 PM)woooee Wrote: "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/python_functions.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?
RE: sqlite3 table structure and db tables - Larz60+ - May-15-2018
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)
RE: sqlite3 table structure and db tables - woooee - May-16-2018
Quote: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. Quote:slight error on database open:I import sqliteX as sqlite. Then, if there is a version change I only have to update the import statement.
open database with:
con = sqlite3.connect(table_name)
RE: sqlite3 table structure and db tables - Larz60+ - May-16-2018
Quote:I import sqliteX as sqlite. Then, if there is a version change I only have to update the import statement.OK, that's fine.
IP Address: Logged
This function will still show all tables
RE: sqlite3 table structure and db tables - pythonNoob - May-16-2018
SOLVED