Учебник по sqlite3 в python

PyMySQL fetchAll

Метод позволяет извлечь все (оставшиеся) строки результата запроса, возвращая их в виде последовательности последовательностей.


Python

#!/usr/bin/python3 # -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect(‘localhost’, ‘user17’, ‘s$cret’, ‘testdb’)

with con:

cur = con.cursor() cur.execute(«SELECT * FROM cities»)

rows = cur.fetchall()

for row in rows: print(«{0} {1} {2}».format(row, row, row))

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

#!/usr/bin/python3 # -*- coding: utf-8 -*-  

importpymysql

con=pymysql.connect(‘localhost’,’user17′,

‘s$cret’,’testdb’)

withcon

cur=con.cursor()

cur.execute(«SELECT * FROM cities»)

rows=cur.fetchall()

forrow inrows

print(«{0} {1} {2}».format(row,row1,row2))

В данном примере из таблицы базы данных выводятся все города (cities).

Python

cur.execute(«SELECT * FROM cities»)

1 cur.execute(«SELECT * FROM cities»)

Python

rows = cur.fetchall()

1 rows=cur.fetchall()

Python

for row in rows: print(«{0} {1} {2}».format(row, row, row))

1 2

forrow inrows

print(«{0} {1} {2}».format(row,row1,row2))

Shell

$ ./retrieve_all.py 1 Bratislava 432000 2 Budapest 1759000 3 Prague 1280000 4 Warsaw 1748000 5 Los Angeles 3971000 6 New York 8550000 7 Edinburgh 464000 8 Berlin 3671000

1 2 3 4 5 6 7 8 9

$.retrieve_all.py

1Bratislava432000

2Budapest1759000

3Prague1280000

4Warsaw1748000

5Los Angeles3971000

6NewYork8550000

7Edinburgh464000

8Berlin3671000

Готовим инвентарь для дальнейшей комфортной работы

  • Python имеет встроенную поддержку SQLite базы данных, для этого вам не надо ничего дополнительно устанавливать, достаточно в скрипте указать импорт стандартной библиотеки

  • Скачаем тестовую базу данных, с которой будем работать. В данной статье будет использоваться открытая (MIT лицензия) тестовая база данных “Chinook”. Скачать ее можно по следующим ссылкам:

    Нам нужен для работы только бинарный файл “Chinook_Sqlite.sqlite”.

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

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

    Вы можете использовать (последние два варианта кросс-платформенные и бесплатные):

    • Привычную вам утилиту для работы с базой в составе вашей IDE;

Prevent SQL Injection

When query values are provided by the user, you should escape the values.

This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.

The mysql.connector module has methods to escape query values:

Example

Escape query values by using the placholder method:


import mysql.connectormydb = mysql.connector.connect(  host=»localhost»,  user=»yourusername»,  password=»yourpassword»,  database=»mydatabase»)mycursor = mydb.cursor()sql = «SELECT * FROM customers WHERE address = %s»adr = («Yellow Garden 2», ) mycursor.execute(sql, adr)myresult = mycursor.fetchall()for x in myresult:  print(x)

Exceptions

The module should make all error information available through these exceptions or subclasses thereof:

Exception raised for important warnings like data truncations while inserting, etc. It must be a subclass of the Python StandardError (defined in the module exceptions).
Exception that is the base class of all other error exceptions. You can use this to catch all errors with one single except statement. Warnings are not considered errors and thus should not use this class as base. It must be a subclass of the Python StandardError (defined in the module exceptions).
Exception raised for errors that are related to the database interface rather than the database itself. It must be a subclass of .
Exception raised for errors that are related to the database. It must be a subclass of .
Exception raised for errors that are due to problems with the processed data like division by zero, numeric value out of range, etc. It must be a subclass of .
Exception raised for errors that are related to the database’s operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, a memory allocation error occurred during processing, etc. It must be a subclass of .
Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails. It must be a subclass of .
Exception raised when the database encounters an internal error, e.g. the cursor is not valid anymore, the transaction is out of sync, etc. It must be a subclass of .
Exception raised for programming errors, e.g. table not found or already exists, syntax error in the SQL statement, wrong number of parameters specified, etc. It must be a subclass of .
Exception raised in case a method or database API was used which is not supported by the database, e.g. requesting a on a connection that does not support transaction or has transactions turned off. It must be a subclass of .

This is the exception inheritance layout:

StandardError
|__Warning
|__Error
   |__InterfaceError
   |__DatabaseError
      |__DataError
      |__OperationalError
      |__IntegrityError
      |__InternalError
      |__ProgrammingError
      |__NotSupportedError

