Стартуем с SQLite3 – Основные команды. Модуль sqlite — Работаем с базой данных. Работа с базой данных

На определенном этапе работы в фотобанках у каждого микростокера появляется вопрос: «Как и куда вывести деньги с микростока?». Вопрос вполне резонный, особенно для микростокеров из СНГ. Большинство фотобанков предлагает опции перечисления заработанных денег на счета в платежных системах Paypal и Moneybookers. микростокеры из России и других бывших советских республик не смогут в связи с ограничениями этой платежной системы на некоторые виды операций. Поэтому придется использовать для вывода денег с микростоков платежную систему Moneybookers (Манибукерс). На сегодняшний день данная платежная система стала одним из самых быстрых и оптимальных вариантов получения заработанных средств с фотобанков, по этой причине я и предлагаю Вашему вниманию обзор по работе с этой системой. В сегодняшней заметке я попытаюсь рассказать о том, что это за зверь такой платежная система Манибукерс, как зарегистрироваться в Moneybookers и как начать работу с ней.

Что за система Moneybookers (Манибукерс)?

Moneybookers – это система электронных платежей, зарегистрированная в Великобритании. Манибукерс очень сходна с более распространенной платежной системой Paypal, но в то же время выгодно отличается тем, что Манибукерс в отличие от Пэйпал работает с основной массой стран восточной Европы, в том числе и СНГ. Международная платёжная система Moneybookers предлагает возможность срочного внесения депозитов и вывода средств. На сегодня, платежная система Манибукерс представляет собой идеальный вариант быстрого получения денег, заработанных в фотобанках. Схематически вывод денежных средств достаточно прост: пересылают Ваши деньги на указанный Вами счёт в платежной системе Moneybookers, а там эти деньги накапливаются до тех пор, пока вы не закажете перевод этих денег из системы на свой валютный банковский счёт или же платежную карту системы Visa. Перечисление денег на платежную карту Visa происходит моментально, но понадобится еще 3-4 рабочих дня, чтобы Ваш банк подтвердил у себя эту операцию. Если же Вы заказываете на Ваш банковский счет, то такой перевод занимает от 4 до 14 дней в зависимости от страны проживания. Также Вы сможете израсходовать деньги на счету в платежной системе Moneybookers для осуществления покупок в Интернете(через свой счет в системе можно производить оплату покупок в 40000 онлайновых магазинов) или же перевести деньги на счет какого-либо другого человека в платежной системе. Русскоязычные пользователи между собой называют платежную систему Moneybookers – букерсы.

О надежности платежной системы Moneybookers () может ярко свидетельствовать тот факт, что платежная система находится под жестким контролем британской службы финансового регулирования FSA и подчиняется строжайшим нормам и правилам, разработанным для обладающих лицензией финансовых институтов. Кроме того, платежная система Манибукерс выступает своего рода сейфом для ваших данных: вы сможете проводить через эту платежную систему, являющуюся лицензионным банковским институтом, все финансовые операции с помощью имеющегося адреса электронной почты и пароля, не раскрывая данных своего личного счёта третьему лицу.

Не так давно платежная система сделала русскоязычную версию своего сервиса и поэтому регистрация в Moneybookers не вызывает никаких проблем. На данный момент существует только два барьера для регистрации в Moneybookers – Ваш возраст менее 18 лет или же Вы проживаете в стране, которая не желает сотрудничать с международным сообществом в борьбе с отмыванием денег.
Чтобы зарегистрироваться в Moneybookers Вам необходимо перейти на сайт платежной системы по этой ссылке: и выбрать русскоязычный интерфейс (если он не был выбран по умолчанию) и далее нажать кнопку «Регистрация » или “Register ”:

На следующей странице после нажатия на кнопку Вам потребуется ввести:

Данные ввели? Перепроверили? Если все правильно, то жмем кнопку “далее” и ждем письма от Монибукерс. На почтовый адрес, указанный вами при регистрации в Moneybookers, Вам придет письмо со ссылкой для активации аккаунта. Присланная ссылка верификации будет действительна в течение суток. Кликнув по этой ссылке, Вы перейдете на страницу продолжения регистрации в Манибукерс. На этой странице Вам надо будет заполнить несколько полей уже со своими личными данными. В дальнейшем, в меню аккаунта “Профиль” Вы сможете изменить улицу проживания, город, район, индекс, страну. Правда, редактировать свои контактные данные в аккаунте можно будет только один раз в 90 дней.
Вбивать свои личные данные надо латиницей (английскими буквами) во избежание дальнейших проблем. Рекомендую данные вбивать также, как они у Вас пропечатаны в загранпаспорте. Если же такая возможность отсутствует, то старайтесь вводить данные в систему также, как Вы вводили их при регистрации на фотобанках. Хочу отметить, что дату рождения, в дальнейшем, Вам надо будет вводить каждый раз при любой операции вывода денег из Moneybookers для сравнения с датой введённой при регистрации. Также платежная система в своих правилах требует, чтобы счёт или кредитная карта, которые будут использоваться для вывода денег из Манибукерс, были открыты на то же самое имя, что было указано вами при регистрации в Moneybookers.
После того, как Вы заполните все предлагаемые поля и перепроверите их пару раз на корректность, жмите “далее” и подтверждайте таким образом свою регистрацию в Манибукерсах. Регистрация в Манибукерс завершена! Сразу после подтверждения Вы уже можете полноправно получать деньги на свой счёт в Moneybookers (не забывайте, что в настройках аккаунта на фотобанке надо указать способ выплаты Moneybookers и прописать e-mail, который используется в качестве идентификатора Вашего счёта в системе).
Кстати, поступление средств от фотобанков на Ваш счет в Moneybookers не облагается никакими комиссиями и происходит абсолютно бесплатно.
Про вывод денег из системы, о тарифах и службе поддержки платежной системы я расскажу в продолжении этой статьи о Манибукерс в ближайшее время.

Все больше фотобанков осуществляют выплаты фотографам и иллюстраторам с помощью платежной системы Moneybookers . Для жителей большинства стран СНГ она, на сегодняшний день, является одним из самых быстрых и удобных способов получения заработанных средств.

В этой статье я постараюсь рассказать, как зарегистрироваться и начать работу в Moneybookers . Наверное, стоит начать с общих понятий и определений…

Что такое Moneybookers?

Moneybookers – это система электронных платежей (Великобритания), во многом похожая на более распространенную - Paypal , но в отличие от последней, работающая с большинством стран восточной Европы. Moneybookers – это идеальный способ быстрого получения денег, заработанных в фотобанках. Схема вывода средств довольно проста – фотобанки переводят ваши деньги на ваш счёт в Moneybookers, где они аккумулируются до тех пор, пока вы не решите перевести их на свой валютный счёт или кредитную карту (Visa). В зависимости от страны проживания, перевод сумм на ваш счёт в банке осуществляется от 2 до 5 рабочих дней, перевод на кредитную карту Visa – моментально.

Кроме того, вы можете использовать Moneybookers для осуществления покупок в Интернете или для перевода денежных средств кому-либо еще.

Как зарегистрироваться в Moneybookers?

С недавнего времени Moneybookers русифицировал свой сервис, и процедура регистрации не вызывает никаких проблем. Есть лишь два препятствия к регистрации в Moneybookers – вам меньше 18 лет или вы проживаете в стране, которая не желает сотрудничать с международным сообществом в борьбе с отмыванием денег.

Итак, переходим на сайт Moneybookers , выбираем русский язык (если он не выбран по умолчанию), и жмём кнопку «регистрация». При регистрации на первой странице вас попросят указать:

