Python Language
Toegang tot database

Opmerkingen

Toegang tot MySQL-database met behulp van MySQLdb

Het eerste wat u hoeft te doen, is een verbinding met de database maken met behulp van de verbindingsmethode. Daarna hebt u een cursor nodig die met die verbinding werkt.

Gebruik de uitvoermethode van de cursor om met de database te communiceren en voer de wijzigingen af en toe door met behulp van de methode commit van het verbindingsobject.

Vergeet niet om de cursor en de verbinding te sluiten als alles klaar is.

Hier is een Dbconnect-klasse met alles wat je nodig hebt.

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

Interactie met de database is eenvoudig. Nadat u het object hebt gemaakt, gebruikt u gewoon de methode execute.

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

Als u een opgeslagen procedure wilt oproepen, gebruikt u de volgende syntaxis. Merk op dat de parameterlijst optioneel is.

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

Nadat de zoekopdracht is voltooid, hebt u op verschillende manieren toegang tot de resultaten. Het cursorobject is een generator die alle resultaten kan ophalen of kan worden doorgelust.

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

Als je een lus wilt met behulp van de generator:

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

Als u wijzigingen in de database wilt doorvoeren:

db.commit_db()

Als u de cursor en de verbinding wilt sluiten:

db.close_db()

SQLite

SQLite is een lichtgewicht, op schijven gebaseerde database. Omdat het geen afzonderlijke databaseserver vereist, wordt het vaak gebruikt voor prototyping of voor kleine applicaties die vaak door een enkele gebruiker of door een gebruiker tegelijkertijd worden gebruikt.

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

De bovenstaande code maakt verbinding met de database die is opgeslagen in het bestand met de naam users.db , waarbij het bestand eerst wordt gemaakt als het nog niet bestaat. U kunt via SQL-instructies met de database communiceren.

Het resultaat van dit voorbeeld moet zijn:

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

De SQLite-syntaxis: een diepgaande analyse

Ermee beginnen

1. Importeer de sqlite-module met

   >>> import sqlite3
   

2. Om de module te gebruiken, moet u eerst een verbindingsobject maken dat de database vertegenwoordigt. Hier worden de gegevens opgeslagen in het bestand example.db:

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

   Als alternatief kunt u ook de speciale naam :memory: als volgt een tijdelijke database in RAM maken:

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

3. Nadat u een Connection , kunt u een Cursor object maken en de methode execute() aanroepen om SQL-opdrachten uit te voeren:

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

Belangrijke kenmerken en functies van Connection

1. isolation_level

   Het is een kenmerk dat wordt gebruikt om het huidige isolatieniveau op te halen of in te stellen. Geen voor autocommit-modus of één van DEFERRED , IMMEDIATE of EXCLUSIVE .

2. cursor

   Het cursorobject wordt gebruikt om SQL-opdrachten en -query's uit te voeren.

3. commit()

   Maakt de huidige transactie vast.

4. rollback()

   Hiermee worden alle wijzigingen ongedaan gemaakt die zijn gemaakt sinds de vorige aanroep voor commit()

5. close()

   Sluit de databaseverbinding. Het roept commit() automatisch aan. Als close() wordt aangeroepen zonder eerst commit() roepen (ervan uitgaande dat je je niet in autocommit-modus bevindt), gaan alle aangebrachte wijzigingen verloren.

6. total_changes

   Een kenmerk dat het totale aantal rijen registreert dat is gewijzigd, verwijderd of ingevoegd sinds de database werd geopend.

7. execute , executemany en executescript

   Deze functies werken op dezelfde manier als die van het cursorobject. Dit is een snelkoppeling, omdat het aanroepen van deze functies via het verbindingsobject resulteert in het maken van een tussenliggend cursorobject en de bijbehorende methode van het cursorobject aanroept

8. row_factory

   U kunt dit kenmerk wijzigen in een opvraagbaar dat de cursor en de oorspronkelijke rij als tuple accepteert en de echte resultaatrij retourneert.

   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
   

Belangrijke functies van Cursor

1. execute(sql[, parameters])

   Voert een enkele SQL-instructie uit. De SQL-instructie kan worden geparametriseerd (dwz tijdelijke aanduidingen in plaats van SQL-letterlijke waarden). De sqlite3-module ondersteunt twee soorten tijdelijke aanduidingen: vraagtekens ? ("Qmark-stijl") en benoemde tijdelijke aanduidingen :name ("benoemde stijl").

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

Let op: gebruik %s om tekenreeksen in SQL-opdrachten in te voegen, omdat dit uw programma kwetsbaar kan maken voor een SQL-injectie-aanval (zie SQL-injectie ).

