Работа с базами данных

Введение

Примеры

  • 4

    Доступ к базе данных MySQL с использованием MySQLdb

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

    Используйте метод execute курсора для взаимодействия с базой данных и время от времени фиксируйте изменения, используя метод commit объекта соединения.

    Как только все будет сделано, не забудьте закрыть курсор и соединение.

    Вот класс Dbconnect со всем, что вам нужно.

     import MySQLdb
    
    class Dbconnect(object):
    
        def __init__(self):
    
            self.dbconection = MySQLdb.connect(host='host_example',
                                               port=int('port_example'),
                                               user='user_example',
                                               passwd='pass_example',
                                               db='schema_example')
            self.dbcursor = self.dbconection.cursor()
    
        def commit_db(self):
            self.dbconection.commit()
    
        def close_db(self):
            self.dbcursor.close()
            self.dbconection.close()
    
     

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

     db = Dbconnect()
    db.dbcursor.execute('SELECT * FROM %s' % 'table_example')
    
     

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

     db = Dbconnect()
    db.callproc('stored_procedure_name', [parameters] )
    
     

    После выполнения запроса вы можете получить доступ к результатам несколькими способами. Объект курсора является генератором, который может получить все результаты или быть зациклен.

     results = db.dbcursor.fetchall()
    for individual_row in results:
        first_field = individual_row[0]
    
     

    Если вы хотите цикл, использующий непосредственно генератор:

     for individual_row in db.dbcursor:
        first_field = individual_row[0]
    
     

    Если вы хотите зафиксировать изменения в базе данных:

     db.commit_db()
    
     

    Если вы хотите закрыть курсор и соединение:

     db.close_db() 
  • 5

    SQLite

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

     import sqlite3
    
    conn = sqlite3.connect("users.db")
    c = conn.cursor()
    
    c.execute("CREATE TABLE user (name text, age integer)")
    
    c.execute("INSERT INTO user VALUES ('User A', 42)")
    c.execute("INSERT INTO user VALUES ('User B', 43)")
    
    conn.commit()
    
    c.execute("SELECT * FROM user")
    print(c.fetchall())
    
    conn.close()
    
     

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

    Результат этого примера должен быть:

     [(u'User A', 42), (u'User B', 43)]
    
     

    Синтаксис SQLite: углубленный анализ

    Начиная

    1) Импортируйте модуль sqlite, используя

         >>> import sqlite3
    
     

    2) Чтобы использовать модуль, вы должны сначала создать объект Connection, который представляет базу данных. Здесь данные будут храниться в файле example.db:

        >>> conn = sqlite3.connect('users.db')
    
    Alternatively, you can also supply the special name `:memory:` to create a temporary database in RAM, as follows:
    
       >>> conn = sqlite3.connect(':memory:')
    
     

    3) Если у вас есть Connection , вы можете создать Cursor объект и вызвать его execute() метод для выполнения команд SQL:

       ##
    
         c = conn.cursor()
    
         # Create table
         c.execute('''CREATE TABLE stocks
                     (date text, trans text, symbol text, qty real, price real)''')
    
         # Insert a row of data
         c.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
    
         # Save (commit) the changes
         conn.commit()
    
         # We can also close the connection if we are done with it.
         # Just be sure any changes have been committed or they will be lost.
         conn.close()
    
     

    Важные атрибуты и функции Connection

    1) isolation_level

     It is an attribute used to get or set the current isolation level. None for autocommit mode or one of `DEFERRED`, `IMMEDIATE` or `EXCLUSIVE`.
    
    
     

    2) cursor

     The cursor object is used to execute SQL commands and queries.
    
    
     

    3) commit()

     Commits the current transaction.
    
    
     

    4) rollback()

     Rolls back any changes made since the previous call to `commit()`
    
    
     

    5) close()

     Closes the database connection. It does not call `commit()` automatically. If `close()` is called without first calling `commit()` (assuming you are not in autocommit mode) then all changes made will be lost.
    
    
     

    6) total_changes

     An attribute that logs the total number of rows modified, deleted or inserted since the database was opened.
    
     

    7) execute , executemany и executescript

     These functions perform the same way as those of the cursor object. This is a shortcut since calling these functions through the connection object results in the creation of an intermediate cursor object and calls the corresponding method of the cursor object
    
    
     

    8) row_factory

     You can change this attribute to a callable that accepts the cursor and the original row as a tuple and will return the real result row.
    
       def dict_factory(cursor, row):
           d = {}
           for i, col in enumerate(cursor.description):
               d[col[0]] = row[i]
           return d
    
       conn = sqlite3.connect(":memory:")
       conn.row_factory = dict_factory
    
     

    Важные функции Cursor

    1) execute(sql[, parameters])

    Executes a _single_ SQL statement. The SQL statement may be parametrized (i. e. placeholders instead of SQL literals). 
    The sqlite3 module supports two kinds of placeholders: question marks `?` (“qmark style”) and named placeholders `:name` (“named style”).
    
       import sqlite3
       conn = sqlite3.connect(":memory:")
       cur = conn.cursor()
       cur.execute("create table people (name, age)")
    
       who = "Sophia"
       age = 37
       # This is the qmark style:
       cur.execute("insert into people values (?, ?)",
                   (who, age))
    
       # And this is the named style:
       cur.execute("select * from people where name=:who and age=:age",
                   {"who": who, "age": age})  # the keys correspond to the placeholders in SQL
    
       print(cur.fetchone())
    
    
    
    

    Осторожно: не используйте %s для вставки строки в команды SQL , как это может сделать вашу программу уязвимым для инъекции SQL атаки (см https://codecamp.ru/documentation/sql/3517/sql-injection ).

    2) executemany(sql, seq_of_parameters)

    Executes an SQL command against all parameter sequences or mappings found in the sequence sql. The sqlite3 module also allows using an iterator yielding parameters instead of a sequence.
    
       L = [(1, 'abcd', 'dfj', 300),    # A list of tuples to be inserted into the database
            (2, 'cfgd', 'dyfj', 400),
            (3, 'sdd', 'dfjh', 300.50)]                           
    
       conn = sqlite3.connect("test1.db")
       conn.execute("create table if not exists book (id int, name text, author text, price real)")
       conn.executemany("insert into book values (?, ?, ?, ?)", L)
    
       for row in conn.execute("select * from book"):
           print(row)
    
    You can also pass iterator objects as a parameter to executemany, and the function will iterate over the each tuple of values that the iterator returns. The iterator must return a tuple of values.
    
       import sqlite3
    
       class IterChars:
           def __init__(self):
               self.count = ord('a')
    
           def __iter__(self):
               return self
    
           def __next__(self):            # (use next(self) for Python 2)
               if self.count > ord('z'):
                   raise StopIteration
               self.count += 1
               return (chr(self.count - 1),) 
    
       conn = sqlite3.connect("abc.db")
       cur = conn.cursor()
       cur.execute("create table characters(c)")
    
       theIter = IterChars()
       cur.executemany("insert into characters(c) values (?)", theIter)
    
       rows = cur.execute("select c from characters")
       for row in rows:
           print(row[0]),
    
     

    3) executescript(sql_script)

    This is a nonstandard convenience method for executing multiple SQL statements at once. It issues a `COMMIT` statement first, then executes the SQL script it gets as a parameter.
    
    `sql_script` can be an instance of `str` or `bytes`.
    
       import sqlite3
       conn = sqlite3.connect(":memory:")
       cur = conn.cursor()
       cur.executescript("""
            create table person(
                firstname,
                lastname,
                age
            );
    
            create table book(
                title,
                author,
                published
            );
    
            insert into book(title, author, published)
            values (
                'Dirk Gently''s Holistic Detective Agency',
                'Douglas Adams',
                1987
            );
            """)
    
    The next set of functions are used in conjunction with `SELECT` statements in SQL. To retrieve data after executing a `SELECT` statement, you can either treat the cursor as an iterator, call the cursor’s `fetchone()` method to retrieve a single matching row, or call `fetchall()` to get a list of the matching rows.
    
    Example of the iterator form:
    
       import sqlite3
       stocks = [('2006-01-05', 'BUY', 'RHAT', 100, 35.14),
                 ('2006-03-28', 'BUY', 'IBM', 1000, 45.0),
                 ('2006-04-06', 'SELL', 'IBM', 500, 53.0),
                 ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)]
       conn = sqlite3.connect(":memory:")
       conn.execute("create table stocks (date text, buysell text, symb text, amount int, price real)")
       conn.executemany("insert into stocks values (?, ?, ?, ?, ?)", stocks)    
       cur = conn.cursor()
    
       for row in cur.execute('SELECT * FROM stocks ORDER BY price'):
           print(row)
    
       # Output:
       # ('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
       # ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
       # ('2006-04-06', 'SELL', 'IBM', 500, 53.0)
       # ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)
    
     

    4) fetchone()

     Fetches the next row of a query result set, returning a single sequence, or None when no more data is available. 
    
       cur.execute('SELECT * FROM stocks ORDER BY price')
       i = cur.fetchone()
       while(i): 
           print(i)
           i = cur.fetchone()
    
       # Output:
       # ('2006-01-05', 'BUY', 'RHAT', 100, 35.14)
       # ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)
       # ('2006-04-06', 'SELL', 'IBM', 500, 53.0)
       # ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)
    
     

    5) fetchmany(size=cursor.arraysize)

       Fetches the next set of rows of a query result (specified by size), returning a list. If size is omitted, fetchmany returns a single row. An empty list is returned when no more rows are available.
    
         cur.execute('SELECT * FROM stocks ORDER BY price')
         print(cur.fetchmany(2))
    
         # Output:    
         # [('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.0)]
    
     

    6) fetchall()

     Fetches all (remaining) rows of a query result, returning a list.
    
       cur.execute('SELECT * FROM stocks ORDER BY price')
       print(cur.fetchall())
    
       # Output:
       # [('2006-01-05', 'BUY', 'RHAT', 100, 35.14), ('2006-03-28', 'BUY', 'IBM', 1000, 45.0), ('2006-04-06', 'SELL', 'IBM', 500, 53.0), ('2006-04-05', 'BUY', 'MSFT', 1000, 72.0)]
    
    
     

    Типы данных SQLite и Python

    SQLite изначально поддерживает следующие типы: NULL, INTEGER, REAL, TEXT, BLOB.

    Так конвертируются типы данных при переходе с SQL на Python или наоборот.

                     None     <->     NULL
                    int      <->     INTEGER/INT
                    float    <->     REAL/FLOAT
                    str      <->     TEXT/VARCHAR(n)
                    bytes    <->     BLOB
    
    
    
     
  • 1

    Доступ к базе данных PostgreSQL с использованием psycopg2

    psycopg2 является самой популярной базы данных PostgreSQL адаптер , который является одновременно легким и эффективным. Это текущая реализация адаптера PostgreSQL.

    Его основными особенностями являются полная реализация спецификации Python DB API 2.0 и безопасность потоков (несколько потоков могут использовать одно соединение)

    Установление соединения с базой данных и создание таблицы

     import psycopg2
    
    # Establish a connection to the database.
    # Replace parameter values with database credentials.
    conn = psycopg2.connect(database="testpython", 
                            user="postgres",
                            host="localhost",
                            password="abc123",
                            port="5432") 
    
    # Create a cursor. The cursor allows you to execute database queries. 
    cur = conn.cursor()
    
    # Create a table. Initialise the table name, the column names and data type. 
    cur.execute("""CREATE TABLE FRUITS (
                        id          INT ,
                        fruit_name  TEXT,
                        color       TEXT,
                        price       REAL
                )""")
    conn.commit()
    conn.close()
    
    
     

    Вставка данных в таблицу:

     # After creating the table as shown above, insert values into it.
    cur.execute("""INSERT INTO FRUITS (id, fruit_name, color, price)
                   VALUES (1, 'Apples', 'green', 1.00)""")
    
    cur.execute("""INSERT INTO FRUITS (id, fruit_name, color, price)
                   VALUES (1, 'Bananas', 'yellow', 0.80)""")
    
    
     

    Получение данных таблицы:

     # Set up a query and execute it 
    cur.execute("""SELECT id, fruit_name, color, price 
                 FROM fruits""")
    
    # Fetch the data 
    rows = cur.fetchall()
    
    # Do stuff with the data
    for row in rows:
        print "ID = {} ".format(row[0])
        print "FRUIT NAME = {}".format(row[1])
        print("COLOR = {}".format(row[2]))
        print("PRICE = {}".format(row[3]))
    
     

    Результат вышеупомянутого будет:

     ID = 1 
    NAME = Apples
    COLOR = green
    PRICE = 1.0
    
    ID = 2 
    NAME = Bananas
    COLOR = yellow
    PRICE = 0.8
    
     

    Итак, вы идете, вы теперь знаете половину все , что вам нужно знать о psycopg2! :)

  • 1

    База данных Oracle

    Предпосылки:

    • cx_Oracle пакет - Смотрите здесь для всех версий
    • Oracle клиент мгновенного - для Windows x64 , Linux x64

    Настроить:

    • Установите пакет cx_Oracle как:

      sudo rpm -i <YOUR_PACKAGE_FILENAME>

    • Извлеките мгновенный клиент Oracle и установите переменные среды как

     ORACLE_HOME=<PATH_TO_INSTANTCLIENT>
    PATH=$ORACLE_HOME:$PATH
    LD_LIBRARY_PATH=<PATH_TO_INSTANTCLIENT>:$LD_LIBRARY_PATH
    
     

    Создание соединения:

     import cx_Oracle
    
    class OraExec(object):
        _db_connection = None
        _db_cur = None
    
        def __init__(self):
            self._db_connection = 
                cx_Oracle.connect('<USERNAME>/<PASSWORD>@<HOSTNAME>:<PORT>/<SERVICE_NAME>')
            self._db_cur = self._db_connection.cursor()
    
     

    Получить версию базы данных:

     ver = con.version.split(".")
    print ver
     

    Пример вывода: ['12', '1', '0', '2', '0']

    Выполнить запрос: SELECT

     _db_cur.execute("select * from employees order by emp_id")
    for result in _db_cur:
        print result
     

    Вывод будет в кортежах Python:

    (10, 'SYSADMIN', 'IT-INFRA', 7)

    (23, «АССОЦИАТ HR», «ЛЮДСКИЕ РЕСУРСЫ», 6)

    Выполнить запрос: INSERT

     _db_cur.execute("insert into employees(emp_id, title, dept, grade) 
                    values (31, 'MTS', 'ENGINEERING', 7)
    _db_connection.commit()
    
     

    При выполнении вставки / обновления / удалении операции в базе данных Oracle, изменения доступны только в пределах сеанса до commit не выдаются. Когда обновленные данные передаются в базу данных, они становятся доступны другим пользователям и сеансам.

    Выполнить запрос: INSERT с использованием переменных Bind

    Ссылка

    Переменные связывания позволяют вам повторно выполнять операторы с новыми значениями без дополнительных затрат на повторный анализ оператора. Переменные связывания улучшают возможность повторного использования кода и могут снизить риск атак SQL-инъекций.

     rows = [ (1, "First" ),
         (2, "Second" ),
         (3, "Third" ) ]
    _db_cur.bindarraysize = 3
    _db_cur.setinputsizes(int, 10)
    _db_cur.executemany("insert into mytab(id, data) values (:1, :2)", rows)
    _db_connection.commit()
    
    
     

    Закрыть соединение:

     _db_connection.close()
    
     

    Метод close () закрывает соединение. Любые соединения, не закрытые явно, будут автоматически освобождены после завершения сценария.

  • 2

    соединение

    Создание соединения

    Согласно PEP 249, подключение к базе данных должно быть создано с помощью функции connect() конструктора, который возвращает Connection объекта. Аргументы для этого конструктора зависят от базы данных. См. Конкретные темы базы данных для соответствующих аргументов.

     import MyDBAPI
    
    con = MyDBAPI.connect(*database_dependent_args)
    
     

    Этот объект подключения имеет четыре метода:

    1: закрыть

     con.close()
     

    Закрывает соединение мгновенно. Обратите внимание , что соединение автоматически закрывается , если Connection.__del___ вызывается метод. Любые незавершенные транзакции будут автоматически отменены.

    2: совершить

     con.commit()
     

    Передает любую ожидающую транзакцию в базу данных.

    3: откат

     con.rollback()
     

    Откат к началу любой ожидающей транзакции. Другими словами: это отменяет любую не зафиксированную транзакцию в базе данных.

    4: курсор

     cur = con.cursor()
     

    Возвращает Cursor объект. Это используется для выполнения транзакций в базе данных.

  • 0

    Использование sqlalchemy

    Чтобы использовать sqlalchemy для базы данных:

     from sqlalchemy import create_engine
    from sqlalchemy.engine.url import URL
    
    
    url = URL(drivername='mysql',
              username='user',
              password='passwd',
              host='host',
              database='db')
    
    engine = create_engine(url)  # sqlalchemy engine
    
    
     

    Теперь этот движок можно использовать: например, с пандами, чтобы получать кадры данных непосредственно из mysql

     import pandas as pd
    
    con = engine.connect()
    dataframe = pd.read_sql(sql=query, con=con) 

Синтаксис

Параметры

Примечания