• Ваш Email (в дальнейшем это будет ваш логин и идентификатор счёта),
• Пароль (не менее 6-ти символов, из которых хоть один должен быть не буквой)
• Страну местожительства (выбор из списка, если вашей страны нет в списке – значит не судьба…)
• Валюту в которой вы хотели бы вести счёт, для работы с фотобанками (выбираем доллар США)
• Язык предпочтения (язык, на котором будет отображаться сайт и служебная корреспонденция)
• Проверочный номер (цифробуквенный код с картинки рядом с полем)

Жмём "далее", и на указанный вами адрес Moneybookers отправляет письмо с ссылкой для активации. Эта ссылка действительна в течение 24 часов. Перейдя по ней, вы попадаете на следующую страницу регистрации, в которой также необходимо заполнить несколько полей уже с личными данными. Обращаю ваше внимание, что дату рождения вас будут просить ввести при каждой операции вывода денег и сравнивать её с введённой при регистрации. Кроме того, Moneybookers требует, чтобы счёт или кредитная карта, на которую вы будете выводить деньги были на то же самое имя, что введено вами при регистрации.

После заполнения всех обязательных полей нажимаем "далее" и подтверждаем регистрацию. Сразу после регистрации вы уже можете получать деньги на ваш счёт в Moneybookers (разумеется, если в фотобанках вами выбран способ выплаты Moneybookers и указан e-mail, соответствующий идентификатору вашего счёта в Moneybookers). Moneybookers не берёт никакой комиссии за получение вами переводов из фотобанков.

Как переводить деньги из Moneybookers на свой валютный счёт?

Для начала, чтобы вывести деньги из Moneybookers необходимо открыть валютный счёт в долларах США в любом удобном для вас банке. Можно открыть счёт даже в Сбербанке России, но в этом случае усложниться процедура верификации счёта. Итак, у вас есть валютный счёт, теперь этот счёт надо зарегистрировать в Moneybookers. Для этого вам понадобиться номер счёта и SWIFT-код вашего банка (SWIFT-код – это Банковский идентификационный код, его вы можете найти на сайте банка или спросить в самом банке). Заходите на сайт Moneybookers (в свой аккаунт), и нажимаете «вывод денег». В появившемся окне рядом с полем «Банк» нажимаем «Управление» и добавляем новый банк. Шаг за шагом вводим SWIFT и двадцатизначный номер счёта (в номере сбербанковского счета цифры после слэша не указываем).

Теперь счёт надо верифицировать, чтобы вы могли выводить свои деньги без ограничений, а Moneybookers спал спокойно, не боясь, что его используют для отмывки денег. Верификация счёта заключается в том, что вы делаете первый пробный перевод 15-20 долларов на ваш счёт в банке (с вас удержат комиссию за перевод в размере $2.28). Вместе с деньгами в переводе придёт код верификации, который необходимо узнать в самом банке. Увы, получение кода в банке самое нудное действие по причине «профессионализма» банковских клерков, но к счастью это надо сделать только один раз. Для экономии вашего времени и нервов подскажите банковским работникам, что искать нужный вам код надо в тексте платёжки (в свифтовом сообщении) в строчке, начинающейся на 70, в следующей строке будет следующее: VRF CODE ****** MONEYBOOKERS. То, что будет написано вместо звёздочек, и есть необходимый код верификации.

Полученный код верификации вас попросят ввести на сайте Moneybookers при выполнении следующего перевода денег на ваш банковский счёт. Код действителен в течении 45 дней с даты пробного перевода, поэтому, чтобы не повторять поход в банк, не откладывайте окончание верификации на потом.

Если вы открыли валютный счёт в отделении Сбербанка России, процедура верификации усложняется по причине того, что «свифтовки» не доходят до отделений и вам просто физически не смогут предоставить верификационный код. В этом случае вам необходимо отправить на адрес [email protected] письмо с просьбой верифицировать ваш счёт (можно на русском языке). К письму необходимо приложить скан официального удостоверения личности с фотографией (загранпаспорт или водительские права) и скан свежего счета за какие-либо услуги, в котором указан ваш почтовый адрес (например, квитанция из химчистки). После этого вы получите ответ об успешной верификации счёта и суммой установленного для вас лимита.

Существует ещё один способ вывода денег из Moneybookers – это перевод напрямую на вашу кредитную карту. Этот способ наиболее простой, но для этого у вас должна быть кредитная карта VISA или VISA electron с CCV-кодом на обратной стороне карты. Для верификации карты в Moneybookers вас попросят ввести данные ее данные, и затем сделают с неё автоматический микро-перевод на Moneybookers (около $3). После этого вы должны взять в банкомате выписку последних операций по карте (многие банки позволяют получить эту информацию через свою систему Интернет-банк) и ввести на Moneybookers точную сумму, которую Moneybookers снял с вашей карты. И всё, вы можете выводить свои деньги из Moneybookers прямо на свою карту.

Какие фотобанки перечисляют деньги на Moneybookers?

На сегодняшний день заработанные вами деньги могут перевести на Moneybookers следующие фотобанки:

• Shutterstock (минимальная выводимая сумма от $75)
• iStockphoto (от $100)

Когда создаются программы, использующее одну или несколько таблиц баз данных и (или) таблицы базы данных требуются только одному приложению (ограниченному числу приложений), то достаточно неудобно при сопровождении приложения отслеживать установки клиентского программного обеспечения для работы с базами данных, выполнять соответствующие настройки, решать вопросы безопасности и т.д., и т.п.

В Borland C++ Builder (Borland Delphi) для таких ситуаций можно использовать компонент TClientDataSet, позволяющий создать из таблиц БД (Paradox, DB) файл, который далее использовать в приложении полностью аналогично таблице базы данных. Собственно компонент TCLientDataSet позволял также, после загрузки файла из реальной таблицы БД, забыть на время о сервере доступа к данным и работать только с этим файлом (выполнять любое редактирование), а на этапе завершения приложения или на этапе необходимости публикации данных - перенести нужные данные в реальную таблицу БД.

Visual Studio Net не использует подобный механизм. Но все, что позволял компонент TCLientDataSet, в ней, с таким же успехом, можно выполнить, используя множество других способов. Я уже рассказал в своей книге в разделе "Работа с XML файлами в Visual Studio NET" про возможность применения для этой цели XML файлов, а в разделе "Работа с базами данных в Visual Studio" про возможность загрузки данных из таблиц БД в DataSet и обратного сохранения данных. Кроме того, средства.Net позволяют напрямую работать с файлами БД Access.

Но, как мы увидим, наиболее просто поставленная задача реализуется с использованием SQLite. Что дает право говорить именно так?

С точки зрения пользователя - SQLite, это одиночный файл на диске. Этот файл не требует для своего использования запуска сторонних процессов, наличия СУБД и дополнительных средств администрирования.

Протокол обмена обеспечивают вызовы функций (API) библиотек SQLite (библиотеки занимают чуть более 500кб. памяти), что не требует от программиста дополнительных знаний структуры БД и особенностей организации взаимодействия. Движок SQLite и интерфейс к ней реализованы в одной библиотеке.

От программиста на С#, работающего в Microsoft Visual Studio, не требуется дополнительных знаний по обеспечению обмена данными, обмен обеспечивается с использованием тех же компонент и функций языка.

Нет проблем с развертыванием и переносом базы данных с одного компьютера на другой. Сама база данных может поставляться вместе с приложением и копироваться другими пользователями в аналогичные программы.

И, все же, SQLite можно назвать встраиваемой реляционной базой данных:

Прежде всего, она, как MySQL и PostgreSQL (сравнение ведем с небольшими базами данных), реализует основные возможности стандарта SQL 92 (включая триггеры и транзакции), и, по мнению многих, превосходит по возможностям PostgreSQL, а по скорости обработки данных превосходит обе указанные базы.