Ограничения

Старые версии SQLite были спроектированы без каких-либо ограничений, единственным условием было то, чтобы база данных умещалась в памяти, в которой все вычисления производились при помощи 32-разрядных целых чисел. Это создавало определённые проблемы. Из-за того, что верхние пределы не были определены и соответственно должным образом протестированы, часто обнаруживались ошибки при использовании SQLite в достаточно экстремальных условиях. Поэтому в новых версиях SQLite были введены пределы, которые теперь проверяются вместе с общим набором тестов.

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

Описание Значение Константа в исходном коде
Максимальная длина строки или BLOB-поля 1 000 000 000 SQLITE_MAX_LENGTH
Максимальное количество колонок 2 000 SQLITE_MAX_COLUMN
Максимальная длина SQL-выражения 1 000 000 000 SQLITE_MAX_SQL_LENGTH
Максимальное количество таблиц в выражениях с JOIN 64
Максимальная глубина дерева выражений 1 000 SQLITE_MAX_EXPR_DEPTH
Максимальное количество аргументов функции 127 SQLITE_MAX_FUNCTION_ARG
Максимальное количество термов в объединённом выражении с SELECT 500 SQLITE_MAX_COMPOUND_SELECT
Максимальная длина шаблона как аргумента операторов LIKE или GLOB 50 000 SQLITE_MAX_LIKE_PATTERN_LENGTH
Максимальное количество символов-заменителей в одном SQL-выражении 999 SQLITE_MAX_VARIABLE_NUMBER
Максимальная глубина рекурсии триггеров 1 000 SQLITE_MAX_TRIGGER_DEPTH
Максимальное количество присоединённых баз 10 SQLITE_MAX_ATTACHED
Максимальный размер страницы базы данных 65 536 SQLITE_MAX_PAGE_SIZE
Максимальное количество страниц в файле базы данных 1 073 741 823 SQLITE_MAX_PAGE_COUNT

На текущий момент[когда?] только значение SQLITE_MAX_PAGE_SIZE не может быть больше заданного по умолчанию. Таким образом, не изменяя SQLITE_MAX_PAGE_COUNT, можно сказать, что максимальный размер файла базы данных составляет примерно 140 ТБ (247 Б).

Некоторые ограничения можно менять в сторону уменьшения во время исполнения программы при помощи задания категории и соответствующего значения функции sqlite3_limit():

int sqlite3_limit(sqlite3*, int id, int newVal)
Категория Описание
SQLITE_LIMIT_LENGTH Максимальная длина любой строки или BLOB-поля или ряда
SQLITE_LIMIT_SQL_LENGTH Максимальная длина SQL-выражения
SQLITE_LIMIT_COLUMN Максимальное количество колонок в определении таблицы или результате выборки, или индексе, или выражениях с операторами ORDER BY или GROUP BY
SQLITE_LIMIT_EXPR_DEPTH Максимальная глубина разобранного дерева любого выражения
SQLITE_LIMIT_COMPOUND_SELECT Максимальное количество термов в объединённом выражении с SELECT
SQLITE_LIMIT_VDBE_OP Максимальное количество инструкций программы виртуальной машины выполняемого SQL-выражения
SQLITE_LIMIT_FUNCTION_ARG Максимально количество аргументов функции
SQLITE_LIMIT_ATTACHED Максимальное количество присоединённых баз
SQLITE_LIMIT_LIKE_PATTERN_LENGTH Максимальная длина шаблона как аргумента операторов LIKE или GLOB
SQLITE_LIMIT_VARIABLE_NUMBER Максимальное количество переменных в SQL-выражении, которые можно связать
SQLITE_LIMIT_TRIGGER_DEPTH Максимальная глубина рекурсии триггеров

Это может быть полезным, если SQLite используется в веб-приложениях, так как уменьшенные пределы могут предотвратить DoS-атаки со стороны недоверяемых внешних клиентов.

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

Создание базы данных в 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) «»»)

1 2 3 4 5 6 7 8 9 10

importsqlite3

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()

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

# Вставляем данные в таблицу

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 чтобы вставить запись в нашу базу данных

Обратите внимание на то, что каждый объект находится в одинарных кавычках. Это может усложнить работу, если вам нужно вставить строчки, которые содержат одинарные кавычки

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

PyMySQL — узнаем версию MySQL

В следующем примере показано, как отобразить текущую версию MySQL.

Python

