首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
社区首页 >问答首页 >使用Python导出SQLite模式

使用Python导出SQLite模式
EN

Stack Overflow用户
提问于 2021-04-02 00:11:39
回答 1查看 138关注 0票数 0

在SQLite命令行中,命令.schema可用于以SQL语法导出数据库模式,该导出可用于重建具有相同结构的数据库:

代码语言:javascript
代码运行次数:0
运行
复制
.output folderpath/schema.sql
.schema

将以下内容保存到名为“schema.sql”的文件中:

代码语言:javascript
代码运行次数:0
运行
复制
CREATE TABLE mytable (id INTEGER NOT NULL, name TEXT NOT NULL, date DATETIME, PRIMARY KEY (id), FOREIGN KEY (name) REFERENCES mytable2 (na ...

在没有自定义函数的情况下,使用Python的sqlite3库可以实现相同的输出.sql文件吗?在Stack Overflow上有几个类似标题的问题,但我没有找到任何实际上试图获得完整模式的问题(他们实际上正在寻找在输出中没有CREATE TABLE等语句的PRAGMA table_info )。

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2021-04-02 01:49:01

井。重写了上面的答案。这正是你所需要的吗?

代码语言:javascript
代码运行次数:0
运行
复制
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()

使用测试sqlite3数据库,我收到了以下内容:

代码语言:javascript
代码运行次数:0
运行
复制
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)
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/66908061

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档