SQLite обеспечивает поддержку объема данных до 2-х терабайт, а размер записи (при использовании полей BLOB) ограничен только памятью компьютера.

Поддерживается небольшое, но вполне достаточное для большинства задач, количество типов данных.

Однако простота накладывает и ограничения. Приходится жертвовать отдельными характеристиками, характерными для больших баз данных, и, прежде всего, параллелизмом процессов, богатством встроенных библиотечных функций, все же небольшим ограничением стандарта языка SQL и некоторыми другими характеристиками. Но это не мешает все более широкому распространению SQLite для прикладных программы с небольшими базами данных, приложений, не требующих администрирования баз данных, для создания временной базы данных в процессе работы приложений, в качестве базы данных доступа и паролей на небольших Web порталах и для многих других целей.

Параграф 1. Основы использования SQLite

Для использования SQL ite в программах на C#, можно использовать два подхода:

1. Скачать соответствующие файлы библиотек провайдера, разместить их на своем компьютере и при формировании решения непосредственно использовать ссылки на данные библиотеки.

2. Скачать и установить соответствующий провайдер, интегрируемый в Visual Studio и использовать контролы провайдера, как и все другие визуальные контролы среды.

Есть и платные, и бесплатные решения. Далеко не полный список инструментов и менеджеров - можно найти по этой ссылке на сайте sqlite.org . Здесь только отметим, что на сайте SQLite можно найти варианты файлов библиотек провайдеров на все случаи жизни.

Managed Only - для приложений, которые должны работать в Windows и в Linux-based операционных системах.

Compact Framework - для работы в среде.NET CF для мобильных устройств под управлением Windows CE.

Itanium - для работы на процессорах Intel Itanium.

x64 - версия для платформ с архитектурой x64.

x86 - версия для платформ с архитектурой x86.

Соответственно выбранному подходу будет несколько отличаться и решение Windows приложения для работы с SQLite.

Для первого подхода файлы библиотек можно найти по этой ссылке на официальном сайте SQlite - sqlite.org . Впрочем , для тех, кто будет рассматривать пример решения, приведенный ниже (Параграфы 2,3), это можно не делать. В конце параграфа 3 приложенном для скачивания, присутствуют все три необходимых нам файла для Windows, и, также, их можно скачать в процессе рассмотрения построения решения, в том месте, где речь пойдет об их включении в проект (Параграф 2).

Для второго подхода в Internrt можно найти множество, но по большей части платных встраиваемых провайдеров. Один из неплохих бесплатных провайдеров "dotConnect for SQLite 3.30 Standard (Free)" я нашел . О его использовании будет вестись речь ниже (параграф 5.), но насколько долго он будет бесплатен, остается под вопросом. К тому же, он уступает, расположенному на сайте рядом с ним, провайдеру dotConnect for SQLite 3.30 Professional Trial(30 дней).

Нам может понадобиться менеджер для работы с БД.

Первый из известных - SQLite Designer. Хотя богатствами функциональных возможностей он далек от хороших менеджеров баз данных и можно обойтись и без него, но все же, он дает возможность использовать некоторые визуальные средства для работы с базами SQLite: построитель запросов (Query Builder), редактирование таблиц и ряд других возможностей. Его можно найти в интернете, в частности (закладка Files).

Другой, более крутой менеджер - SQLite Expert Professional, обладает большинством возможностей хороших менеджеров баз данных. Его также можно найти в интернете, в частности на сайте программы sqliteexpert.com . К сожалению, это Trial version со сроком знакомства в 30 дней, но и за 30 дней можно познакомиться и с типами данных, и с формированием SQL предложений для работы с базой, и со многими другими особенностями. Так что не поленитесь и установите у себя данный менеджер.

Отметим также, что при использовании встраиваемых в Visual Studio провайдеров, появляется возможность доступа к БД из Server Explorer студии, и, в зависимости от провайдера, некоторые функции по манипулированию таблицами баз данных и составления запросов.

Организацию взаимодействия с SQLite начнем с подробного рассмотрения его с использованием первого подхода. Далее - второй и третий параграфы посвящены созданию Windows приложений с использованием первого подхода, основанного на непосредственном использовании файлов библиотек провайдера без интеграции их в Visual Studio Net.

Параграф 2. Подготовка демонстрационного решения

Создадим простейшее Windows Application решение, как это показано в главе 1 "Простейший Windows Application проект". Я использовал Visual Studio 2010. В ней, при создании решения, все осталось, как и в предыдущих версиях студии, Меню "File/New Project". Далее выбираем, как показано на Рис.1., темплату и задаем имена. Обратите внимание на выделенное красным, Возможно, у Вас нет папки VS_2010(она будет создана) или нет диска D, тогда его следует заменить на C.

Рис.1 Создание решения приложения

На форму приложения поместим 6 кнопок, как показано на Рис.2.

Рис.2 Решение приложения

Дважды кликнув по каждой кнопке, создадим все необходимые нам обработчики нажатия кнопок, чтобы в дальнейшем к этому не возвращаться.

Функции для работы с SqLite будем оформлять, следуя моде, в классе "sqliteclass", для чего создадим этот класс, как показано на Рис.3., кликнув в Solution Explore на узле "sqlite" правой кнопкой мышки и выбрав пункты контекстного меню "Add" и "New Item".

Рис.3 Добавление класса для работы с SQLite

В появившемся окне коллекции темплат "Add New Item" выберем "Class", в поле "Name" введем имя класса - "sqliteclass.cs" и нажмем кнопочку "Add". Класс создан - Рис.4. Осталось заполнить его содержанием.

Рис.4 Класса для размещения функций для работы с SQLite

На данном этапе целесообразно нажать кнопочку "F5", выполнив решение, чтобы убедиться, что до данного момента код работоспособен.

Наступил тот момент, когда мы должны включить в проект библиотеки провайдера SQLite. Для этого скачаем их или как рекомендовано выше с сайта SQLite, или вместе с проектом итогового решения в конце параграфа 3, или . Распакуем архив, в последних двух случаях найдем папку SQLite3NET, поместите ее в общедоступное место, например в корень диска "С" (можно конечно поместить файлы из папки или саму папку туда, где Visual Studio хранит основные dll - у меня:
C:\Program Files\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\Profile\Client\).

PS: Чтобы узнать этот путь, в Solution Explorer дважды кликните в закладке "References" ссылку "System" (Рис.4.) - в окне "Object Browser" отобразится путь, где хранятся библиотеки .

Кликаем правой кнопкой мышки на закладке References (Рис.4.), выбираем пункт контекстного меню "AddReference...", в окне "AddReference" выбираем закладку "Browse", находим нашу распакованную папку SQLite3NET, и последовательно выбираем в проект библиотеки "SQLite.Net.dll" и "ExceptionHandler.dll" (Рис.5.).

Рис.5 Добавление библиотек провайдера SQLite в решение

Переходим на закладку sqliteclass решения, и добавляем пространство имен "Finisar.SQLite" и "System.Data" - они необходимы для работы с базай данных.

Using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data ;
using Finisar.SQLite ; namespace sqlite { class sqliteclass { } }

На этом этапе наше решение готово для создания и организации взаимодействия с БД SQLite. Для того, чтобы убедиться, что и на данном этапе все работоспособно, нажмем "F5".

Параграф 3. Организация взаимодействия с SQLite

3.1 Приступая к взаимодействию с базой даннах

Переходим на вкладку "Form1.cs" и добавляем переменные, которые нам понадобятся для работы, и, прежде всего, экземпляр класса "sqliteclass" - "mydb".