#!/usr/bin/python3 # -*- coding: utf-8 -*-

import pymysql

con = pymysql.connect(‘localhost’, ‘user17’, ‘s$cret’, ‘mydb’)

with con: cur = con.cursor() cur.execute(«SELECT VERSION()»)

version = cur.fetchone() print(«Database version: {}».format(version))

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

#!/usr/bin/python3 # -*- coding: utf-8 -*-  

importpymysql

con=pymysql.connect(‘localhost’,’user17′,

‘s$cret’,’mydb’)

withcon

cur=con.cursor()

cur.execute(«SELECT VERSION()»)


version=cur.fetchone()

print(«Database version: {}».format(version))

Для того чтобы узнать версию MySQL, можно использовать команду .

Python

import pymysql

1 importpymysql

Для этого потребуется импортировать модуль.

Python

con = pymysql.connect(‘localhost’, ‘user17’, ‘s$cret’, ‘mydb’)

1 2

con=pymysql.connect(‘localhost’,’user17′,

‘s$cret’,’mydb’)

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

  • имя хоста;
  • имя пользователя;
  • пароль;
  • название базы данных.

Python

with con:

1 withcon

При помощи интерпретатор Python автоматически открывает доступные ресурсы. Он также обрабатывает возможные ошибки.

Python

cur = con.cursor()

1 cur=con.cursor()

Из объекта подключения создается курсор. Курсор используется для перемещения записей из набора результатов.

Python

cur.execute(«SELECT VERSION()»)

1 cur.execute(«SELECT VERSION()»)

Для использования команды SQL вызывается метод курсора .

Python

version = cur.fetchone()

1 version=cur.fetchone()

Метод позволяет вызвать следующую строку из набора результатов запроса, показывая только одну запись. В том случае, если доступных данных нет, выводится .

Python

print(«Database version: {}».format(version))

1 print(«Database version: {}».format(version))

Таким образом, версия базы данных выводится на экран.

Shell

$ ./version.py Database version: 5.7.23-0ubuntu0.16.04.1

1 2

$.version.py

Database version5.7.23-0ubuntu0.16.04.1

Это результат вывода.

Python NumPy

NumPy IntroNumPy Getting StartedNumPy Creating ArraysNumPy Array IndexingNumPy Array SlicingNumPy Data TypesNumPy Copy vs ViewNumPy Array ShapeNumPy Array ReshapeNumPy Array IteratingNumPy Array JoinNumPy Array SplitNumPy Array SearchNumPy Array SortNumPy Array FilterNumPy Random Random Intro Data Distribution Random Permutation Seaborn Module Normal Distribution Binomial Distribution Poisson Distribution Uniform Distribution Logistic Distribution Multinomial Distribution Exponential Distribution Chi Square Distribution Rayleigh Distribution Pareto Distribution Zipf Distribution

NumPy ufunc ufunc Intro ufunc Create Function ufunc Simple Arithmetic ufunc Rounding Decimals ufunc Logs ufunc Summations ufunc Products ufunc Differences ufunc Finding LCM ufunc Finding GCD ufunc Trigonometric ufunc Hyperbolic ufunc Set Operations

Python NumPy

NumPy IntroNumPy Getting StartedNumPy Creating ArraysNumPy Array IndexingNumPy Array SlicingNumPy Data TypesNumPy Copy vs ViewNumPy Array ShapeNumPy Array ReshapeNumPy Array IteratingNumPy Array JoinNumPy Array SplitNumPy Array SearchNumPy Array SortNumPy Array FilterNumPy Random Random Intro Data Distribution Random Permutation Seaborn Module Normal Distribution Binomial Distribution Poisson Distribution Uniform Distribution Logistic Distribution Multinomial Distribution Exponential Distribution Chi Square Distribution Rayleigh Distribution Pareto Distribution Zipf Distribution

NumPy ufunc ufunc Intro ufunc Create Function ufunc Simple Arithmetic ufunc Rounding Decimals ufunc Logs ufunc Summations ufunc Products ufunc Differences ufunc Finding LCM ufunc Finding GCD ufunc Trigonometric ufunc Hyperbolic ufunc Set Operations

Обнаружение SQL-инъекций с использованием абстрактных синтаксических деревьев

Наиболее распространенная ошибка, которая приводит к SQL-инъекциям в коде Python, заключается в использовании форматирования строк в операторах SQL. Чтобы найти SQL-инъекцию в коде Python, нам нужно найти форматирование строки в вызове функции execute или executemany.

