Python Language
Acceso a la base de datos

Observaciones

Accediendo a la base de datos MySQL usando MySQLdb

Lo primero que debe hacer es crear una conexión a la base de datos utilizando el método de conexión. Después de eso, necesitará un cursor que operará con esa conexión.

Utilice el método de ejecución del cursor para interactuar con la base de datos y, de vez en cuando, confirme los cambios utilizando el método de confirmación del objeto de conexión.

Una vez hecho todo, no olvides cerrar el cursor y la conexión.

Aquí hay una clase Dbconnect con todo lo que necesitas.

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

Interactuar con la base de datos es simple. Después de crear el objeto, simplemente use el método de ejecución.

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

Si desea llamar a un procedimiento almacenado, use la siguiente sintaxis. Tenga en cuenta que la lista de parámetros es opcional.

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

Una vez que se realiza la consulta, puede acceder a los resultados de varias maneras. El objeto del cursor es un generador que puede obtener todos los resultados o ser enlazado.

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

Si quieres un loop usando directamente el generador:

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

Si desea confirmar los cambios en la base de datos:

db.commit_db()

Si quieres cerrar el cursor y la conexión:

db.close_db()

SQLite

SQLite es una base de datos ligera, basada en disco. Dado que no requiere un servidor de base de datos separado, a menudo se usa para hacer prototipos o para aplicaciones pequeñas que a menudo usan un solo usuario o un usuario en un momento dado.

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

El código anterior se conecta a la base de datos almacenada en el archivo llamado users.db , creando primero el archivo si aún no existe. Puede interactuar con la base de datos a través de sentencias de SQL.

El resultado de este ejemplo debe ser:

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

La sintaxis de SQLite: un análisis en profundidad

Empezando

1. Importar el módulo sqlite usando

   >>> import sqlite3
   

2. Para usar el módulo, primero debe crear un objeto de conexión que represente la base de datos. Aquí los datos se almacenarán en el archivo example.db:

   >>> conn = sqlite3.connect('users.db')
   

   Alternativamente, también puede proporcionar el nombre especial :memory: para crear una base de datos temporal en la RAM, de la siguiente manera:

   >>> conn = sqlite3.connect(':memory:')
   

3. Una vez que tenga una Connection , puede crear un objeto Cursor y llamar a su método execute() para ejecutar comandos 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()
   

Atributos importantes y funciones de Connection

1. isolation_level

   Es un atributo utilizado para obtener o establecer el nivel de aislamiento actual. Ninguno para el modo de confirmación automática o uno de DEFERRED , IMMEDIATE o EXCLUSIVE .

2. cursor

   El objeto del cursor se utiliza para ejecutar comandos y consultas SQL.

3. commit()

   Confirma la transacción actual.

4. rollback()

   Deshace los cambios realizados desde la llamada anterior a commit()

5. close()

   Cierra la conexión de la base de datos. No llama a commit() automáticamente. Si se llama a close() sin llamar primero a commit() (suponiendo que no esté en modo de commit() automática), se perderán todos los cambios realizados.

6. total_changes

   Un atributo que registra el número total de filas modificadas, eliminadas o insertadas desde que se abrió la base de datos.

7. execute , executemany y executescript

   Estas funciones se realizan de la misma manera que las del objeto cursor. Este es un atajo ya que llamar a estas funciones a través del objeto de conexión da como resultado la creación de un objeto de cursor intermedio y llama al método correspondiente del objeto de cursor

8. row_factory

   Puede cambiar este atributo a un llamable que acepte el cursor y la fila original como una tupla y devolverá la fila del resultado real.

   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
   

Funciones importantes del Cursor

1. execute(sql[, parameters])

   Ejecuta una sola sentencia SQL. La declaración SQL puede estar parametrizada (es decir, marcadores de posición en lugar de literales de SQL). El módulo sqlite3 admite dos tipos de marcadores de posición: ¿signos de interrogación ? ("Estilo de qmark") y marcadores de posición con :name ("estilo con nombre").

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

Cuidado: no utilice %s para insertar cadenas en los comandos SQL, ya que puede hacer que su programa sea vulnerable a un ataque de inyección de SQL (consulte Inyección de SQL ).

