Как получить бд c forms
Данное руководство устарело. Актуальное руководство: по ADO.NET и работе с базами данных в .NET 6
Последнее обновление: 31.10.2015
Ранее мы рассмотрели, как удобно загружать данные в приложении Windows Forms в элемент DataGridView через DataSet. Теперь определим полнофункциональную форму, через которую мы сможем производить все стандартные CRUD операции в базе данных.
Итак, определим форму, на которой будет элемент DataGridView и три кнопки для добавления, удаления и сохранения изменений. Форма в итоге будет выглядеть примерно следующим образом:
Код формы будет выглядеть следующим образом:
using System; using System.Data; using System.Windows.Forms; using System.Data.SqlClient; namespace AdoNetWinFormsApp < public partial class Form1 : Form < DataSet ds; SqlDataAdapter adapter; SqlCommandBuilder commandBuilder; string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True"; string sql = "SELECT * FROM Users"; public Form1() < InitializeComponent(); dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; dataGridView1.AllowUserToAddRows = false; using (SqlConnection connection = new SqlConnection(connectionString)) < connection.Open(); adapter = new SqlDataAdapter(sql, connection); ds = new DataSet(); adapter.Fill(ds); dataGridView1.DataSource = ds.Tables[0]; // делаем недоступным столбец id для изменения dataGridView1.Columns["Id"].ReadOnly = true; >> // кнопка добавления private void addButton_Click(object sender, EventArgs e) < DataRow row = ds.Tables[0].NewRow(); // добавляем новую строку в DataTable ds.Tables[0].Rows.Add(row); >// кнопка удаления private void deleteButton_Click(object sender, EventArgs e) < // удаляем выделенные строки из dataGridView1 foreach(DataGridViewRow row in dataGridView1.SelectedRows) < dataGridView1.Rows.Remove(row); >> // кнопка сохранения private void saveButton_Click(object sender, EventArgs e) < using (SqlConnection connection = new SqlConnection(connectionString)) < connection.Open(); adapter = new SqlDataAdapter(sql, connection); commandBuilder = new SqlCommandBuilder(adapter); adapter.InsertCommand = new SqlCommand("sp_CreateUser", connection); adapter.InsertCommand.CommandType = CommandType.StoredProcedure; adapter.InsertCommand.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 50, "Name")); adapter.InsertCommand.Parameters.Add(new SqlParameter("@age", SqlDbType.Int, 0, "Age")); SqlParameter parameter = adapter.InsertCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id"); parameter.Direction = ParameterDirection.Output; adapter.Update(ds); >> > >
Здесь для добавления объекта мы будем обращаться к хранимой процедуре sp_CreateUser, которая была добавлена в базу данных в прошлой теме.
В конструкторе данные загружаются в DataSet, первая таблица которого устанавливается в качестве источника данных для dataGridView1:
dataGridView1.DataSource = ds.Tables[0];
Также в конструкторе устанавливается полное выделение строки и запрет на ручное добавление новых строк:
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; dataGridView1.AllowUserToAddRows = false;
В обработчике кнопки добавления создается новая строка, которая добавляется в таблицу объекта DataSet. И так как мы ранее установили привязку к источнику данных, то автоматически новая строка также будет добавляться и в dataGridView1:
private void addButton_Click(object sender, EventArgs e) < DataRow row = ds.Tables[0].NewRow(); // добавляем новую строку в DataTable ds.Tables[0].Rows.Add(row); >
В обработчике кнопки удаления удаляются выделенные строки в dataGridView1. Опять же в силу привязки к источнику данных будет также происходить удаление и из таблицы в DataSet:
private void deleteButton_Click(object sender, EventArgs e) < foreach(DataGridViewRow row in dataGridView1.SelectedRows) < dataGridView1.Rows.Remove(row); >>
Для обновления на не нужна никакая кнопка, так как мы можем нажать на любую ячейку таблицы (кроме заблокированного для изменения столбца Id) и изменить в ней данные. Однако сами по себе добавление новой строки, удаление строк, изменение ячеек ни как автоматически не отразятся на базе данных. И чтобы бд синхронизировалась, пользователю надо будет нажать на кнопку сохранения, обработчик которой выглядит следующим образом:
private void saveButton_Click(object sender, EventArgs e) < using (SqlConnection connection = new SqlConnection(connectionString)) < connection.Open(); adapter = new SqlDataAdapter(sql, connection); commandBuilder = new SqlCommandBuilder(adapter); adapter.InsertCommand = new SqlCommand("sp_CreateUser", connection); adapter.InsertCommand.CommandType = CommandType.StoredProcedure; adapter.InsertCommand.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 50, "Name")); adapter.InsertCommand.Parameters.Add(new SqlParameter("@age", SqlDbType.Int, 0, "Age")); SqlParameter parameter = adapter.InsertCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id"); parameter.Direction = ParameterDirection.Output; adapter.Update(ds); >>
Как в прошлой теме здесь устанавливается у адаптера команда на добавление InsertCommand и затем вызывается метод Update() . В итоге мы можем добавить несколько строк, удалить, изменить, и потом один раз мы нажмем на кнопку, и все изменения будут применены к базе данных.
Метаданные (metadata)¶
Для описания структуры базы данных используют 3 основных класса:
- sqlalchemy.schema.Table — таблица
- sqlalchemy.schema.Column — поле таблицы
- sqlalchemy.schema.MetaData — список таблиц
А также типы полей описанные в модуле sqlalchemy.types :
- sqlalchemy.types.Integer
- sqlalchemy.types.String
- sqlalchemy.types.Text
- И другие
1 2 3 4 5 6 7 8 9 10
from sqlalchemy import MetaData from sqlalchemy import Table, Column from sqlalchemy import Integer, String metadata = MetaData() user_table = Table('user', metadata, Column('id', Integer, primary_key=True), Column('name', String), Column('fullname', String) )
Создание таблиц таким образом описывают структуру базы данных независимо от объектно-реляционного отображения. Объект sqlalchemy.schema.Table представляет имя и другие атрибуты текущей таблицы. Его коллекция объектов Column представляет информацию об именах и типах для определенных столбцов таблицы.
Дополнительно в описание схемы базы данных можно включить внешние ключи, индексы, последовательности и т.д.:
- sqlalchemy.schema.ForeignKey — внешние ключи
- sqlalchemy.schema.Index — индексы
- sqlalchemy.schema.Sequence — последовательности
Вся информация о таблицах базы данных складывается в объект класса sqlalchemy.schema.MetaData . Получить список таблиц можно при помощи атрибута sqlalchemy.schema.MetaData.tables .
Базовые объекты пакета sqlalchemy.schema
Объекты Table и Column уникальны по сравнению со всеми остальными объектами из пакета для работы со схемами, так как они используют двойное наследование от объектов из пакетов sqlalchemy.schema и sqlalchemy.sql.expression , работая не только как конструкции уровня обработки схем, но также и как синтаксические единицы языка для создания выражений SQL. Это отношение проиллюстрировано на sqlalchemy_table_crossover .
Двойная жизнь объектов Table и Column
Table¶
1 2 3 4 5
>>> user_table Table('user', MetaData(bind=None), Column('id', Integer(), table=, primary_key=True, nullable=False), Column('name', String(), table=), Column('fullname', String(), table=), schema=None)
Имя таблицы¶
>>> user_table.name 'user'
Поля таблицы¶
Поля таблицы хранятся в списке sqlalchemy.schema.Table.columns или его более коротком варианте sqlalchemy.schema.Table.c .
1 2 3 4 5 6 7 8 9 10
>>> user_table.c >>> print(user_table.c) ['user.id', 'user.name', 'user.fullname'] >>> user_table.c.id Column('id', Integer(), table=, primary_key=True, nullable=False) >>> user_table.c.name Column('name', String(), table=) >>> user_table.c.fullname Column('fullname', String(), table=)
Сами поля тоже содержат информацию о себе, например в атрибутах name и type .
1 2 3 4 5 6 7 8 9 10 11 12 13
>>> user_table.c.id Column('id', Integer(), table=, primary_key=True, nullable=False) >>> user_table.c.id.name 'id' >>> user_table.c.id.type Integer() >>> >>> user_table.c.name Column('name', String(), table=) >>> user_table.c.name.name 'name' >>> user_table.c.name.type String()
Первичные ключи¶
Первичные ключи таблицы можно получить при помощи атрибута sqlalchemy.schema.Table.primary_key
1 2 3 4 5 6
>>> user_table.primary_key PrimaryKeyConstraint(Column('id', Integer(), table=, primary_key=True, nullable=False)) >>> print(user_table.primary_key.columns) ['user.id'] >>> user_table.primary_key.columns.id Column('id', Integer(), table=, primary_key=True, nullable=False)
SQL выражения¶
Объект класса sqlalchemy.schema.Table является частью механизма SQL выражений в sqlalchemy и содержит в себе множество вспомогательных методов для построения SQL запросов:
- sqlalchemy.schema.Table.select()
- sqlalchemy.schema.Table.delete()
- sqlalchemy.schema.Table.insert()
- sqlalchemy.schema.Table.update()
- sqlalchemy.schema.Table.join()
- sqlalchemy.schema.Table.outerjoin()
1 2 3 4 5 6 7 8 9 10 11 12
>>> print(user_table.select()) SELECT "user".id, "user".name, "user".fullname FROM "user" >>> print(user_table.delete()) DELETE FROM "user" >>> print(user_table.insert()) INSERT INTO "user" (id, name, fullname) VALUES (:id, :name, :fullname) >>> print(user_table.update()) UPDATE "user" SET name=:name, fullname=:fullname
Создание таблиц¶
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
>>> from sqlalchemy import create_engine >>> engine = create_engine("sqlite://") >>> metadata.create_all(engine) [SQL]: PRAGMA table_info("user") [SQL]: () [SQL]: CREATE TABLE user ( id INTEGER NOT NULL, name VARCHAR, fullname VARCHAR, PRIMARY KEY (id) ) [SQL]: () [SQL]: COMMIT
Для создания, удаления одной таблицы необходимо использовать методы класса sqlalchemy.schema.Table :
- sqlalchemy.schema.Table.drop()
- sqlalchemy.schema.Table.create()
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
>>> user_table.drop(engine) [SQL]: DROP TABLE user [SQL]: () [SQL]: COMMIT >>> user_table.create(engine) [SQL]: CREATE TABLE user ( id INTEGER NOT NULL, name VARCHAR, fullname VARCHAR, PRIMARY KEY (id) ) [SQL]: () [SQL]: COMMIT
Типы полей¶
Типы полей описаны в модуле sqlalchemy.types :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
>>> from sqlalchemy import String, Numeric, DateTime, Enum >>> fancy_table = Table('fancy', metadata, . Column('key', String(50), primary_key=True), . Column('timestamp', DateTime), . Column('amount', Numeric(10, 2)), . Column('type', Enum('a', 'b', 'c')) . ) >>> fancy_table.create(engine) [SQL]: CREATE TABLE fancy ( "key" VARCHAR(50) NOT NULL, timestamp DATETIME, amount NUMERIC(10, 2), type VARCHAR(1), PRIMARY KEY ("key"), CHECK (type IN ('a', 'b', 'c')) ) [SQL]: () [SQL]: COMMIT
Огрничения и Индексы¶
Индексы создаются при помощи параметра index в классе sqlalchemy.schema.Column или при помощи объекта класса sqlalchemy.schema.Index .
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
>>> meta = MetaData() >>> mytable = Table('mytable', meta, . # an indexed column, with index "ix_mytable_col1" . Column('col1', Integer, index=True), . . # a uniquely indexed column with index "ix_mytable_col2" . Column('col2', Integer, index=True, unique=True), . . Column('col3', Integer), . Column('col4', Integer), . . Column('col5', Integer), . Column('col6', Integer), . ) >>> from sqlalchemy import Index >>> Index('idx_col34', mytable.c.col3, mytable.c.col4) Index('idx_col34', Column('col3', Integer(), table=), Column('col4', Integer(), table=)) >>> Index('myindex', mytable.c.col5, mytable.c.col6, unique=True) Index('myindex', Column('col5', Integer(), table=), Column('col6', Integer(), table=), unique=True) >>> mytable.create(engine) [SQL]: CREATE TABLE mytable ( col1 INTEGER, col2 INTEGER, col3 INTEGER, col4 INTEGER, col5 INTEGER, col6 INTEGER ) [SQL]: () [SQL]: COMMIT [SQL]: CREATE UNIQUE INDEX myindex ON mytable (col5, col6) [SQL]: () [SQL]: COMMIT [SQL]: CREATE INDEX idx_col34 ON mytable (col3, col4) [SQL]: () [SQL]: COMMIT [SQL]: CREATE INDEX ix_mytable_col1 ON mytable (col1) [SQL]: () [SQL]: COMMIT [SQL]: CREATE UNIQUE INDEX ix_mytable_col2 ON mytable (col2) [SQL]: () [SQL]: COMMIT
Внешние ключи¶
Внешние ключи обычно используют как ссылки на первичные ключи. Для описания внешнего ключа в схеме нужно использовать класс sqlalchemy.schema.ForeignKey .
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
>>> from sqlalchemy import ForeignKey >>> addresses_table = Table('address', metadata, . Column('id', Integer, primary_key=True), . Column('email_address', String(100), nullable=False), . Column('user_id', Integer, ForeignKey('user.id')) . ) >>> addresses_table.create(engine) [SQL]: CREATE TABLE address ( id INTEGER NOT NULL, email_address VARCHAR(100) NOT NULL, user_id INTEGER, PRIMARY KEY (id), FOREIGN KEY(user_id) REFERENCES user (id) ) [SQL]: () [SQL]: COMMIT
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57
>>> from sqlalchemy import Unicode, UnicodeText, DateTime >>> from sqlalchemy import ForeignKeyConstraint >>> story_table = Table('story', metadata, . Column('story_id', Integer, primary_key=True), . Column('version_id', Integer, primary_key=True), . Column('headline', Unicode(100), nullable=False), . Column('body', UnicodeText) . ) >>> published_table = Table('published', metadata, . Column('pub_id', Integer, primary_key=True), . Column('pub_timestamp', DateTime, nullable=False), . Column('story_id', Integer), . Column('version_id', Integer), . ForeignKeyConstraint( . ['story_id', 'version_id'], . ['story.story_id', 'story.version_id']) . ) >>> metadata.create_all(engine) [SQL]: PRAGMA table_info("user") [SQL]: () [SQL]: PRAGMA table_info("fancy") [SQL]: () [SQL]: PRAGMA table_info("story") [SQL]: () [SQL]: PRAGMA table_info("published") [SQL]: () [SQL]: PRAGMA table_info("address") [SQL]: () [SQL]: CREATE TABLE story ( story_id INTEGER NOT NULL, version_id INTEGER NOT NULL, headline VARCHAR(100) NOT NULL, body TEXT, PRIMARY KEY (story_id, version_id) ) [SQL]: () [SQL]: COMMIT [SQL]: CREATE TABLE published ( pub_id INTEGER NOT NULL, pub_timestamp DATETIME NOT NULL, story_id INTEGER, version_id INTEGER, PRIMARY KEY (pub_id), FOREIGN KEY(story_id, version_id) REFERENCES story (story_id, version_id) ) [SQL]: () [SQL]: COMMIT
Рефлексия¶
- https://ru.wikipedia.org/wiki/Отражение_(программирование)
- http://docs.sqlalchemy.org/en/latest/core/reflection.html
В информатике отражение или рефлексия (холоним интроспекции, англ. reflection) означает процесс, во время которого программа может отслеживать и модифицировать собственную структуру и поведение во время выполнения.
В SQLAlchemy рефлексия означает автоматическую загрузку схемы таблицы из уже существующей базы данных. Реализуется через параметр autoload в конструкторе класса sqlalchemy.schema.Table.autoload .
1 2 3 4 5 6 7 8 9 10 11 12
>>> metadata2 = MetaData() >>> user_reflected = Table('user', metadata2, autoload=True, autoload_with=engine) [SQL]: PRAGMA table_info("user") [SQL]: () [SQL]: PRAGMA foreign_key_list("user") [SQL]: () [SQL]: PRAGMA index_list("user") [SQL]: () >>> print(user_reflected.c) ['user.id', 'user.name', 'user.fullname']
Для отражения всех таблиц существует метод sqlalchemy.schema.MetaData.reflect() .
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
>>> meta = MetaData() >>> meta.reflect(bind=engine) [SQL]: SELECT name FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type='table' ORDER BY name [SQL]: () [SQL]: PRAGMA table_info("address") [SQL]: () [SQL]: PRAGMA foreign_key_list("address") [SQL]: () [SQL]: PRAGMA table_info("user") [SQL]: () [SQL]: PRAGMA foreign_key_list("user") [SQL]: () [SQL]: PRAGMA index_list("user") [SQL]: () [SQL]: PRAGMA index_list("address") [SQL]: () [SQL]: PRAGMA table_info("fancy") [SQL]: () [SQL]: PRAGMA foreign_key_list("fancy") [SQL]: () [SQL]: PRAGMA index_list("fancy") [SQL]: () [SQL]: PRAGMA table_info("mytable") [SQL]: () [SQL]: PRAGMA foreign_key_list("mytable") [SQL]: () [SQL]: PRAGMA index_list("mytable") [SQL]: () [SQL]: PRAGMA index_info("ix_mytable_col2") [SQL]: () [SQL]: PRAGMA index_info("ix_mytable_col1") [SQL]: () [SQL]: PRAGMA index_info("idx_col34") [SQL]: () [SQL]: PRAGMA index_info("myindex") [SQL]: () [SQL]: PRAGMA table_info("published") [SQL]: () [SQL]: PRAGMA foreign_key_list("published") [SQL]: () [SQL]: PRAGMA table_info("story") [SQL]: () [SQL]: PRAGMA foreign_key_list("story") [SQL]: () [SQL]: PRAGMA index_list("story") [SQL]: () [SQL]: PRAGMA index_list("published") [SQL]: () >>> new_user_table = meta.tables['user'] >>> new_fancy_table = meta.tables['fancy']
Интроспекция¶
Интроспекция (англ. type introspection) в программировании — возможность в некоторых объектно-ориентированных языках определить тип и структуру объекта во время выполнения программы. В SQLAlchemy возможность анализа схемы базы данных. Для анализа используется функция sqlalchemy.inspection.inspect() .
Список таблиц¶
1 2 3 4 5 6 7 8
>>> from sqlalchemy import inspect >>> inspector = inspect(engine) >>> >>> inspector.get_table_names() [SQL]: SELECT name FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type='table' ORDER BY name [SQL]: () [u'address', u'fancy', u'mytable', u'published', u'story', u'user']
Информация о полях таблицы¶
1 2 3 4 5
>>> inspector.get_columns('address') [SQL]: PRAGMA table_info("address") [SQL]: () [, , ]
Внешние ключи¶
1 2 3 4 5
>>> inspector.get_foreign_keys('address') [SQL]: PRAGMA foreign_key_list("address") [SQL]: () []
Полный пример¶
2.sqlalchemy/2.metadata.py ¶
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155
# ## title:: Schema and MetaData # The structure of a relational schema is represented in Python # using MetaData, Table, and other objects. from sqlalchemy import MetaData from sqlalchemy import Table, Column from sqlalchemy import Integer, String metadata = MetaData() user_table = Table('user', metadata, Column('id', Integer, primary_key=True), Column('name', String), Column('fullname', String) ) # Table provides a single point of information regarding # the structure of a table in a schema. user_table.name # The .c. attribute of Table is an associative array # of Column objects, keyed on name. user_table.c.name # It's a bit like a Python dictionary but not totally. print(user_table.c) # Column itself has information about each Column, such as # name and type user_table.c.name.name user_table.c.name.type # Table has other information available, such as the collection # of columns which comprise the table's primary key. user_table.primary_key # The Table object is at the core of the SQL expression # system - this is a quick preview of that. print(user_table.select()) # Table and MetaData objects can be used to generate a schema # in a database. from sqlalchemy import create_engine engine = create_engine("sqlite://") metadata.create_all(engine) # Types are represented using objects such as String, Integer, # DateTime. These objects can be specified as "class keywords", # or can be instantiated with arguments. from sqlalchemy import String, Numeric, DateTime, Enum fancy_table = Table('fancy', metadata, Column('key', String(50), primary_key=True), Column('timestamp', DateTime), Column('amount', Numeric(10, 2)), Column('type', Enum('a', 'b', 'c')) ) fancy_table.create(engine) # table metadata also allows for constraints and indexes. # ForeignKey is used to link one column to a remote primary # key. from sqlalchemy import ForeignKey addresses_table = Table('address', metadata, Column('id', Integer, primary_key=True), Column('email_address', String(100), nullable=False), Column('user_id', Integer, ForeignKey('user.id')) ) addresses_table.create(engine) # ForeignKey is a shortcut for ForeignKeyConstraint, # which should be used for composite references. from sqlalchemy import Unicode, UnicodeText, DateTime from sqlalchemy import ForeignKeyConstraint story_table = Table('story', metadata, Column('story_id', Integer, primary_key=True), Column('version_id', Integer, primary_key=True), Column('headline', Unicode(100), nullable=False), Column('body', UnicodeText) ) published_table = Table('published', metadata, Column('pub_id', Integer, primary_key=True), Column('pub_timestamp', DateTime, nullable=False), Column('story_id', Integer), Column('version_id', Integer), ForeignKeyConstraint( ['story_id', 'version_id'], ['story.story_id', 'story.version_id']) ) # create_all() by default checks for tables existing already metadata.create_all(engine) # ## title:: Exercises # 1. Write a Table construct corresponding to this CREATE TABLE # statement. # # CREATE TABLE network ( # network_id INTEGER PRIMARY KEY, # name VARCHAR(100) NOT NULL, # created_at DATETIME NOT NULL, # owner_id INTEGER, # FOREIGN KEY owner_id REFERENCES user(id) # ) # # 2. Then emit metadata.create_all(), which will # emit CREATE TABLE for this table (it will skip # those that already exist). # # The necessary types are imported here: # ## title:: Reflection # 'reflection' refers to loading Table objects based on # reading from an existing database. metadata2 = MetaData() user_reflected = Table('user', metadata2, autoload=True, autoload_with=engine) print(user_reflected.c) # Information about a database at a more specific level is available # using the Inspector object. from sqlalchemy import inspect inspector = inspect(engine) # the inspector provides things like table names: inspector.get_table_names() # column information inspector.get_columns('address') # constraints inspector.get_foreign_keys('address') # ## title:: Exercises # # 1. Using 'metadata2', reflect the "network" table in the same way # we just did 'user', then display the columns (or bonus, display # just the column names) # # 2. Using "inspector", print a list of all table names that # include a column called "story_id" #
Previous: Соединение (engine) Next: SQL выражения
© Copyright 2020, Кафедра Интеллектуальных Информационных Технологий ИнФО УрФУ. Created using Sphinx 1.7.6.
Как работать с модулем JSON в Python
JSON часто применяют, когда разрабатывают API и веб-приложения.
Анастасия Хамидулина
Автор статьи
25 апреля 2022 в 18:54
Основной принцип работы интернета — обмен данными. Они бывают разных видов, например файл, строка или число. Есть структура данных, которая позволяет быстро и просто воссоздавать объекты и обмениваться этими данными по сети, — JSON.
JSON в Python
JSON — это строка со словарем. Она представлена в виде байтовой последовательности. Вы можете отправить ее по сети приложению, а в нём воссоздать полученную структуру в объекты языка.
�� Пример JSON:
Сериализация и десериализация
В Python есть множество библиотек, чтобы работать с JSON, но мы рассмотрим встроенную библиотеку JSON Python. Она позволяет приводить любые структуры данных к JSON-объекту — вплоть до пользовательских классов. А из него получать совместимую для работы в Python сущность — объект языка.
Полностью разобраться в Python вы сможете на курсе «Python-разработчик». Преподаватели объяснят все нюансы доступным языком, а вы выполните практические задания. Например, разработаете виджет банковских операций. После обучения у вас будет готовое портфолио и диплом о профессиональной переподготовке.
Упаковка объектов в байтовую последовательность называется сериализацией. А распаковка байтов в объекты языка программирования, приведение последовательности назад к типам и структурам, — десериализацией.
Python-разработчик: новая работа через 9 месяцев
Получится, даже если у вас нет опыта в IT
В байты данные необходимо переводить, чтобы отправлять их по сети или локально другому приложению, так как иной формат передать невозможно. Вот так преобразовывают данные из объектов Python в JSON и обратно:
>>> # импортируем библиотеку >>> import json >>> >>> # объявляем переменные >>> string = "Some test string" >>> integer = 211 >>> array = [1, 2, 3, 4, 5] >>> >>> # создаем словарь >>> mydict = >>> >>> # сериализуем его в JSON-структуру, как строку >>> x = json.dumps(mydict) >>> x '' >>> >>> # проводим десериализацию JSON-объекта >>> y = json.loads(x) >>> y >>> >>> y["title"] 'Some test string' >>>
Функции
Dumps позволяет создать JSON-строку из переданного в нее объекта. Loads — преобразовать строку назад в объекты языка.
Dump и load используют, чтобы сохранить результат в файл или воссоздать объект. Работают они схожим образом, но требуют передачи специального объекта для работы с файлом — filehandler.
>>> import json # импортируем библиотеку >>> >>> # создаем filehandler с помощью контекстного менеджера >>> with open("data.json", "w") as fh: . json.dump([1, 2, 3, 4, 5], fh) # записываем структуру в файл . >>> >>> # открываем тот же файл, но уже на чтение >>> with open("data.json", "r") as fh: . json.load(fh) # загружаем структуру из файла . [1, 2, 3, 4, 5] >>>
https://sky.pro/media/modul-requests-v-python/
Как работать с пользовательскими объектами
Пользовательские классы не относятся к JSON-сериализуемым. Это значит, что просто применить к ним функции dumps, loads или dump и load не получится:
>>> # создаем пользовательский класс >>> class Test: . def __init__(self, title, body): . self.title = title . self.body = body . >>> # создаем экземпляр класса >>> t = Test("Some string", "Here is a bit more text, but still isn't enough") >>> >>> # пытаемся сериализовать его в JSON, но. >>> json.dumps(t) >>> # получаем ошибку TypeError, что класс несериализуем >>>
Решить эту проблему можно тремя способами.
�� Написать функцию
Чтобы сериализовать пользовательский объект в JSON-структуру данных, нужен аргумент default. Указывайте вызываемый объект, то есть функцию или статический метод.
Чтобы получить аргументы класса с их значениями, нужна встроенная функция __dict__, потому что любой класс — это словарь со ссылками на значения по ключу.
Чтобы сериализовать аргументы класса и их значения в JSON, напишите функцию:
>>> # используем анонимную функцию (лямбду), которая >>> # в качестве сериализуемых данных указывает полученный __dict__ объекта >>> json.dumps(t, default=lambda x: x.__dict__) '' >>>
Но можно создать отдельную функцию и указать ее в качестве аргумента:
>>> def to_json(obj): . if isinstance(obj, Test): . result = obj.__dict__ . result["className"] = obj.__class__.__name__ . return result . >>> json.dumps(t, default=to_json) '' >>>
❗ Мы добавили название класса в получаемую структуру. Такой подход позволяет безошибочно понять: сущность какого класса нужно десериализовать в объект.
Более подробно писать функции вы научитесь на курсе «Python-разработчик». А еще сами сделаете сервис проверки файлов с возможностью регистрации, отправки уведомлений и формирования отчета о задачах.
�� Создать расширение классов
Такого же результата добьетесь, если примените расширения специальных классов библиотеки:
>>> class TestEncoder(json.JSONEncoder): . def default(self, o): . return . >>> x = json.dumps(t, cls=TestEncoder) >>> x '' >>> >>> y = json.loads(x) >>> y >>> >>> y["TITLE"] 'Some string' >>>
Такой подход можно использовать и в том случае, если класс состоит из нескольких других.
�� Применить паттерн «Адаптер»
Идея в том, чтобы написать класс, который приводит к JSON пользовательские объекты и восстанавливает их. Определите класс фигуры, формы и цвета:
class Figure: def __init__(self, title, form, color): self.title = title self.form = form self.color = color def __str__(self): return f"Figure: , , " class Form: def __init__(self, name): self.name = name def __repr__(self): return f">" class Color: def __init__(self, name): self.name = name def __repr__(self): return f">"
Напишите класс, который будет приводить объекты фигуры к JSON, а из JSON воссоздавать полученный объект:
class JSONDataAdapter: @staticmethod def to_json(o): if isinstance(o, Figure): return json.dumps(< "title": o.title, "form": o.form.name, "color": o.color.name, >) @staticmethod def from_json(o): o = json.loads(o) try: form = Form(o["form"]) color = Color(o["color"]) figure = Figure(o["title"], form, color) return figure except AttributeError: print("Неверная структура")
if __name__ == '__main__': # создадим несколько цветов black = Color("Black") yellow = Color("Yellow") green = Color("Green") # несколько форм rountt = Form("Rounded") square = Form("Squared") # объекты фигур figure_one = Figure("Black Square", form=square, color=black) figure_two = Figure("Yellow Circle", form=rountt, color=yellow) print(“Отображение объектов”) print(figure_one) print(figure_two) print() # преобразуем данные в JSON jone = JSONDataAdapter.to_json(figure_one) jtwo = JSONDataAdapter.to_json(figure_two) print(“Отображение JSON”) print(jone) print(jtwo) print() # восстановим объекты restored_one = JSONDataAdapter.from_json(jone) restored_two = JSONDataAdapter.from_json(jtwo) print(“Отображение восстановленных объектов”) print(restored_one) print(restored_two)
Вывод терминала:
Figure: Black Square, , Figure: Yellow Circle, ,
Отображение восстановленных объектов
Полезные запросы к системным таблицам Firebird и InterBase
В этом документе приведены полезные для разработчика или администратора БД запросы к системным таблицам InterBase/Firebird. Для понимания связей между системными таблицами и назначения самих системных таблиц следует прочитать Language Reference (Руководство по языку), приложение System Tables (Системные таблицы). Далее, если вы работаете с PowerDesigner DataArchitect 6.x, стоит взять физическую модель системных таблиц (12K), и тогда вы сами сможете строить подобные запросы буквально за полминуты.
Все приведенные ниже запросы можно оформить в виде view в БД, если вам необходимо часто их использовать. По возможности элементы запроса выделяются цветом (например. таблица1 и ее поля – одним цветом, а таблица2 и ее поля – другим, ключевые слова – жирным шрифтом и т. д.)
1. Получение списка полей и их типов по таблицам
select R.RDB$RELATION_NAME , R.RDB$FIELD_POSITION , R.RDB$FIELD_NAME ,
F.RDB$FIELD_LENGTH , F.RDB$FIELD_TYPE , F.RDB$FIELD_SCALE , F.RDB$FIELD_SUB_TYPE
from RDB$FIELDS F , RDB$RELATION_FIELDS R
where F.RDB$FIELD_NAME = R.RDB$FIELD_SOURCE and R.RDB$SYSTEM_FLAG = 0
order by R.RDB$RELATION_NAME , R.RDB$FIELD_POSITION
Если вам не нравится нумерация полей с 0, то можно вместо R.RDB$FIELD_POSITION написать R.RDB$FIELD_POSITION+1. Для получения списка полей конкретной таблицы нужно добавить соответствующее условие к where – and RDB$RELATION_NAME = ‘MYTABLE’.
2. Мониторинг счетчика метаданных таблиц
Известно, что IB позволяет измененять структуру таблицы «на ходу». При этом для правильного считывания структур записей созданными в разных версиях таблицы используется счетчик изменения метаданных. Этот счетчик находится в таблицах RDB$RELATIONS и RDB$FORMATS и хранится в поле RDB$FORMAT. Изменения тела триггеров их их параметров (например ACTIVE/INACTIVE) также приводят к увеличению счетчика метаданных соответствующей таблицы. Однако счетчик этот ограничен максимальным значением 256. Как только он достигнет максимального значения, станет невозможным менять структуры таблиц или триггеры пока не будет сделан backup/restore. Для того, чтобы узнать, насколько близко ваша БД подошла к этому состоянию, можно выдать запрос:
select R.RDB$RELATION_NAME , max( F.RDB$FORMAT )
from RDB$RELATIONS R , RDB$FORMATS F
where R.RDB$RELATION_ID = F.RDB$RELATION_ID and R.RDB$SYSTEM_FLAG = 0
group by R.RDB$RELATION_NAME
having max( F.RDB$FORMAT ) > 1
order by 2 desc
Здесь выбирается максимальный достигнутый на текущий момент номер версии метаданных для каждой несистемной таблицы. При помощи having max(F.RDB$FORMAT) > x можно управлять «порогом» версий метаданных, которые вы хотите видеть, если количество таблиц в базе данных слишком большое. Конструкция order by 2 desc сортирует записи, помещая самый большой номер версии метаданных (max(f.rdb$format)) в начало таблицы.
Примечание. На самом деле максимальный номер счетчика метаданных хранится в RDB$RELATIONS, поэтому можно обойтись и более простым запросом:
select RDB$RELATION_NAME, RDB$FORMAT
from RDB$RELATIONS R
where R.RDB$SYSTEM_FLAG = 0 and RDB$FORMAT > 1
order by RDB$FORMAT desc
3. Если вам пришла в голову идея перестроить все индексы в базе данных,
то для этих целей очень легко получить скрипт. Выполните в WISQL следующий запрос:
select 'alter index ' || RDB$INDEX_NAME || ' inactive;'
from RDB$INDICES
where RDB$INDEX_NAME not like 'RDB$%';
И из области вывода WISQL можно скопировать готовый текст. Получить скрипт для пересоздания индексов можно выполнив тот же запрос, заменив слово ‘inactive’ на ‘active’. Условие where . not like ‘rdb$%’ исключает из результата имена индексов, которые строятся автоматически по первичным и вторичным ключам – такие индексы могут быть удалены только при удалении соответствующего constraint.
4. Получение списка таблиц базы данных, связанных по master-detail,
т. е. посредством foreign key:
select F.RDB$RELATION_NAME , F.RDB$CONSTRAINT_NAME ,
T.RDB$RELATION_NAME , T.RDB$CONSTRAINT_NAME
from RDB$REF_CONSTRAINTS C, RDB$RELATION_CONSTRAINTS F , RDB$RELATION_CONSTRAINTS T
where C.RDB$CONSTRAINT_NAME = F.RDB$CONSTRAINT_NAME and
T.RDB$CONSTRAINT_NAME = C.RDB$CONST_NAME_UQ
order by F.RDB$RELATION_NAME, T.RDB$RELATION_NAME
Смысл этого запроса следующий: таблица RDB$RELATION_CONSTRAINTS содержит все имена constraints, включая первичные и вторичные ключи. Таблица RDB$REF_CONSTRAINTS содержит список всех вторичных ключей. Задача – вытащить имена таблиц и имена ссылающихся на них таблиц, при помощи двукратного обращения к RDB$RELATION_CONSTRAINTS (алиасы F и T, from и to соответственно). В результате получаем четыре столбца:
- F.RDB$RELATION_NAME - имя таблицы, которая ссылается на другую при помощи foreign key
- F.RDB$CONSTRAINT_NAME - имя соответствующего foreign key
- T.RDB$RELATION_NAME - имя таблицы, на которую ссылаются (используется как справочник)
- F.RDB$CONSTRAINT_NAME - имя primary key constraint таблицы T.RDB$RELATION_NAME
Второй и четвертый столбцы чисто информативные. Order by – для красоты. Запрос такого типа можно использовать как для организации механизма репликации, так и для создания программ копирования данных из одной базы данных в другую.
Поскольку в случае копирования данных из одной БД в другую требуется сначала копировать таблицы, которые являются справочными (т.е. не имеют ссылок по foreign key на другие таблицы), то получение списка таких таблиц может также оказаться полезным:
select distinct C.RDB$RELATION_NAME
from RDB$RELATION_CONSTRAINTS C
where not exists (select * from RDB$RELATION_CONSTRAINTS R
where R.RDB$CONSTRAINT_TYPE = 'FOREIGN KEY' and
R.RDB$RELATION_NAME = C.RDB$RELATION_NAME )
order by C.RDB$RELATION_NAME
Здесь из результата выборки исключаются таблицы, которые имеют хотя-бы одну связь по foreign key. Distinct используется для исключения повторения имен таблиц, т. к. у таблицы может быть несколько constraints, которые совсем необязательно foreign key. Кому-то этот запрос может показаться не совсем красивым, но он работает так, как нужно. Желающие могут поупражняться в SQL самостоятельно.
5. Получение списка таблиц и полей, которые ссылаются сами на себя
Предыдущий запрос вряд ли учитывает ситуацию, когда таблица имеет ссылки по foreign key на саму себя. При этом данные из такой таблицы откопировать можно только либо предварительно удалив аналогичный FK в таблице назначения, либо определенным образом отсортировать считываемые данные. Для этого нужно найти таблицы, ссылающиеся сами на себя, и их поля.
select F.RDB$FIELD_NAME, RC1.RDB$RELATION_NAME
from
RDB$RELATION_CONSTRAINTS RC1 ,
RDB$RELATION_CONSTRAINTS RC2 ,
RDB$REF_CONSTRAINTS RF ,
RDB$INDEX_SEGMENTS I ,
RDB$RELATION_FIELDS F
where
RC1.RDB$CONSTRAINT_TYPE = ‘FOREIGN KEY’ and
RC2.RDB$CONSTRAINT_TYPE = ‘PRIMARY KEY’ and
RC1.RDB$CONSTRAINT_NAME = RF.RDB$CONSTRAINT_NAME and
RC2.RDB$CONSTRAINT_NAME = RF.RDB$CONST_NAME_UQ and
RC1.RDB$INDEX_NAME = I.RDB$INDEX_NAME and
RC1.RDB$RELATION_NAME = RC2.RDB$RELATION_NAME and
F.RDB$RELATION_NAME = RC1.RDB$RELATION_NAME and
F.RDB$FIELD_NAME = I.RDB$FIELD_NAME
order by RC1.RDB$RELATION_NAME , F.RDB$FIELD_POSITION
Смысл следующий. Таблица REF_CONSTRAINTS хранит информацию о том, какой FK ссылается на какой PK. Следовательно, сделав пару алиасов RELATION_CONSTRAINTS – RC1 и RC2 – можно вытащить данные об одинаковых RELATION_NAME для PK и FK. Далее, чтобы извлечь имена полей, приходится обращаться к таблице INDEX_SEGMENTS (т. к. RC не хранит информацию о полях связи, а только индекс). Но и чтобы не попасть на одинаковые имена полей для разных таблиц, приходится сравнивать имя таблицы поля с именем таблицы RC1.
Разумеется, если ссылающихся на самих себя FK у таблицы более одного, то сложность задачи возрастает намного. И дело здесь даже не в порядке полей, а в том, какие именно значения в них записаны. Теоретически может потребоваться открыть столько запросов, сколько таких FK у таблицы, каждый отсортированный по своему полю. И дальше пытаться по очереди копировать записи из всех запросов, контролируя отсутствие дубликатов (или exceptions) по первичному ключу.
6. Копирование привилегий доступа
от одного пользователя к другому. Такое бывает необходимо, если нужно сменить USERNAME для пользователя. Процедуру предложил Rado Benc. Если необходимость в такой операции возникает только один раз, то разумеется, можно воспользоваться просто оператором insert into . select from, вырезанным из этой процедуры. Ну и конечно, перед выполнением процедуры или запроса, не забудьте сделать backup и extract metadata for database в WISQL. Осторожность не помешает.
create procedure COPY_USER
(OLDUSER VARCHAR(32), NEWUSER VARCHAR(32))
as
begin
/* Be aware! Inappropriate use of this procedure */
/* may cause database crash and permanent data loss 🙂 */
if (OLDUSER <> NEWUSER) then
insert into RDB$USER_PRIVILEGES (
RDB$USER,
RDB$GRANTOR,
RDB$PRIVILEGE,
RDB$GRANT_OPTION,
RDB$RELATION_NAME,
RDB$FIELD_NAME,
RDB$USER_TYPE,
RDB$OBJECT_TYPE)
select
:NEWUSER,
RDB$GRANTOR,
RDB$PRIVILEGE,
RDB$GRANT_OPTION,
RDB$RELATION_NAME,
RDB$FIELD_NAME,
RDB$USER_TYPE,
RDB$OBJECT_TYPE
from RDB$USER_PRIVILEGES
where (RDB$USER = :OLDUSER);
end
Если после копирования привилегий вам «старый» пользователь больше не нужен, то вы можете удалить его привилегии командой
delete from RDB$USER_PRIVILEGES
where RDB$USER = "your old user"
7. Получение списка таблиц, отсортированного по количеству записей
Известно, что оптимизатор для планирования запросов использует информацию об уникальности конкретного индекса. Эта информация хранится в столбце RDB$STATISTICS таблицы RDB$INDICES. Обновляется эта статистика или при перестройке (создании) индекса, или при выполнении оператора
SET STATISTICS INDEX
Если вы пользуетесь утилитой IBExpert (или подобной), то в меню алиаса есть пункт Recompute index selectivity, который пересчитывает статистику для всех индексов. Также можно воспользоваться утилитой gidx из комплекта gtools.
После сбора статистики запрос
select rdb$relation_name,
cast(rdb$statistics as numeric(15,13)) from rdb$indices
where rdb$index_name starting with ‘RDB$PRI’
order by rdb$statistics
и выдаст список имен таблиц в порядке убывания по относительному числу записей.
8. Как узнать количество записей в таблицах?
Конечно, обычный способ это select count(*) from table , но этот способ может быть долгим.
Более простой способ – это использовать статистику по уникальным индексам. Статистика хранится в RDB$INDICES.RDB$INDEX_STATISTICS и представляет собой результат выполнения формулы 1/(Keys-Duplicates), где Keys – число ключей индекса (равно или больше числа записей), а Duplicates – количество повторяющихся значений. Для уникального индекса Duplicates будет всегда равно 0. Поэтому если 1 поделить на результат вычисления этой формулы, мы получим количество записей в конкретной таблице с достаточно высокой точностью:
Внимание! Перед выполнением этого запроса надо собрать статистику по индексам (в т. ч. уникальным) либо в IBExpert (Database/recompute selectivity for all indices), либо утилитой gidx, либо вручную (или скриптом) выполняя команды set statistics index index_name>
SELECT RDB$INDICES.rdb$relation_name RELATION,
cast(1/I.RDB$STATISTICS as integer) RECORD_COUNT
FROM RDB$INDICES I
JOIN RDB$RELATION_CONSTRAINTS C
ON (C.RDB$INDEX_NAME = I.RDB$INDEX_NAME)
AND (C.RDB$CONSTRAINT_TYPE = ‘PRIMARY KEY’)
AND (I.RDB$STATISTICS > cast(0 as double precision))
Последнее условие необходимо для исключения таблиц с числом записей = 0. В результат выборки попадут только те таблицы, которые имеют первичный ключ объявленнный как PRIMARY KEY. Если вы вместо PK используете UNIQUE – поменяйте запрос.
9. Получить список таблиц с индексами и fk, и куда и какие fk ссылаются
Данный запрос выбирает: индекс, таблицу-detail, constraint FK, по которому создан индекс, таблицу-master, constraint PK и индекс, куда ссылается исходный FK-constraint. Идея этого запроса возникла для отслеживания таблиц с поврежденными связями master-detail после ремонта баз данных (ситуация описана здесь).
В запросе для поиска неактивных индексов FK нужно заменить условие i.rdb$index_inactive = 0 на i.rdb$index_inactive = 1. То есть, запрос будет показывать информацию detail-master для таблиц, у которых неактивен FK из-за того, что в detail-таблице есть записи, ссылающиеся на отсутствующие записи в таблице master.
select
i.RDB$INDEX_NAME what_index,
i.RDB$RELATION_NAME what_table,
r.RDB$CONSTRAINT_NAME what_constraint,
c.RDB$CONST_NAME_UQ refers_to_constraint,
r2.RDB$RELATION_NAME refers_to_table,
r2.RDB$INDEX_NAME refers_to_index
from rdb$indices i , rdb$relation_constraints r ,
rdb$ref_constraints c ,
rdb$relation_constraints r2
where i.rdb$index_inactive = 0 and
r.RDB$INDEX_NAME = i.RDB$INDEX_NAME and
r.RDB$CONSTRAINT_NAME = c.RDB$CONSTRAINT_NAME and
r2.RDB$CONSTRAINT_NAME = c.RDB$CONST_NAME_UQ
order by 2, 1
10. Получить список grant, выданных на отсутствующие объекты
В серверах IB/FB присутствует ошибка, при которой в случае удаления (drop) нескольких объектов в одной транзакции могут остатся grant в rdb$user_privileges. Такая же проблема может быть и из-за повреждения системных таблиц. Для того, чтобы обнаружить такие grant, можно выполнить запрос:
select *
from rdb$user_privileges up
where up.rdb$user_type = 5
and not exists ( select 1 from rdb$procedures where rdb$procedure_name = up.rdb$user )
union all
select *
from rdb$user_privileges up
where up.rdb$user_type = 2
and not exists ( select 1 from rdb$triggers where rdb$trigger_name = up.rdb$user )
union all
select *
from rdb$user_privileges up
where up.rdb$user_type = 1
and not exists ( select 1 from rdb$view_relations where rdb$view_name = up.rdb$user )
Найденные записи можно безболезненно удалить прямо из rdb$user_privileges.
11. Список чарсетов и коллэйтов (character sets & collate) для всех столбцов таблиц базы данных
Иногда бывает нужно посмотреть все чарсеты у строковых полей таблиц (как минимум) и их collate, для обнаружения некорректно заданных, например.
Чарсет и коллэйт указаны у столбцов в rdb$fields. Используемые в таблицах столбцы – в rdb$relations. При желании эту таблицу можно заменить на rdb$procedure_parameters для проверки того же самого в отношении входных и выходных параметров процедур. Далее идет связка rdb$fields с таблицами чарсетов и коллэйтов (не забудьте, что rdb$collations – подчиненная для rdb$character_sets таблица).
Отфильтровать только строковые столбцы можно было бы указав проверку на field_type, однако это проще сделать проверив чарсет стольбца на is null – он будет null у всех остальных столбцов, кроме строк и блобов. Также исключаем из проверки все системные таблицы, отфильтровав названия, начинающиеся с ‘rdb$’
select rf.rdb$relation_name relation, rf.rdb$field_name table_field,
f.rdb$field_name field_domain, f.rdb$field_type field_type,
cs.rdb$character_set_name character_set,
c.rdb$collation_name collation_name
from
rdb$fields f, rdb$character_sets cs,
rdb$collations c, rdb$relation_fields rf
where
cs.rdb$character_set_id = f.rdb$character_set_id and
c.rdb$collation_id = f.rdb$collation_id and
c.rdb$character_set_id = cs.rdb$character_set_id and
f.rdb$field_name = rf.rdb$field_source
and f.rdb$character_set_id is not null
and rf.rdb$relation_name not starting with ‘RDB$’
order by 1, 2
сортируем результат по имени таблицы и реальному имени столбца. Если хочется увидеть название типа столбца по его коду, то нужно добавить в from таблицу rdb$types t, объединить в where f.rdb$field_type = t.rdb$type and t.rdb$field_name = ‘RDB$FIELD_TYPE’, и вместо f.rdb$field_name в запросе вывести t.rdb$type_name.
12. Подсчет размера столбцов блобов во всех таблицах
С увеличением объемов данных к 2011 году нормальный размер БД это 10-50 гигабайт. Иногда возникают ситуации, когда нужно понять, в каких таблицах блобы занимают больше всего места, т. к. в статистике, выдаваемой gstat -a -r блобы не учитываются (и IBAnalyst поэтому тоже о блобах ничего сказать не может).
Микросекунда, участник форума sql.ru, предложил следующий запрос для Firebird 2.5:
execute block
returns (
TABLE_NAME CHAR(31),
FIELD_NAME CHAR(31),
FIELD_SIZE bigint,
SQL varchar(200))
as
begin
for select T.RDB$RELATION_NAME, R.RDB$FIELD_NAME
from RDB$RELATIONS T
join RDB$RELATION_FIELDS R on R.RDB$RELATION_NAME=T.RDB$RELATION_NAME
join RDB$FIELDS F on F.RDB$FIELD_NAME = R.RDB$FIELD_SOURCE
where F.RDB$FIELD_TYPE=261
and T.RDB$RELATION_TYPE=0
order by 1, 2
into :TABLE_NAME, :FIELD_NAME
do
begin
SQL=’select sum(OCTET_LENGTH(‘ || TRIM(FIELD_NAME) || ‘)) from ‘ || TRIM(TABLE_NAME);
execute statement SQL into FIELD_SIZE;
suspend;
end
end
13. Получение списка таблиц, не имеющих ограничений Primary key или Unique
Автор: С. Никитин.
select distinct r.rdb$relation_name
from
(select rdb$relation_name from rdb$relations where (rdb$system_flag is
distinct from 1) and (rdb$relation_type in (0,4,5)) ) r
left join
(select rdb$relation_name from RDB$INDICES where (rdb$system_flag is
distinct from 1) and (rdb$unique_flag=1)) i
on r.rdb$relation_name=i.rdb$relation_name
where
(i.rdb$relation_name is null)
Copyright iBase.ru © 2002-2023