Существует как минимум три способа отформатировать строку в Python:

c.execute("SELECT username, rank FROM users WHERE rank = '{0}'".format(rank))
c.execute("SELECT username, rank FROM users WHERE rank = '%s'" % rank)
c.execute(f"SELECT username, rank FROM users WHERE rank = `{rank}`")

Кроме того, я хочу отслеживать простые назначения переменных:

q = "SELECT username, rank FROM users qqqq WHERE rank = '%s'" % rank
c.execute(q)

Чтобы уменьшить вероятность ложных срабатываний, нам также необходимо проверить, содержит ли аргумент оператор SQL.


Вот как выглядит детектор AST SQL-инъекций:

import ast
import astor
import re

SQL_FUNCTIONS = {
    'execute',
    'executemany',
}
SQL_OPERATORS = re.compile('SELECT|UPDATE|INSERT|DELETE', re.IGNORECASE)


class ASTWalker(ast.NodeVisitor):
    def __init__(self):
        self.candidates = []
        self.variables = {}

    def visit_Call(self, node):
        # Search for function calls with attributes, e.g. cursor.execute
        if isinstance(node.func, ast.Attribute) and node.func.attr in SQL_FUNCTIONS:
            self._check_function_call(node)
        # Traverse child nodes
        self.generic_visit(node)

    def visit_Assign(self, node):
        if not isinstance(node.targets, ast.Name):
            return self.generic_visit(node)

        variable, value = node.targets.id, node.value
        # Some variable assignments can store SQL queries with string formatting.
        # Save them for later.
        if isinstance(value, (ast.Call, ast.BinOp, ast.Mod)):
            self.variables = node.value
        self.generic_visit(node)

    def _check_function_call(self, node):
        if not node.args:
            return
        first_argument = node.args
        query = self._check_function_argument(first_argument)
        if query and re.search(SQL_OPERATORS, query):
            self.candidates.append(node)

    def _check_function_argument(self, argument):
        query = None
        if isinstance(argument, ast.Call) and argument.func.attr == 'format':
            # Formatting using .format
            query = argument.func.value.s
        elif isinstance(argument, ast.BinOp) and isinstance(argument.op, ast.Mod):
            # Old-style formatting, .e.g. '%s' % 'string'
            query = argument.left.s
        elif isinstance(argument, ast.JoinedStr) and len(argument.values) > 1:
            # New style f-strings
            query = argument.values.s
        elif isinstance(argument, ast.Name) and argument.id in self.variables:
            # If execute function takes a variable as an argument, try to track its real value.
            query = self._check_function_argument(self.variables)
        return query


if __name__ == '__main__':
    code = open('webapp.py', 'r').read()
    tree = ast.parse(code)
    ast_walker = ASTWalker()
    ast_walker.visit(tree)

    for candidate in ast_walker.candidates:
        print(astor.to_source(candidate).strip())

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

Для этой задачи нам нужно обработать только два выражения — Call и Assign. Первый запускается при вызове функции, а второй — при назначении переменной. Наш класс найдет все вызовы функций execute независимо от того, как код отформатирован или структурирован.

Этот скрипт производит много ложных срабатываний, но, несмотря на это, работает довольно хорошо. Очень трудно автоматически отслеживать, если переменная приходит из HTTP-запроса и может быть изменена пользователем. Поэтому каждая находка должна быть проверена человеческим глазом.

Тестирование скрипта на данных GitHub

Чтобы протестировать мой сценарий, я собрал около 100 сценариев Python с помощью поиска на GitHub и смог найти четыре репозитория, в которых есть уязвимости в SQL-инъекциях. Чтобы получить такой хороший уровень обнаружения, вам также нужно придумать подходящий поисковый запрос. Я не буду размещать его по этическим причинам :).

Есть много вещей, которые могут быть улучшены в сценарии. Например, этот запрос использует форматирование, но он не может быть использован в инъекции:

c.execute("SELECT * FROM users WHERE year_registered = {0} ".format(int(year)))

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

Оригинальная статья: Detecting SQL injections in Python code using AST

Spread the love

Соединение с базой данных

Для установки соединения с базой данных существует функция MySQLdb.connect.

Так же можно указать port, unix_socket, connect_timeout, compress, charset, sql_mode и некоторые другие (см. документацию)

