List of tables, db schema, dump etc using the Python sqlite3 API
You can fetch the list of tables and schemata by querying the SQLITE_MASTER table:
sqlite> .tab
job snmptarget t1 t2 t3
sqlite> select name from sqlite_master where type = 'table';
job
t1
t2
snmptarget
t3
sqlite> .schema job
CREATE TABLE job (
id INTEGER PRIMARY KEY,
data VARCHAR
);
sqlite> select sql from sqlite_master where type = 'table' and name = 'job';
CREATE TABLE job (
id INTEGER PRIMARY KEY,
data VARCHAR
)
Using python3 and connected to sqlite3 I want to display tables that are in my database, . in .tables keeps giving error and \ is not helpful
You need to remove the "." from _sql because you are executing a sql command which does not accept "."
Using Python, export SQLite schema
Well. Rewritten the answer above. It that exactly what you need?
import sqlite3
dbname = 'chinook.db'
with sqlite3.connect(dbname) as con:
cursor = con.cursor()
cursor.execute('select sql from sqlite_master')
for r in cursor.fetchall():
print(r[0])
cursor.close()
With the test sqlite3 database I received the following:
CREATE TABLE "albums"
(
[AlbumId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[Title] NVARCHAR(160) NOT NULL,
[ArtistId] INTEGER NOT NULL,
FOREIGN KEY ([ArtistId]) REFERENCES "artists" ([ArtistId])
ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE TABLE sqlite_sequence(name,seq)
CREATE TABLE "artists"
(
[ArtistId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[Name] NVARCHAR(120)
)
CREATE TABLE "customers"
(
[CustomerId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[FirstName] NVARCHAR(40) NOT NULL,
[LastName] NVARCHAR(20) NOT NULL,
[Company] NVARCHAR(80),
[Address] NVARCHAR(70),
[City] NVARCHAR(40),
[State] NVARCHAR(40),
[Country] NVARCHAR(40),
[PostalCode] NVARCHAR(10),
[Phone] NVARCHAR(24),
[Fax] NVARCHAR(24),
[Email] NVARCHAR(60) NOT NULL,
[SupportRepId] INTEGER,
FOREIGN KEY ([SupportRepId]) REFERENCES "employees" ([EmployeeId])
ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE TABLE "employees"
(
[EmployeeId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[LastName] NVARCHAR(20) NOT NULL,
[FirstName] NVARCHAR(20) NOT NULL,
[Title] NVARCHAR(30),
[ReportsTo] INTEGER,
[BirthDate] DATETIME,
[HireDate] DATETIME,
[Address] NVARCHAR(70),
[City] NVARCHAR(40),
[State] NVARCHAR(40),
[Country] NVARCHAR(40),
[PostalCode] NVARCHAR(10),
[Phone] NVARCHAR(24),
[Fax] NVARCHAR(24),
[Email] NVARCHAR(60),
FOREIGN KEY ([ReportsTo]) REFERENCES "employees" ([EmployeeId])
ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE TABLE "genres"
(
[GenreId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[Name] NVARCHAR(120)
)
CREATE TABLE "invoices"
(
[InvoiceId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[CustomerId] INTEGER NOT NULL,
[InvoiceDate] DATETIME NOT NULL,
[BillingAddress] NVARCHAR(70),
[BillingCity] NVARCHAR(40),
[BillingState] NVARCHAR(40),
[BillingCountry] NVARCHAR(40),
[BillingPostalCode] NVARCHAR(10),
[Total] NUMERIC(10,2) NOT NULL,
FOREIGN KEY ([CustomerId]) REFERENCES "customers" ([CustomerId])
ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE TABLE "invoice_items"
(
[InvoiceLineId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[InvoiceId] INTEGER NOT NULL,
[TrackId] INTEGER NOT NULL,
[UnitPrice] NUMERIC(10,2) NOT NULL,
[Quantity] INTEGER NOT NULL,
FOREIGN KEY ([InvoiceId]) REFERENCES "invoices" ([InvoiceId])
ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY ([TrackId]) REFERENCES "tracks" ([TrackId])
ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE TABLE "media_types"
(
[MediaTypeId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[Name] NVARCHAR(120)
)
CREATE TABLE "playlists"
(
[PlaylistId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[Name] NVARCHAR(120)
)
CREATE TABLE "playlist_track"
(
[PlaylistId] INTEGER NOT NULL,
[TrackId] INTEGER NOT NULL,
CONSTRAINT [PK_PlaylistTrack] PRIMARY KEY ([PlaylistId], [TrackId]),
FOREIGN KEY ([PlaylistId]) REFERENCES "playlists" ([PlaylistId])
ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY ([TrackId]) REFERENCES "tracks" ([TrackId])
ON DELETE NO ACTION ON UPDATE NO ACTION
)
None
CREATE TABLE "tracks"
(
[TrackId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[Name] NVARCHAR(200) NOT NULL,
[AlbumId] INTEGER,
[MediaTypeId] INTEGER NOT NULL,
[GenreId] INTEGER,
[Composer] NVARCHAR(220),
[Milliseconds] INTEGER NOT NULL,
[Bytes] INTEGER,
[UnitPrice] NUMERIC(10,2) NOT NULL,
FOREIGN KEY ([AlbumId]) REFERENCES "albums" ([AlbumId])
ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY ([GenreId]) REFERENCES "genres" ([GenreId])
ON DELETE NO ACTION ON UPDATE NO ACTION,
FOREIGN KEY ([MediaTypeId]) REFERENCES "media_types" ([MediaTypeId])
ON DELETE NO ACTION ON UPDATE NO ACTION
)
CREATE INDEX [IFK_AlbumArtistId] ON "albums" ([ArtistId])
CREATE INDEX [IFK_CustomerSupportRepId] ON "customers" ([SupportRepId])
CREATE INDEX [IFK_EmployeeReportsTo] ON "employees" ([ReportsTo])
CREATE INDEX [IFK_InvoiceCustomerId] ON "invoices" ([CustomerId])
CREATE INDEX [IFK_InvoiceLineInvoiceId] ON "invoice_items" ([InvoiceId])
CREATE INDEX [IFK_InvoiceLineTrackId] ON "invoice_items" ([TrackId])
CREATE INDEX [IFK_PlaylistTrackTrackId] ON "playlist_track" ([TrackId])
CREATE INDEX [IFK_TrackAlbumId] ON "tracks" ([AlbumId])
CREATE INDEX [IFK_TrackGenreId] ON "tracks" ([GenreId])
CREATE INDEX [IFK_TrackMediaTypeId] ON "tracks" ([MediaTypeId])
CREATE TABLE sqlite_stat1(tbl,idx,stat)
How can I list the tables in a SQLite database file that was opened with ATTACH?
The .tables
, and .schema
"helper" functions don't look into ATTACHed databases: they just query the SQLITE_MASTER
table for the "main" database. Consequently, if you used
ATTACH some_file.db AS my_db;
then you need to do
SELECT name FROM my_db.sqlite_master WHERE type='table';
Note that temporary tables don't show up with .tables
either: you have to list sqlite_temp_master
for that:
SELECT name FROM sqlite_temp_master WHERE type='table';
List of tables, db schema, dump etc using the Python sqlite3 API
You can fetch the list of tables and schemata by querying the SQLITE_MASTER table:
sqlite> .tab
job snmptarget t1 t2 t3
sqlite> select name from sqlite_master where type = 'table';
job
t1
t2
snmptarget
t3
sqlite> .schema job
CREATE TABLE job (
id INTEGER PRIMARY KEY,
data VARCHAR
);
sqlite> select sql from sqlite_master where type = 'table' and name = 'job';
CREATE TABLE job (
id INTEGER PRIMARY KEY,
data VARCHAR
)
In what way can I know the tables in a .db file using Python SQLite3?
The name of your database is not test
(that would be part of the file name) but main
:
cu.execute("SELECT * FROM main.sqlite_master WHERE type = 'table'")
And main
is the default database name, so you do not even need to specifiy it:
cu.execute("SELECT * FROM sqlite_master WHERE type = 'table'")
Related Topics
Shuffle an Array with Python, Randomize Array Item Order with Python
Python Postgres Psycopg2 Threadedconnectionpool Exhausted
Getting the Docstring from a Function
Easy Pretty Printing of Floats
Classification Using Movie Review Corpus in Nltk/Python
Python Ctypes Issue on Different Oses
What Is the Reason for Having '//' in Python
Call Int() Function on Every List Element
How to Patch a Python Decorator Before It Wraps a Function
How to Check If One Two-Dimensional Numpy Array Contains a Specific Pattern of Values Inside It
Why Are Str.Count('') and Len(Str) Giving Different Output
Is There a "Not Equal" Operator in Python
What's the Difference Between _Builtin_ and _Builtins_
Difference Between "Findall" and "Find_All" in Beautifulsoup
Cannot Return Results from Stored Procedure Using Python Cursor