2. executemany(sql, seq_of_parameters)

   Voert een SQL-opdracht uit voor alle parameterreeksen of toewijzingen in de reeks sql. Met de sqlite3-module kan ook een iterator worden gebruikt die parameters oplevert in plaats van een reeks.

   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)
   

   U kunt iterator-objecten ook doorgeven als een parameter om veel uit te voeren, en de functie doorloopt elke tupel waarden die de iterator retourneert. De iterator moet een aantal waarden retourneren.

   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)

   Dit is een niet-standaard gemaksmethode voor het tegelijkertijd uitvoeren van meerdere SQL-instructies. Het geeft eerst een COMMIT instructie uit en voert vervolgens het SQL-script uit dat het als parameter krijgt.

   sql_script kan een instantie van str of 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
        );
        """)
   

   De volgende set functies wordt gebruikt in combinatie met SELECT instructies in SQL. Om gegevens op te halen na het uitvoeren van een SELECT instructie, kunt u de cursor als een iterator behandelen, de methode fetchone() de cursor fetchone() om een enkele overeenkomende rij op te halen, of fetchall() oproepen om een lijst met overeenkomende rijen te krijgen.

   Voorbeeld van het iteratorformulier:

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

   Hiermee wordt de volgende rij van een set met queryresultaten opgehaald, waarbij een enkele reeks of Geen wordt geretourneerd wanneer er geen gegevens meer beschikbaar zijn.

   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)

   Hiermee wordt de volgende reeks rijen van een queryresultaat opgehaald (opgegeven door grootte), waarbij een lijst wordt geretourneerd. Als de grootte wordt weggelaten, retourneert fetchmany een enkele rij. Er wordt een lege lijst geretourneerd wanneer er geen rijen meer beschikbaar zijn.

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

   Haalt alle (resterende) rijen van een queryresultaat op en retourneert een lijst.

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

Gegevenstypen SQLite en Python

SQLite ondersteunt native de volgende typen: NULL, INTEGER, REAL, TEXT, BLOB.

Dit is hoe de datatypes worden geconverteerd bij het verplaatsen van SQL naar Python of vice versa.

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

PostgreSQL Database toegang met behulp van psycopg2

psycopg2 is de populairste PostgreSQL-database-adapter die zowel lichtgewicht als efficiënt is. Het is de huidige implementatie van de PostgreSQL-adapter.

De belangrijkste kenmerken zijn de volledige implementatie van de Python DB API 2.0-specificatie en de threadveiligheid (verschillende threads kunnen dezelfde verbinding delen)

Verbinding maken met de database en een tabel maken

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

Gegevens in de tabel invoegen:

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

Tabelgegevens ophalen:

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

De output van het bovenstaande zou zijn:

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

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

En dus, daar ga je, je weet nu de helft van alles wat je moet weten over psycopg2 ! :)

Oracle-database

Vereisten:

• cx_Oracle-pakket - Zie hier voor alle versies
• Oracle instant client - voor Windows x64 , Linux x64

Opstelling:

• Installeer het cx_Oracle-pakket als:

  sudo rpm -i <YOUR_PACKAGE_FILENAME>

• Pak de Oracle instant-client uit en stel omgevingsvariabelen in als:

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

Verbinding maken:

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

Download databaseversie:

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

Voorbeelduitvoer: ['12', '1', '0', '2', '0']

Zoekopdracht uitvoeren: SELECT

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

De uitvoer is in Python-tupels:

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

(23, 'HR ASSOCIATE', 'MENSELIJKE HULPBRONNEN', 6)

Zoekopdracht uitvoeren: INVOEGEN

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

Wanneer u invoeg- / update- / verwijderbewerkingen uitvoert in een Oracle-database, zijn de wijzigingen alleen beschikbaar binnen uw sessie totdat de commit is uitgegeven. Wanneer de bijgewerkte gegevens zijn vastgelegd in de database, zijn deze beschikbaar voor andere gebruikers en sessies.

Query uitvoeren: INSERT met behulp van Bind-variabelen

Referentie

Bind variabelen stellen u in staat om instructies met nieuwe waarden opnieuw uit te voeren, zonder dat u de instructie opnieuw hoeft te parseren. Bind variabelen verbeteren herbruikbaarheid van code en kunnen het risico op SQL Injection-aanvallen verminderen.

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

Hechte band:

_db_connection.close()

De methode close () sluit de verbinding. Verbindingen die niet expliciet zijn gesloten, worden automatisch vrijgegeven wanneer het script eindigt.

Verbinding

Verbinding maken

Volgens PEP 249 moet de verbinding met een database tot stand worden gebracht met behulp van een connect() constructor, die een Connection object retourneert. De argumenten voor deze constructor zijn database-afhankelijk. Raadpleeg de database-specifieke onderwerpen voor de relevante argumenten.

import MyDBAPI

con = MyDBAPI.connect(*database_dependent_args)

Dit verbindingsobject heeft vier methoden:

1: sluiten

con.close()

Verbindt de verbinding onmiddellijk. Merk op dat de verbinding automatisch wordt Connection.__del___ als de methode Connection.__del___ wordt aangeroepen. Alle lopende transacties worden impliciet teruggedraaid.

2: vastleggen

con.commit()

Maakt elke lopende transactie naar de database.

3: terugdraaien

con.rollback()

Keert terug naar het begin van een lopende transactie. Met andere woorden: hiermee wordt elke niet-gecommitteerde transactie in de database geannuleerd.

4: cursor

cur = con.cursor()

Retourneert een Cursor object. Dit wordt gebruikt om transacties in de database uit te voeren.

Sqlalchemy gebruiken

Om sqlalchemy voor database te gebruiken:

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

Nu kan deze motor worden gebruikt: bijv. Met panda's om dataframes rechtstreeks van mysql op te halen

import pandas as pd

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