В случае успеха, функция возвращает объект класса Connection. Экземпляр con класса Connection обладает следующими методами.

  • con.close() – закрывает соединение с сервером базы данных.
  • con.commit() – подтверждает все незавершенные транзакции
  • con.rollback() – откатывает все изменение в базе данных до момента когда были запущенные незавершённые транзакции.
  • con.cursor() – создаёт новый курсор. Это экземпляр класса Cursor. Курсор – это объект, который используется для SQL запросов и получения результата.
  • con.autocommit() – используется для автоматического завершения транзакции.
  • con.affected_rows() — возвратите число строк, на которые повлиял последний запрос. Лучше использовать cur.rowcount(). (не стандартный)
  • con.errno() – возвращает номер ошибки если она произошла.
  • con.error() – возвращает описание ошибки если она произошла.
  • con.select_db() – используется для выбора базы данных. (не стандартный)
  • con.insert_id() – Возвращает ID, сгенерированный для столбца AUTO_INCREMENT предыдущим запросом
  • con.escape() – используется для экранирования символов (см. документацию)
  • con.escape_string() – используется для экранирования символов (см. документацию)
  • con.string_literal() – используется для экранирования символов (см. документацию)

Так же модуль имеет ещё ряд нестандартных методов, при желании с ними можно ознакомиться в документации.

Python NumPy

NumPy IntroNumPy Getting StartedNumPy Creating ArraysNumPy Array IndexingNumPy Array SlicingNumPy Data TypesNumPy Copy vs ViewNumPy Array ShapeNumPy Array ReshapeNumPy Array IteratingNumPy Array JoinNumPy Array SplitNumPy Array SearchNumPy Array SortNumPy Array FilterNumPy Random Random Intro Data Distribution Random Permutation Seaborn Module Normal Distribution Binomial Distribution Poisson Distribution Uniform Distribution Logistic Distribution Multinomial Distribution Exponential Distribution Chi Square Distribution Rayleigh Distribution Pareto Distribution Zipf Distribution

NumPy ufunc ufunc Intro ufunc Create Function ufunc Simple Arithmetic ufunc Rounding Decimals ufunc Logs ufunc Summations ufunc Products ufunc Differences ufunc Finding LCM ufunc Finding GCD ufunc Trigonometric ufunc Hyperbolic ufunc Set Operations

The SQL database query language

database

sqlite3 user.db SELECT * FROM Users;SELECT count(*) FROM Users;SELECT name FROM Users;SELECT * FROM Users WHERE id = 2;DELETE FROM Users WHERE id = 6;

We can use those queries in a Python program:

import sqlite3 as liteimport syscon = lite.connect('user.db')with con:        cur = con.cursor()        cur.execute("SELECT * FROM Users")    rows = cur.fetchall()    for row in rows:        print row

This will output all data in the Users table from the database:

$ python get.py (1, u'Michelle')(2, u'Sonya')(3, u'Greg')

Creating a user information database

We can structure our data across multiple tables. This keeps our data structured, fast and organized. If we would have a single table to store everything, we would quickly have a big chaotic mess. What we will do is create multiple tables and use them in a combination. We create two tables:

Users:

SQL Table

Jobs:

SQL Table

To create these tables, you can do that by hand in the GUI or use the script below:

import sqlite3 as liteimport syscon = lite.connect('system.db')with con:    cur = con.cursor()        cur.execute("CREATE TABLE Users(Id INT, Name TEXT)")    cur.execute("INSERT INTO Users VALUES(1,'Michelle')")    cur.execute("INSERT INTO Users VALUES(2,'Howard')")    cur.execute("INSERT INTO Users VALUES(3,'Greg')")    cur.execute("CREATE TABLE Jobs(Id INT, Uid INT, Profession TEXT)")    cur.execute("INSERT INTO Jobs VALUES(1,1,'Scientist')")    cur.execute("INSERT INTO Jobs VALUES(2,2,'Marketeer')")    cur.execute("INSERT INTO Jobs VALUES(3,3,'Developer')")

The jobs table has an extra parameter, Uid. We use that to connect the two tables in an SQL query:

SELECT users.name, jobs.profession FROM jobs INNER JOIN users ON users.ID = jobs.uid

You can incorporate that SQL query in a Python script:

import sqlite3 as liteimport syscon = lite.connect('system.db')with con:        cur = con.cursor()        cur.execute("SELECT users.name, jobs.profession FROM jobs INNER JOIN users ON users.ID = jobs.uid")    rows = cur.fetchall()    for row in rows:        print row

It should output:

$ python get2.py(u'Michelle', u'Scientist')(u'Howard', u'Marketeer')(u'Greg', u'Developer')

You may like: Databases and data analysis


С этим читают