2. executemany(sql, seq_of_parameters)

   Ejecuta un comando SQL contra todas las secuencias de parámetros o asignaciones encontradas en la secuencia sql. El módulo sqlite3 también permite usar un iterador para producir parámetros en lugar de una secuencia.

   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)
   

   También puede pasar objetos de iterador como un parámetro a muchos, y la función se repetirá sobre cada tupla de valores que devuelve el iterador. El iterador debe devolver una tupla de valores.

   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)

   Este es un método de conveniencia no estándar para ejecutar varias declaraciones SQL a la vez. Primero emite una instrucción COMMIT , luego ejecuta el script SQL que obtiene como parámetro.

   sql_script puede ser una instancia de str o 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
        );
        """)
   

   El siguiente conjunto de funciones se usa junto con las SELECT en SQL. Para recuperar datos después de ejecutar una instrucción SELECT , puede tratar el cursor como un iterador, llamar al método fetchone() del cursor para recuperar una sola fila coincidente, o llamar a fetchall() para obtener una lista de las filas correspondientes.

   Ejemplo de la forma iterador:

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

   Obtiene la siguiente fila de un conjunto de resultados de consulta, devolviendo una secuencia única o Ninguno cuando no hay más datos disponibles.

   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)

   Obtiene el siguiente conjunto de filas de un resultado de consulta (especificado por tamaño), devolviendo una lista. Si se omite el tamaño, fetchmany devuelve una sola fila. Se devuelve una lista vacía cuando no hay más filas disponibles.

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

   Obtiene todas las filas (restantes) de un resultado de consulta, devolviendo una lista.

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

Tipos de datos SQLite y Python

SQLite admite de forma nativa los siguientes tipos: NULL, INTEGER, REAL, TEXT, BLOB.

Así es como se convierten los tipos de datos al pasar de SQL a Python o viceversa.

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

Acceso a la base de datos PostgreSQL usando psycopg2

psycopg2 es el adaptador de base de datos PostgreSQL más popular que es ligero y eficiente. Es la implementación actual del adaptador PostgreSQL.

Sus características principales son la implementación completa de la especificación Python DB API 2.0 y la seguridad de subprocesos (varios subprocesos pueden compartir la misma conexión)

Estableciendo una conexión a la base de datos y creando una tabla.

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

Insertando datos en la tabla:

# 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)""")

Recuperando datos de la tabla:

# 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]))

La salida de lo anterior sería:

ID = 1 
NAME = Apples
COLOR = green
PRICE = 1.0

ID = 2 
NAME = Bananas
COLOR = yellow
PRICE = 0.8

Y así, ahí tienes, ¡ahora sabes la mitad de todo lo que necesitas saber sobre psycopg2 ! :)

Base de datos Oracle

Pre-requisitos:

• Paquete cx_Oracle - Vea aquí para todas las versiones
• Cliente instantáneo de Oracle - Para Windows x64 , Linux x64

Preparar:

• Instala el paquete cx_Oracle como:

  sudo rpm -i <YOUR_PACKAGE_FILENAME>

• Extraiga el cliente instantáneo de Oracle y establezca las variables de entorno como:

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

Creando una conexión:

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

Obtener la versión de la base de datos:

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

Salida de muestra: ['12', '1', '0', '2', '0']

Ejecutar consulta: SELECCIONAR

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

La salida será en tuplas de Python:

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

(23, 'HR ASSOCIATE', 'RECURSOS HUMANOS', 6)

Ejecutar consulta: INSERTAR

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

Cuando realiza operaciones de inserción / actualización / eliminación en una base de datos Oracle, los cambios solo están disponibles dentro de su sesión hasta commit se emita la commit . Cuando los datos actualizados se confirman en la base de datos, están disponibles para otros usuarios y sesiones.

Ejecutar consulta: INSERTAR utilizando variables Bind

Referencia

Las variables de vinculación le permiten volver a ejecutar sentencias con nuevos valores, sin la sobrecarga de volver a analizar la sentencia. Las variables de enlace mejoran la reutilización del código y pueden reducir el riesgo de ataques de inyección de 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()

Conexión cercana:

_db_connection.close()

El método close () cierra la conexión. Cualquier conexión no cerrada explícitamente se liberará automáticamente cuando finalice el script.

Conexión

Creando una conexión

Según PEP 249, la conexión a una base de datos debe establecerse mediante un constructor connect() , que devuelve un objeto Connection . Los argumentos para este constructor son dependientes de la base de datos. Consulte los temas específicos de la base de datos para los argumentos relevantes.

import MyDBAPI

con = MyDBAPI.connect(*database_dependent_args)

Este objeto de conexión tiene cuatro métodos:

1: cerrar

con.close()

Cierra la conexión al instante. Tenga en cuenta que la conexión se cierra automáticamente si se llama al método Connection.__del___ . Todas las transacciones pendientes se revertirán implícitamente.

2: cometer

con.commit()

Se compromete cualquier transacción pendiente a la base de datos.

3: retroceso

con.rollback()

Retrocede al inicio de cualquier transacción pendiente. En otras palabras: esto cancela cualquier transacción no confirmada a la base de datos.

4: cursor

cur = con.cursor()

Devuelve un objeto Cursor . Esto se utiliza para hacer transacciones en la base de datos.

Usando sqlalchemy

Para usar sqlalchemy para la base de datos:

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

Ahora se puede usar este motor: por ejemplo, con pandas para obtener marcos de datos directamente desde mysql

import pandas as pd

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