Namespace sqlite { public partial class Form1: Form { private sqliteclass mydb = null; private string sPath = string.Empty; private string sSql = string.Empty; .....

Определимся, что базу данных будем создавать в директории приложения. Ее имя определим "mybd.db" (желательные расширения файла базы данных - это "db" или "db3", по крайней мере, менеджеры баз данных предлагают выбрать нам именно файл баз данных с этими расширениям, но расширение, в принципе, не играет роли). Кроме того, нам понадобится путь, где находится "exe" файл приложения и полный путь к файлу базы данных - переменная "sPath". Перейдем на вкладку "Form1.cs." и дважды кликнем по заголовку окна решения. Будет создан обработчик загрузки формы приложения "Form1_Load", в нем и определим "sPath" (в C# есть множество способов определения того, где находится "exe" файл, это один из многих).

Private void Form1_Load(object sender, EventArgs e) { sPath = Path.Combine(Application.StartupPath, "mybd.db"); //Чтобы не плодить MessageBox все сообщения выводим в заголовок формы Text = sPath; }

3.2. Класс для работы с базой данных

Мы знаем, что SQL операторы можно подразделить на два основных типа:

Не возвращающие значения из базы данных (в лучшем случае возвращающие число записей, которые затронуло их выполнение - "Insert", "Delete", "Update").

Возвращающие запрошенную информацию из таблиц баз данных.

Поэтому в нашем классе создадим две функции для этих случаев, соответственно iExecuteNonQuery и drExecute. Первая будет возвращать число затронутых ею записей, вторая массив выбранных строк DataRow. Поскольку, функции практически не отличаются по организации работы с БД от рассматриваемых нами в разделе "Работа с базами данных в Visual Studio" книги, размещенной на данном сайте, то здесь приведем класс полностью и рассмотрим его функции:

PS: В класс можно добавить функции для скалярного выбора, функции выбора с использованием DataReader, но для нашей цели достаточно и двух функций. Остальные, каждый может добавить и сам .

Using System; using System.Collections.Generic; using System.Linq; using System.Text; using Finisar.SQLite; using System.Data; namespace sqlite { class sqliteclass { //Конструктор public sqliteclass() { } #region iExecuteNonQuery public int iExecuteNonQuery(string FileData, string sSql, int where) { int n = 0; try { using (SQLiteConnection con = new SQLiteConnection()) { if (where == 0) { con.ConnectionString = @"Data Source=" + FileData + ";New=True;Version=3"; } else { con.ConnectionString = @"Data Source=" + FileData + ";New=False;Version=3"; } con.Open(); using (SQLiteCommand sqlCommand = con.CreateCommand()) { sqlCommand.CommandText = sSql; n = sqlCommand.ExecuteNonQuery(); } con.Close(); } } catch (Exception ex) { n = 0; } return n; } #endregion #region drExecute public DataRow drExecute(string FileData, string sSql) { DataRow datarows = null; SQLiteDataAdapter dataadapter = null; DataSet dataset = new DataSet(); DataTable datatable = new DataTable(); try { using (SQLiteConnection con = new SQLiteConnection()) { con.ConnectionString = @"Data Source=" + FileData + ";New=False;Version=3"; con.Open(); using (SQLiteCommand sqlCommand = con.CreateCommand()) { dataadapter = new SQLiteDataAdapter(sSql, con); dataset.Reset(); dataadapter.Fill(dataset); datatable = dataset.Tables; datarows = datatable.Select(); } con.Close(); } } catch (Exception ex) { datarows = null; } return datarows; } #endregion } }

Итак, отличие от работы с другими базами данных - это наличие нескольких собственных невизуальных контролов. Первый, это свой коннектор - "SQLiteConnection", второй - собственный провайдер команд - "SQLiteCommand", и третий - свой адаптер данных, "SQLiteDataAdapter". Других контролов, отличных от MS SQL и Oracle не требуется.

"ConnectionString" требует указания полного пути к БД. Сами процессы связи с БД также стандартные. Обратим внимание на параметр "New", равный "True" или "False", который, как ясно по контексту, при значении "True", создает новою базу данных, а при значении "False" - нет. Причем, его значение приоритетнее "if not exists" в SQL операторе "CREATE TABLE if not exists..."

Перейдем к рассмотрению кода:

3.3 Создание базы данных

Будем создавать базу данных "mybd.db", путь к файлу которой мы определили в переменной "sPath". В базе данных создадим таблицу "birthday" со значениями полей "ФИО", "Даты рождения" и "отметки о поздравлении в данном году":

Private void button1_Click(object sender, EventArgs e) { //Создаем экземпляр нашего класса mydb = new sqliteclass(); sSql = @"CREATE TABLE birthday]( INTEGER PRIMARY KEY AUTOINCREMENT"; sSql +=", TEXT NOT NULL, datetime NOT NULL, INTEGER DEFAULT 0);"; //Пытаемся создать базу данных и таблицу одновременно, так как создание //базы данных есть всего лишь процесс создания пустого файла при организации //connection, или указание на файл, если БД (файл) существует //Третий параметр функции говорит, что БД создается вновь mydb.iExecuteNonQuery(sPath, sSql, 0);

Если мы запустим на данном этапе решение на выполнение, то получим необрабатываемое исключение. Причина в том, что для работы с SQLite все файлы библиотек провайдера должны лежать где и "exe" файл приложения - в ссборке . Скопируем три файла из C:\SQLite3NET\ в D:\VS_2010\sqlite\bin\Debug\ (тоже придется сделать, когда мы будем компилировать приложение в "Release", скопировав файлы в...\bin\Release).

PS: Те ссылки, библиотеки которых необходимы в сборке приложения, можно копировать в сборку автоматически. Для этогов Solutation Explorer надо выбрать окно "Properties" ссылки и установить свойство "Copy Local" в "True" .

Запустим приложение на выполнение, нажмем кнопочку "Create" и убедимся, что база данных создана (D:\VS_2010\sqlite\bin\Debug\mybd.db).

Отметим, что ни при создании базы, ни при создании таблицы, sqlCommand.ExecuteNonQuery ничего не возвращает, вернее возвращает "0". Поэтому надо как-то проверить то, что мы создали. Я делаю это так (продолжаем код, приведенный выше):

//Проверка работы sSql = @"insert into birthday (FIO,bdate,gretinyear)"; sSql +=" values("Александр Сергеевич Пушкин","1799-06-06",0);"; if (mydb.iExecuteNonQuery(sPath, sSql, 1) == 0) { Text = "Ошибка проверки таблицы на запись,"; Text +=" таблица или не создана или не прошла запись тестовой строки!"; mydb = null; return; } sSql = "select * from birthday"; DataRow datarows = mydb.drExecute(sPath, sSql); if (datarows == null) { Text = "Ошибка проверки таблицы на чтение!"; mydb = null; return; } Text = ""; foreach (DataRow dr in datarows) { Text += dr["id"].ToString().Trim() + dr["FIO"].ToString().Trim() + dr["bdate"].ToString().Trim() + " "; } sSql = "delete from birthday"; if (mydb.iExecuteNonQuery(sPath, sSql, 1) == 0) { Text = "Ошибка проверки таблицы на удаление записи!"; mydb = null; return; } Text = "Таблица создана!"; mydb = null; return; }//private void button1_Click(object sender, EventArgs e)

В принципе, мы использовали при проверки все основные действия с базой данных, Ниже приведенные функции лишь уточняют принципы взаимодействия.

3.4 Работа с базой данных

Работа с базой данных заключается в использовании функций класса для чтения и записи.

Private void button2_Click(object sender, EventArgs e) { mydb = new sqliteclass(); sSql = @"insert into birthday (FIO,bdate,gretinyear)"; sSql +=" values("Александр Сергеевич Пушкин","1799-06-06",0);"; //Проверка работы if (mydb.iExecuteNonQuery(sPath, sSql, 1) == 0) { Text = "Ошибка записи!"; } mydb = null; Text = "Запись 1 добавлена!"; return; } private void button3_Click(object sender, EventArgs e) { mydb = new sqliteclass(); //Ошибка в дате специально для последующего исправления sSql = @"insert into birthday (FIO,bdate,gretinyear)"; sSql += values("Толстой Лев Николаевич","1928-08-28",0);"; //Проверка работы if (mydb.iExecuteNonQuery(sPath, sSql, 1) == 0) { Text = "Ошибка записи!"; } mydb = null; Text = "Запись 2 добавлена!"; } private void button4_Click(object sender, EventArgs e) { mydb = new sqliteclass(); sSql = "select * from birthday"; DataRow datarows = mydb.drExecute(sPath, sSql); if (datarows == null) { Text = "Ошибка чтения!"; mydb = null; return; } Text = ""; foreach (DataRow dr in datarows) { Text += dr["id"].ToString().Trim() + " " + dr["FIO"].ToString().Trim() + " " + dr["bdate"].ToString().Trim() + " "; } mydb = null; } private void button5_Click(object sender, EventArgs e) { mydb = new sqliteclass(); sSql = "delete from birthday"; if (mydb.iExecuteNonQuery(sPath, sSql, 1) == 0) { Text = "Ошибка удаления записи!"; mydb = null; return; } mydb = null; Text = "Записи удалены из БД!"; } private void button6_Click(object sender, EventArgs e) { mydb = new sqliteclass(); sSql = @"Update birthday set bdate="1828-08-28" where FIO like("%Толстой%");"; if (mydb.iExecuteNonQuery(sPath, sSql, 1) == 0) { Text = "Ошибка обновления записи!"; mydb = null; return; } mydb = null; Text = "Запись 2 исправлена!"; }

Работа с базой данных показана на Рис.6.

Рис.6 Работа с базой данных SQLite

3.5 Скачать решение приложения

Решение приложения можно скачать .

Параграф 4. Типы данных SQLite

Наиболее удобно посмотреть типы данных SQLite в менеджере SQLite Expert Professional, о котором мы говорили в параграфе 1 (Рис.7.). Для этого нажмем кнопку "New Table", введем имя таблицы и нажмем кнопку "Add", в окне "New Field" в выпадающем списке можем посмотреть все возможные на данный момент типы данных.

Рис.7 Типы данных SQLite

Однако не все так прекрасно как может показаться. Мы действительно можем при создании таблицы задать показанные выше типы, но это, в большинстве своем, так называемые аффинированные типы , или типы, которые провайдер приведет к одному из следующих типов хранения:

NULL. Пустое значение в таблице базы.

INTEGER. Целочисленное значение, хранящееся в 1, 2, 3, 4, 6 или 8 байтах, в зависимости от величины самого значения.

REAL. Числовое значение с плавающей точкой. Хранится в формате 8-байтного числа IEEE с плавающей точкой.

TEXT. Значение строки текста. Хранится с использованием кодировки базы данных (UTF-8, UTF-16BE или UTF-16LE).

BLOB. Значение бинарных данных, хранящихся точно в том же виде, в каком были введены.

Подробно о типах данных можно посмотреть на сайте xBB - полезности для вебмастеров и не только (см. раздел сайта "Избранные доки" - "Типы данных SQLite"). Приведем с сайта правила аффинированности столбца по объявлению типа столбца и приведенную на сайте таблицу преобразования объявленных данных в аффинированные типы в соответствии с пятью правилами:

1. Если объявление типа содержит строку "INT", то столбец ассоциируется с аффинированным INTEGER.

2. Если объявление типа столбца содержит любую из строк "CHAR", "CLOB", или "TEXT", то аффинированность определяется как TEXT. Обратите внимание, что тип VARCHAR содержит подстроку "CHAR", и поэтому ему тоже сопоставляется аффинированный TEXT.

3. Если объявление типа столбца содержит строку "BLOB" или если тип не указан, то столбец аффинируется с NONE.

4. Если объявление типа столбца содержит любую из строк "REAL", "FLOA" или "DOUB", аффинированность определяется как REAL.

5. В остальных случаях столбцу сопоставляется аффинированный NUMERIC.

Полезно также посмотреть документацию по SQLite и, в частности, на сайте sb-money.ru и конечно на сайте SQLlite.

Параграф 5. Использование встроенных в Visual Studio провайдеров SQLite

Как говорилось в параграфе 1, неплохой встраиваемый в Visual Studio бесплатный провайдер "dotConnect for SQLite 3.30 Standard (Free)" можно найти на сайте http://www.devart.com, закладка Download .

Скачаем его и установим на машину, предварительно закрыв все Visual Studio Net.

В аннотации к провайдеру сказано:

Note: This package works with .NET Framework 2.0, 3.0, 3.5, and 4.0

Убедимся в этом. Откроем проект, который мы скачали (создали) в конце параграфа 3, удалим все ссылки в "Solution Explorer" в закладке "References" на SQLite, а также пространство имен в "sqliteclass.cs":

Using Finisar.SQLite;

Выберем Toolbox (меню "View", "Toolbox") и увидим новую закладку "SQLiteData" (Рис.8.).

Рис.8. Встроенный провайдер SQLite

Перенесем в проект любой из контролов со вкладки "SQLiteData", например контрол "SQLiteConnection". Убедимся, что в "Solution Explorer", в закладке "References", появились новые библиотеки (Рис.8.):

Devart.Data Devart.Data.SQLite

Добавим в класс "sqlclass.cs" пространства имен:

Using Devart.Data; using Devart.Data.SQLite;

Определимся на данном этапе, будем мы использовать стандартные контролы или нет. Я не сторонник использования стандартных контролов. Без них мы можем построить более гибкие приложения. Поэтому, удалим из проекта контрол "sqLiteConnection1" (на закладке "References" в Solution Explorer, при внесении контрола в проект библиотеки были добавлены, при удалении - они остаются). Значит, мы можем продолжить работу над проектом.

Изменим немного наш класс "sqliteclass.cs":

Using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.SqlTypes; using System.Data; using Devart.Data;
using Devart.Data.SQLite ; namespace sqlite { class sqliteclass { //Конструктор public sqliteclass() { } #region iExecuteNonQuery public int iExecuteNonQuery(string FileData, string sSql, int where) { int n = 0; try { using (SQLiteConnection con = new SQLiteConnection()) { if (where == 0) { SQLiteConnection.CreateFile(FileData); con.ConnectionString = @"Data Source=" + FileData; } else { con.ConnectionString = @"Data Source=" + FileData; } con.Open(); using (SQLiteCommand sqlCommand = con.CreateCommand()) { sqlCommand.CommandText = sSql; n = sqlCommand.ExecuteNonQuery(); } con.Close(); } } catch (Exception ex) { n = 0; } return n; } #endregion #region drExecute public DataRow drExecute(string FileData, string sSql) { DataRow datarows = null; SQLiteDataAdapter dataadapter = null; DataSet dataset = new DataSet(); DataTable datatable = new DataTable(); try { using (SQLiteConnection con = new SQLiteConnection()) { con.ConnectionString = @"Data Source=" + FileData; con.Open(); using (SQLiteCommand sqlCommand = con.CreateCommand()) { dataadapter = new SQLiteDataAdapter(sSql, con); dataset.Reset(); dataadapter.Fill(dataset); datatable = dataset.Tables; datarows = datatable.Select(); } con.Close(); } } catch (Exception ex) { datarows = null; } return datarows; } #endregion } }

Вот и все изменения в проекте решения. Выполним решение и убедимся, что приложение работает как и ранее (Рис.6.).

Параграф 6. О работе с ошибками

Особых средств для обработки ошибок провайдеры SQLite не содержат.

Единственное, что мы можем, это объявить в классе "sqlliteclass" переменные и функции возврата ошибок, например так:

Private string sLasterror = string.Empty; private string sErrors = string.Empty; public string sGetErrors() { return sErrors; } public string sGeLastError() { return sLasterror; }

И соответственно в функциях класса добавить код:

Try { ....... }catch(Excaption ex) { sLasterror =ex.Message; sErrors+=ex.Message+" "; }

Анализ ощибки выполнять уже в коде обращения к классу, после вызова данных функций.

Можно в try-catch предусмотреть и ведение журнала ошибок, добавив, например такую функцию:

File.AppendAllText("logerrors.txt", DateTime.Now.ToString() + " " + Path.GetFileName(FileData) + " " + sSql + " " + ex.Message+" "+Environment.NewLine, System.Text.Encoding.UTF8);

Параграф 7. Работа с Blob типами или как хранить фото

7.1 Решение приложения

Создадим новый проект решения с именем "sqlite1". Будем использовать шаги создания проекта, по методике показанной выше. Поместим на форму 6 контролов Button, 2 контрола PictureBox и один контрол OpenFileDialog. Также создадим класс "sqliteclass" (Рис.9.). Класс можно взять из предыдущего проекта, скопировав его в наш проект, добавив в решение(Solution Explorer, узел "sqlite1", контекстное меню узла, "Add", "Existing Item") и переименовав пространство имен:

Using System; using System.Collections.Generic; using System.Linq; using System.Text; using Devart.Data; using Devart.Data.SQLite; using System.IO; using System.Data.SqlTypes; using System.Data;
namespace sqlite1 { class sqliteclass {

Обратим внимание, что для работы с SQLite мы используем встроенный в Visual Studio провайдер и подход, описанный нами в параграфе 5. Как мы убедились - это не является принципиальным, мы переделывали проект подхода от использования соответствующих файлов библиотек провайдера к использованию встроенного в Visual Studio провайдера, Вы можете без труда, в качестве тренировки, после прочтения этого параграфа, проделать обратное преобразование.

Рис.9. Проект решения

Поставим задачу: нам необходимо хранить файлы фотографий в некоторой базе SQLite, причем, для простоты, ничего, кроме фотографий, в этой базе храниться не будет (только номер фотографии и само фото).

Начнем с кнопки 1 - создание базы данных.

//Необходимые пространства имен в классе формы using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SQLite; using System.Data.Common; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.IO; using System.Drawing.Imaging; namespace sqlite1 { public partial class Form1: Form { //Необходимые переменные для класса формы private sqliteclass mydb = null; private string sCurDir = string.Empty; private string sPath = string.Empty; private string sSql = string.Empty; private int viNum = 0; public byte data = null; //Обработчик нажатия кнопки 1 - создание базы данных: private void button1_Click(object sender, EventArgs e) { File.Delete(sPath); mydb = new sqliteclass(); sSql = "create table myphoto (id INTEGER PRIMARY KEY NOT NULL,photos blob)"; //Используем функцию класса, рассмотренную выше mydb.iExecuteNonQuery(sPath, sSql, 0); mydb = null; //Проверку работоспособности мы делали, повторять не будем }

Здесь ничего нового, даже функцию iExecuteNonQuery мы использовали из класса "sqliteclass" прошлого решения. Вы можете скопировать все функции в класс "sqliteclass" из прошлого решения, если не воспользовались советом о копировании класса.

Обратим внимание, что при создании таблицы базы данных, мы намеренно отказались от опции "AUTOINCREMENT" - причина, метод записи, который мы будем рассматривать следующим, не очень дружит с этим предложением.

Private void button2_Click(object sender, EventArgs e) { openFileDialog1.InitialDirectory = @"C:\"; openFileDialog1.Filter = "All Embroidery Files|*.bmp;*.gif;*.jpeg;*.jpg;" + "*.fif;*.fiff;*.png;*.wmf;*.emf" + "|Windows Bitmap (*.bmp)|*.bmp" + "|JPEG File Interchange Format (*.jpg)|*.jpg;*.jpeg" + "|Graphics Interchange Format (*.gif)|*.gif" + "|Portable Network Graphics (*.png)|*.png" + "|Tag Embroidery File Format (*.tif)|*.tif;*.tiff"; //openFileDialog1.Filter += "|All Files (*.*)|*.*"; if (openFileDialog1.ShowDialog() == DialogResult.OK) { Image image = Image.FromFile(openFileDialog1.FileName); MemoryStream memoryStream = new MemoryStream(); //Здесь можно выбрать формат хранения image.Save(memoryStream, ImageFormat.Jpeg); data = memoryStream.ToArray(); pictureBox1.Image = image; } }

Помимо отображения записываемой фотографии мы поместили байты файла фото в байтовый массив "data", используя MemoryStream и его метод ToArray().

7.2 Возможности использование SQLiteDataAdapter для записи BLOB данных

Введем в наш класс две функции. Задача первой будет "изучить" схему таблицы базы данных и возвратить ее в DataTable.

#region fReagSchema - Чтение схемы БД public DataTable dtReagSchema(string FileData, string sSql) { bool f = true; DataTable mydatatable = new DataTable(); SQLiteConnection con = new SQLiteConnection(); try { con.ConnectionString = @"Data Source=" + FileData; con.Open(); myDataAdapter = new SQLiteDataAdapter(sSql, con); mycommandbuilder = new SQLiteCommandBuilder(myDataAdapter); myDataAdapter.FillSchema(mydatatable, SchemaType.Source); } catch (Exception ex) { mydatatable = null; } if (myDataAdapter == null || mycommandbuilder == null) { mydatatable = null; } return mydatatable; } #endregion

Задача второй функции будет непосредственно внесение изменения в базу данных:

#region Изменение базы данных public int iWriteBlob(string FileData, DataTable mydatatable) { int n = 0; using (SQLiteConnection con = new SQLiteConnection()) { try { n = myDataAdapter.Update(mydatatable); } catch (Exception ex) { n = 0; } } return n; } #endregion

Эти две функции используются комплексно. При обращении к первой функции мы получаем объект DataTable, содержащий схему базы данных. Иначе, мы можем добавить строку таблицы базы данных в объект, как будто мы ее добавили в таблицу базы данных. Далее, после добавления строки, вызывается вторая функция, которая используя объекты SQLiteDataAdapter и SQLiteCommandBuilder, проводит изменение в таблице базы данных.

На стороне клиента осталось заполнить строку DataTable:

Private void button3_Click(object sender, EventArgs e) { if (data == null) { Text = "Не выбрано фото для записи в БД"; return; } int id = 0; mydb = new sqliteclass(); sSql = "Select max(id) from myphoto"; object obj = mydb.oExecuteScalar(sPath, sSql); if (!string.IsNullOrEmpty(obj.ToString())) { id = Convert.ToInt32(obj) + 1; } else { id = 1; } //SQL предложение для получения схемы таблицы sSql = "Select * from myphoto"; //Чтение схемы DataTable dt = mydb.dtReagSchema(sPath, sSql); //Добавление строки с данными DataRow datarow = dt.NewRow(); datarow["id"] = id; datarow["photos"] = data; dt.Rows.Add(datarow); //Сохранение изменения int n = mydb.iWriteBlob(sPath, dt); if (n == 0) { Text = "Фото в БД не записано!"; } else { Text = "Фото записано в БД!"; } mydb = null; }

7.3 Возможности использования SQL предложений с параметрами для записи BLOB данных

Приведенная ниже функция, которая была добавлена в класс, не претендует на универсальность, но позволяет записать в поле Blob байты изображения. Почему это приходится делать через параметры, а не преобразовать массив в строку? Дело в том, что байты изображения могут содержать нулевой символ, что воспринимается как конец строки и единственная возможность непосредственно указать адаптеру о необходимости воспринимать то, что передается в массиве байт, как сплошной поток бит - "SQLiteType.Blob".

#region ExecuteNonQuery public int iExecuteNonQueryBlob(string FileData, string sSql, string s0, Int32 number, string s1, byte data) { int n = 0; try { using (SQLiteConnection con = new SQLiteConnection()) { con.ConnectionString = @"Data Source=" + FileData; con.Open(); using (SQLiteCommand sqlCommand = con.CreateCommand()) { sqlCommand.CommandText = sSql; sqlCommand.Parameters.Add("@" + s0, SQLiteType.Int32).Value = number; sqlCommand.Parameters.Add("@" + s1, SQLiteType.Blob, data.Length).Value = data; n = sqlCommand.ExecuteNonQuery(); } con.Close(); } } catch (Exception ex) { n = 0; } return n; } #endregion

Соответственно, использование данной функции, будет выглядеть примерно так:

Private void button5_Click(object sender, EventArgs e) { if (data == null) { Text = "Не выбрано фото для записи в БД"; return; } int id = 0; mydb = new sqliteclass(); sSql = "Select max(id) from myphoto"; object obj = mydb.oExecuteScalar(sPath, sSql); if (!string.IsNullOrEmpty(obj.ToString())) { id = Convert.ToInt32(obj) + 1; } else { id = 1; } Text = id.ToString(); sSql="Insert into myphoto (id,photos) values(@id,@photos)"; int n = mydb.iExecuteNonQueryBlob(sPath, sSql, "id", id, "photos", data); if (n == 0) { Text = "Фото не записано в БД"; } else { Text = "Фото внесено в БД"; data = null; } mydb = null; }

7.4 Чтение Blob данных с использованием SQLiteDataAdapter

Это наиболее простой способ, основанный на том, что прочитанные в DataTable столбцы таблицы мы можем интерпретировать как любой тип данных, используя средства языка C#:

#region Чтение 1 поля BLOB public byte rgbtReadBlob(string FileData, string sSql, string field) { byte buffer = null; DataTable datatable = new DataTable(); using (SQLiteConnection con = new SQLiteConnection()) { try { con.ConnectionString = @"Data Source=" + FileData; con.Open(); SQLiteDataAdapter myDataAdapter = new SQLiteDataAdapter(sSql, con); SQLiteCommandBuilder commandbuilder = new SQLiteCommandBuilder(myDataAdapter); myDataAdapter.Fill(datatable); DataRow datarows = datatable.Select(); DataRow datarow = datarows; //Получаем быйты фото из БД в массив buffer = (byte)datarow; } catch (Exception ex) { buffer = null; } } return buffer; } #endregion

7.5 Чтение данных с использованием SQLiteDataReader

#region Execute SQLiteDataReader public byte rgbytedreaderExecute(string FileData, string sSql) { byte data = null; SQLiteDataReader dr = null; try { using (SQLiteConnection con = new SQLiteConnection()) { con.ConnectionString = @"Data Source=" + FileData; con.Open(); using (SQLiteCommand sqlCommand = con.CreateCommand()) { sqlCommand.CommandText = sSql; dr = sqlCommand.ExecuteReader(); } dr.Read(); data = GetBytes(dr); con.Close(); } } catch (Exception ex) { data = null; } return data; } static byte GetBytes(SQLiteDataReader reader) { //const int CHUNK_SIZE = 2 * 1024; byte bDate = new byte; long lRead = 0; long lOffset = 0; using (MemoryStream memorystream = new MemoryStream()) { while ((lRead = reader.GetBytes(0, lOffset, bDate, 0, bDate.Length)) > 0) { byte bRead = new byte; Buffer.BlockCopy(bDate, 0, bRead, 0, (int)lRead); memorystream.Write(bRead, 0, bRead.Length); lOffset += lRead; } return memorystream.ToArray(); } } #endregion

Молчанов Владислав 25.05.2011г., параграф 7 добавлен 30.5 2011г .

SQLite – это автономный, работающий без сервера транзакционный механизм базы данных SQL. Python получил модуль sqlite3 в версии 2.5, что значит что вы можете создавать базу данных SQLite в любой настоящей версии Python, без необходимости скачивания дополнительных инструментов. Mozilla использует базы данных SQLite в своем популярном браузере Firefox для хранения закладок и прочей различной информации. В данной статье мы рассмотрим следующее:

• Как создать базу данных SQLite
• Как вставить данные в таблицу
• Как редактировать данные
• Как удалять данные
• Базовые запросы SQL

Другими словами, вместо того чтобы собирать по кусочкам сам модуль, мы непосредственно ознакомимся с тем, как его использовать.
Если вы хотите визуально проверить свою базу данных, вы можете использовать плагин SQLite Manager в Firefox (рекомендуем погуглить данный вопрос), или, если вы предпочитаете командную строку, вы можете использовать оболочку командной строки .

Как создавать базу данных и вставлять различные данные

Создание базы данных в SQLite – это очень просто, но процесс требует того, чтобы вы немного разбирались в том, что такое SQL. Давайте взглянем на код, который создаст базу данных для хранения музыкальных альбомов:

Python

import sqlite3 conn = sqlite3.connect("mydatabase.db") # или:memory: чтобы сохранить в RAM cursor = conn.cursor() # Создание таблицы cursor.execute("""CREATE TABLE albums (title text, artist text, release_date text, publisher text, media_type text) """)

Сначала нам нужно модуль sqlite3 и создать связь с базой данных. Вы можете передать название файла или просто использовать специальную строку “:memory: ” для создания базы данных в памяти. В нашем случае, мы создаем его на диске в файле под названием mydatabase.db .

Далее мы создаем объект cursor , который позволяет нам взаимодействовать с базой данных и добавлять записи, помимо всего прочего. Здесь мы используем синтаксис SQL для создания таблицы под названием альбомы с пятью следующими полями: title, artist, release_date, publisher и media_type . SQLite поддерживает только пять типов данных: null , integer , real , text и blob . Давайте напишем этот код и вставим кое-какие данные в нашей новой таблице. Запомните, если вы запускаете команду CREATE TABLE , при этом база данных уже существует, вы получите сообщение об ошибке.

Python

# Вставляем данные в таблицу cursor.execute("""INSERT INTO albums VALUES ("Glow", "Andy Hunter", "7/24/2012", "Xplore Records", "MP3")""") # Сохраняем изменения conn.commit() # Вставляем множество данных в таблицу используя безопасный метод "?" albums = [("Exodus", "Andy Hunter", "7/9/2002", "Sparrow Records", "CD"), ("Until We Have Faces", "Red", "2/1/2011", "Essential Records", "CD"), ("The End is Where We Begin", "Thousand Foot Krutch", "4/17/2012", "TFKmusic", "CD"), ("The Good Life", "Trip Lee", "4/10/2012", "Reach Records", "CD")] cursor.executemany("INSERT INTO albums VALUES (?,?,?,?,?)", albums) conn.commit()

INSERT INTO SQL чтобы вставить запись в нашу базу данных. Обратите внимание на то, что каждый объект находится в одинарных кавычках . Это может усложнить работу, если вам нужно вставить строчки, которые содержат одинарные кавычки. В любом случае, чтобы сохранить запись в базе данных, нам нужно создать её. Следующая часть кода показывает, как добавить несколько записей за раз при помощи метода курсора . Обратите внимание на то, что мы используем знаки вопроса (? ), вместо строк замещения (%) чтобы вставить значения. Обратите внимание, что использование строки замещения не безопасно, так как может стать причиной появления атаки инъекций SQL . Использование знака вопроса намного лучше, а использование SQLAlchemy тем более, так как он делаете все необходимое, чтобы уберечь вас от правки встроенных одинарных кавычек на то, что SQLite в состоянии принимать.

Редактирование и удаление записей

Возможность обновлять записи в вашей базе данных это ключ к тому, чтобы ваши данные велись аккуратно, и был полный порядок. Если вы не можете редактировать данные , тогда ваша база станет бесполезной достаточно скоро. Иногда вам, в том числе, нужно будет удалять и строки. Мы рассмотрим оба вопроса в данном разделе. Для начала, давайте немного обновимся.

Python

Здесь мы использовали команду SQL UPDATE , чтобы обновить таблицу альбомов. Здесь вы можете использовать команду SET , чтобы изменить поле, так что в нашем случае мы изменим имя исполнителя на John Doe в каждой записи, где поле исполнителя указано для Andy Hunter. Весьма просто, не так ли? Обратите внимание на то, что если вы не подтвердите изменения, то они не будут внесены в базу данных. Команда DELETE настолько же проста. Давайте посмотрим.

Python

Удаление еще проще, чем обновление. У SQL это занимает всего две строчки. В данном случае, все, что нам нужно сделать, это указать SQLite , из какой таблицы удалить (albums), и какую именно запись при помощи пункта WHERE . Таким образом, был выполнен поиск записи, в которой присутствует имя “John Doe” в поле исполнителей, после чего эти данные были удалены .

Основные запросы SQLite

Запросы в SQLite очень похожи на те, которые вы используете в других базах данных, таких как MySQL или Postgres. Мы просто используем обычный синтаксис SQL для выполнения запросов, после чего объект cursor выполняет SQL. Вот несколько примеров:

Python

import sqlite3 conn = sqlite3.connect("mydatabase.db") #conn.row_factory = sqlite3.Row cursor = conn.cursor() sql = "SELECT * FROM albums WHERE artist=?" cursor.execute(sql, [("Red")]) print(cursor.fetchall()) # or use fetchone() print("Here"s a listing of all the records in the table:") for row in cursor.execute("SELECT rowid, * FROM albums ORDER BY artist"): print(row) print("Results from a LIKE query:") sql = "SELECT * FROM albums WHERE title LIKE "The%"" cursor.execute(sql) print(cursor.fetchall())

Это библиотека, написанная на языке C, которая обеспечивает работу с SQL. Данный инструмент относится к Реляционным системам управления базами данных . Большинство баз данных SQL работает по схеме клиент/сервер. Возьмём к примеру MySQL . В процессе работы данные берутся с MySQL сервера, и отправляются в качестве ответа на запрос. В случае использования SQLite, данные будут браться непосредственно с диска, т.е. не будет необходимости обращаться к серверу.

Установка

Мы будем взаимодействовать с базой данных через интерфейс командной строки sqlite3 (CLI) в Linux. Работа с sqlite3 CLI в MAC OS и Windows осуществляется таким же образом, однако я рекомендую вам потратить 5 минут на установку виртуальной машины, чтобы не захламлять свой компьютер лишним софтом.

Для установки sqlite3 на Linux выполняем команду:

sudo apt-get install sqlite3 libsqlite3-dev

В результате на вашей машине будет установлен sqlite3 . Для установки данного инструмента на других ОС следуйте инструкциям . Для запуска sqlite выполняем команду sqlite3 в консоли. Результат должен быть таким:

Во второй строчке указана подсказка о том, что для получения справки необходимо выполнить команду.help . Давайте сделаем это. В результате мы увидим Мета Команды и их описание.

Мета Команды

Мета Команды - предназначены для формирования таблиц и других административных операций. Все они оканчиваются точкой . Пройдёмся по списку команд, которые могут пригодиться:

Стандартные команды

Теперь давайте пройдёмся по списку стандартных команд sqlite3, которые предназначены для взаимодействия с базой данных. Стандартные команды могут быть классифицированы по трём группам:

• Язык описания данных DDL : команды для создания таблицы, изменения и удаления баз данных, таблиц и прочего.

Заметка : SQLite так же поддерживает и множество других команд, список которых можно найти . Поскольку данный урок предназначен для начинающих, мы ограничимся перечисленным набором команд.

Файлы баз данных SQLite являются кроссплатформенными . Они могут располагаться на различного рода устройствах.

• Email
• Комментарий

Из всех этих полей только адрес сайта может быть пустым. Так же можем ввести колонку для нумерации комментриев. Назовём её post_id .

Теперь давайте определимся с типами данных для каждой из колонок:

Вы сможете найти все типы данных, поддерживаемые в SQLite3.

Так же следует отметить, в SQLite3 данные, вставляемые в колонку могут отличаться от указанного типа. В MySQL такое не пройдёт.

Теперь давайте создадим базу данных. Если вы ещё находитесь в интерфейсе sqlite3, то наберите команду.quit для выхода. Теперь вводим:

sqlite3 comment_section.db

В результате, в текущем каталоге у нас появится файл comment_section.db .

Заметка : если не указать название файла, sqlite3 создаст временную базу данных.

Создание таблицы

Для хранения комментариев нам необходимо создать таблицу. Назовём её comments . Выполняем команду:

CREATE TABLE comments (post_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, email TEXT NOT NULL, website_url TEXT NULL, comment TEXT NOT NULL);

NOT NULL обеспечит уверенность, что ячейка не будет содержать пустое значение. PRIMARY KEY и AUTOINCREMENT расширяют возможности поля post_id .

Чтобы убедиться в том, что таблица была создана, выполняем мета команду.tables . В результате видим нашу таблицу comments .

Заметка : Для получения структуры таблицы наберите.schema comments

Теперь можем внести данные в таблицу.

ВСТАВКА СТРОК

Предположим, что нам необходим внести следующую запись:

Name: Shivam Mamgain Email: [email protected] Website: shivammg.blogspot.com Comment: Great tutorial for beginners.

Для вставки воспользуемся командой INSERT .

INSERT INTO comments (name, email, website_url, comment) VALUES ("Shivam Mamgain", "[email protected]", "shivammg.blogspot.com", "Great tutorial for beginners.");

Указывать значение для post_id не нужно т.к. оно сформируется автоматически благодаря настройке AUTOINCREMENT .

Чтобы набить руку можете вставить ещё несколько строк.

ВЫБОРКА

Для выборки данных воспользуемся командой SELECT .

SELECT post_id, name, email, website_url, comment FROM comments;

Этот же запрос может выглядеть так:

SELECT * FROM comments;

В результате из таблицы будут извлечены все строки. Результат может выглядеть без разграничения по колонкам и без заголовка. Чтобы это исправить выполняем:

Для отображения шапки введите.headers ON .

Для отображения колонок выполните команду.mode column .

Выполняем SELECT запрос ещё раз.

Заметка : вид отображения можно изменить, воспользовавшись мета командой.mode .

ОБНОВЛЕНИЕ

Предположим, что поле email для пользователя ‘Shivam Mamgain’ необходимо изменить на ‘[email protected]’. Выполняем следующую команду:

В результате запись будет изменена.

Заметка : Значение в колонке name может быть не уникально, так что в результате работы команды может быть затронуто более одной строки. Для всех пользователей, где значение name = ‘Shivam Mamgain’, поле email будет изменено на ‘[email protected]’. Для изменения какой-то конкретной строки следует её отследить по полю post_id . Мы его определили как PRIMARY KEY , что обеспечивает уникальность значения.