Python Language
데이터베이스 액세스

Django Flask HTML GNU/Linux matplotlib MySQL opencv pandas Regular Expressions tensorflow
수색…


비고

파이썬은 많은 다른 유형의 데이터베이스를 처리 할 수 ​​있습니다. 이러한 유형마다 각각 다른 API가 존재합니다. 그래서 다른 API들 사이의 유사점을 권장합니다. PEP 249가 도입되었습니다.

이 API는 데이터베이스에 액세스하는 데 사용되는 Python 모듈 간의 유사성을 권장하도록 정의되었습니다. 이를 통해 일관성을 확보하여보다 쉽게 ​​이해할 수있는 모듈, 일반적으로 데이터베이스간에 이식성이 뛰어난 코드 및 Python의 광범위한 데이터베이스 연결 범위를 확보 할 수 있기를 바랍니다. PEP-249

MySQLdb를 사용하여 MySQL 데이터베이스에 액세스하기

제일 먼저해야할 일은 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()

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

    또는 특수 이름 인 :memory: 를 제공하여 다음과 같이 RAM에 임시 데이터베이스를 만들 수 있습니다. 

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

    현재 격리 수준을 가져 오거나 설정하는 데 사용되는 특성입니다. 자동 커밋 모드 또는 DEFERRED , IMMEDIATE 또는 EXCLUSIVE 중 하나에 대한 없음.

  1. cursor

    커서 개체는 SQL 명령 및 쿼리를 실행하는 데 사용됩니다.

  1. commit()

    현재 트랜잭션을 커밋합니다.

  1. rollback()

    commit() 대한 이전 호출 이후 변경된 내용을 롤백합니다.

  1. close()

    데이터베이스 연결을 닫습니다. 자동으로 commit() 호출하지 않습니다. commit() 호출하지 않고 close() 를 호출하면 (자동 커밋 모드가 아님을 가정하면) 모든 변경 사항이 손실됩니다.

  1. total_changes

    데이터베이스가 열린 후 수정, 삭제 또는 삽입 된 전체 행 수를 기록하는 특성입니다.

  2. execute , executemany 및 executescript

    이 함수는 커서 객체와 동일한 방식으로 수행됩니다. 이것은 연결 개체를 통해 이러한 함수를 호출하면 중간 커서 개체가 생성되고 커서 개체의 해당 메서드가 호출되므로 바로 가기입니다

  1. row_factory

    이 속성을 커서와 원본 행을 튜플로 받아들이고 실제 결과 행을 반환하는 호출 가능 객체로 변경할 수 있습니다. 

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

    단일 SQL 문을 실행합니다. SQL 문이 매개 변수화 될 수 있습니다 (예 : SQL 리터럴 대신 자리 표시 자). sqlite3 모듈은 두 종류의 자리 표시 자, 즉 물음표를 지원 ? ( "qmark 스타일") 및 명명 된 자리 표시 자 :name ( "명명 된 스타일"). 

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

SQL 주입 공격에 취약해질 수 있으므로 SQL 명령에 문자열을 삽입하는 데 %s 을 사용하지 마십시오 ( SQL 주입 참조).

  1. executemany(sql, seq_of_parameters)

    시퀀스 sql에서 찾은 모든 매개 변수 시퀀스 또는 매핑에 대해 SQL 명령을 실행합니다. 또한 sqlite3 모듈은 시퀀스 대신 매개 변수를 생성하는 반복기를 사용할 수 있습니다. 

    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)

    반복자 객체를 executemany의 매개 변수로 전달할 수도 있으며, 함수는 반복자가 반환하는 값의 각 튜플을 반복합니다. 이터레이터는 값의 튜플을 반환해야합니다. 

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

  2. executescript(sql_script)

    이것은 한 번에 여러 SQL 문을 실행하기위한 비표준 편의 메소드입니다. COMMIT 문을 먼저 실행 한 다음 매개 변수로 가져온 SQL 스크립트를 실행합니다.

    sql_script 는 str 또는 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
     );
     """)

    다음 함수 집합은 SQL의 SELECT 문과 함께 사용됩니다. SELECT 문을 실행 한 후 데이터를 검색하려면 커서를 반복자로 취급하거나 커서의 fetchone() 메소드를 호출하여 일치하는 단일 행을 검색하거나 fetchall() 을 호출하여 일치하는 행의 목록을 가져올 수 있습니다.

    이터레이터 형식의 예 : 

    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)

  3. fetchone()

    단일 결과 시퀀스를 반환하는 쿼리 결과 집합의 다음 행을 가져 오거나 사용할 수있는 데이터가 더 이상 없으면 없음을 가져옵니다. 

    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)

  4. fetchmany(size=cursor.arraysize)

    질의 결과의 다음 행 집합을 가져오고 (크기로 지정) 목록을 반환합니다. size가 생략되면 fetchmany는 단일 행을 리턴합니다. 행이 더 이상 사용 가능하지 않으면 빈 목록이 리턴됩니다. 

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

  5. fetchall()

    질의 결과의 모든 (나머지) 행을 가져와 목록을 반환합니다. 

    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

psycopg2를 사용한 PostgreSQL 데이터베이스 액세스

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 에 대해 알아야 할 모든 것의 절반을 알게되었습니다! :)

오라클 데이터베이스

사전 요구 사항 :

설정:

  • 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 ASSOCIATE', '인간 자원', 6)

쿼리 실행 : INSERT 

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

Oracle Database에서 삽입 / 갱신 / 삭제 작업을 수행 할 때 변경 사항은 commit 이 발행 될 때까지 세션 내에서만 사용 가능합니다. 업데이트 된 데이터가 데이터베이스에 커밋되면 다른 사용자 및 세션에서 사용할 수 있습니다.

쿼리 실행 : 바인딩 변수를 사용하여 INSERT

참고

바인드 변수를 사용하면 명령문을 다시 구문 분석하는 오버 헤드없이 새로운 값으로 명령문을 다시 실행할 수 있습니다. 바인딩 변수는 코드 재사용 가능성을 향상시키고 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 () 메서드는 연결을 닫습니다. 명시 적으로 닫히지 않은 모든 연결은 스크립트가 끝나면 자동으로 해제됩니다.

연결

연결 만들기

PEP 249에 따르면 데이터베이스에 대한 connect() 은 Connection 객체를 반환하는 connect() 생성자를 사용하여 설정해야합니다. 이 생성자의 인수는 데이터베이스에 따라 다릅니다. 관련 인수에 대해서는 데이터베이스 특정 주제를 참조하십시오. 

import MyDBAPI

con = MyDBAPI.connect(*database_dependent_args)

이 연결 개체에는 네 가지 방법이 있습니다.

1 : 닫기 

con.close()

즉시 연결을 닫습니다. Connection.__del___ 메소드가 호출되면 연결은 자동으로 닫힙니다. 보류중인 트랜잭션은 암시 적으로 롤백됩니다.

2 : 커밋 

con.commit()

데이터베이스에 보류중인 트랜잭션을 커밋합니다.

3 : 롤백 

con.rollback()

보류중인 트랜잭션의 시작으로 롤백합니다. 즉, 데이터베이스에 대한 커밋되지 않은 트랜잭션을 취소합니다.

4 : 커서 

cur = con.cursor()

Cursor 객체를 리턴합니다. 이것은 데이터베이스에서 트랜잭션을 수행하는 데 사용됩니다.

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)


Modified text is an extract of the original Stack Overflow Documentation
아래 라이선스 CC BY-SA 3.0
와 제휴하지 않음 Stack Overflow