제퍼넷 로고

Pandas에서 SQL 파일 읽기 및 쓰기

시간

몇 년 전 데이터 분석을 배우기 시작했을 때 가장 먼저 배운 것은 SQL과 Pandas였습니다. 데이터 분석가로서 SQL 및 Pandas 작업에 대한 강력한 기반을 갖추는 것이 중요합니다. 둘 다 데이터 분석가가 데이터베이스에 저장된 데이터를 효율적으로 분석하고 조작하는 데 도움이 되는 강력한 도구입니다.

SQL 및 Pandas 개요

SQL(Structured Query Language)은 관계형 데이터베이스를 관리하고 조작하는 데 사용되는 프로그래밍 언어입니다. 반면 Pandas는 데이터 조작 및 분석에 사용되는 Python 라이브러리입니다.

데이터 분석에는 많은 양의 데이터 작업이 포함되며 이러한 데이터를 저장하기 위해 데이터베이스가 자주 사용됩니다. SQL 및 Pandas는 데이터베이스 작업을 위한 강력한 도구를 제공하여 데이터 분석가가 데이터를 효율적으로 추출, 조작 및 분석할 수 있도록 합니다. 데이터 분석가는 이러한 도구를 활용하여 다른 방법으로는 얻기 어려운 데이터에서 귀중한 통찰력을 얻을 수 있습니다.

이 기사에서는 SQL 및 Pandas를 사용하여 데이터베이스를 읽고 쓰는 방법을 살펴봅니다.

DB에 연결

라이브러리 설치

Pandas로 SQL 데이터베이스에 연결하려면 먼저 필요한 라이브러리를 설치해야 합니다. 필요한 두 가지 주요 라이브러리는 Pandas와 SQLAlchemy입니다. Pandas는 서론에서 언급한 대로 대규모 데이터 구조를 저장할 수 있는 인기 있는 데이터 조작 라이브러리입니다. 반대로 SQLAlchemy는 SQL 데이터베이스에 연결하고 상호 작용하기 위한 API를 제공합니다.

명령 프롬프트에서 다음 명령을 실행하여 Python 패키지 관리자 pip를 사용하여 두 라이브러리를 모두 설치할 수 있습니다.

$ pip install pandas
$ pip install sqlalchemy

연결하기

라이브러리가 설치되었으므로 이제 Pandas를 사용하여 SQL 데이터베이스에 연결할 수 있습니다.

시작하려면 다음을 사용하여 SQLAlchemy 엔진 개체를 만듭니다. create_engine(). 그만큼 create_engine() 함수는 Python 코드를 데이터베이스에 연결합니다. 데이터베이스 유형 및 연결 세부 정보를 지정하는 연결 문자열을 인수로 사용합니다. 이 예에서는 SQLite 데이터베이스 유형과 데이터베이스 파일의 경로를 사용합니다.

아래 예제를 사용하여 SQLite 데이터베이스용 엔진 개체를 만듭니다.

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db')

SQLite 데이터베이스 파일(이 경우에는 student.db)이 Python 스크립트와 동일한 디렉터리에 있으면 아래와 같이 파일 이름을 직접 사용할 수 있습니다.

engine = create_engine('sqlite:///student.db')

Pandas로 SQL 파일 읽기

이제 연결이 설정되었으므로 데이터를 읽어 봅시다. 이 섹션에서는 read_sql, read_sql_tableread_sql_query 기능 및 데이터베이스 작업에 사용하는 방법.

Panda를 사용하여 SQL 쿼리 실행 읽기_SQL() 함수

최대 XNUMXW 출력을 제공하는 read_sql() SQL 쿼리를 실행하고 결과를 Pandas 데이터 프레임으로 검색할 수 있는 Pandas 라이브러리 함수입니다. 그만큼 read_sql() 함수는 SQL과 Python을 연결하여 두 언어의 장점을 모두 활용할 수 있도록 합니다. 함수가 래핑합니다. read_sql_table()read_sql_query(). 그만큼 read_sql() 함수는 제공된 입력을 기반으로 내부적으로 라우팅됩니다. 즉, 입력이 SQL 쿼리를 실행하는 경우 다음으로 라우팅됩니다. read_sql_query(), 데이터베이스 테이블인 경우 다음으로 라우팅됩니다. read_sql_table().

최대 XNUMXW 출력을 제공하는 read_sql() 구문은 다음과 같습니다.

pandas.read_sql(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, columns=None, chunksize=None)

SQL 및 con 매개변수가 필요합니다. 나머지는 선택 사항입니다. 그러나 이러한 선택적 매개변수를 사용하여 결과를 조작할 수 있습니다. 각 매개변수를 자세히 살펴보겠습니다.

  • sql: SQL 쿼리 또는 데이터베이스 테이블 이름
  • con: 연결 개체 또는 연결 URL
  • index_col: 이 매개변수를 사용하면 SQL 쿼리 결과에서 하나 이상의 열을 데이터 프레임 인덱스로 사용할 수 있습니다. 단일 열 또는 열 목록을 사용할 수 있습니다.
  • coerce_float: 이 매개변수는 숫자가 아닌 값을 부동 소수점으로 변환할지 또는 문자열로 남겨둘지를 지정합니다. 기본적으로 true로 설정됩니다. 가능하면 숫자가 아닌 값을 float 유형으로 변환합니다.
  • params: params는 동적 값을 SQL 쿼리에 전달하기 위한 안전한 방법을 제공합니다. params 매개변수를 사용하여 사전, 튜플 또는 목록을 전달할 수 있습니다. 데이터베이스에 따라 params 구문이 다릅니다.
  • parse_dates: 이렇게 하면 결과 데이터 프레임에서 날짜로 해석할 열을 지정할 수 있습니다. 단일 열, 열 목록 또는 열 이름으로 키가 있고 열 형식으로 값이 있는 사전을 허용합니다.
  • columns: 이렇게 하면 목록에서 선택한 열만 가져올 수 있습니다.
  • chunksize: 대용량 데이터셋으로 작업할 때는 청크사이즈가 중요합니다. 더 작은 청크로 쿼리 결과를 검색하여 성능을 향상시킵니다.

사용 방법의 예는 다음과 같습니다. read_sql():

암호:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql("SELECT * FROM Student", engine, index_col='Roll Number', parse_dates='dateOfBirth')
print(df)
print("The Data type of dateOfBirth: ", df.dateOfBirth.dtype) engine.dispose()

출력:

 firstName lastName email dateOfBirth
rollNumber
1 Mark Simson [email protected] 2000-02-23
2 Peter Griffen [email protected] 2001-04-15
3 Meg Aniston [email protected] 2001-09-20
Date type of dateOfBirth: datetime64[ns]

데이터베이스에 연결한 후 데이터베이스의 모든 레코드를 반환하는 쿼리를 실행합니다. Student 테이블을 DataFrame에 저장합니다. df. "롤 번호" 열은 다음을 사용하여 인덱스로 변환됩니다. index_col 매개변수이며 "dateOfBirth" 데이터 유형은 "datetime64[ns]"입니다. parse_dates. 우리는 사용할 수 있습니다 read_sql() 데이터 검색뿐만 아니라 삽입, 삭제 및 업데이트와 같은 다른 작업을 수행합니다. read_sql() 는 일반 함수입니다.

DB에서 특정 테이블 또는 뷰 불러오기

Pandas로 특정 테이블 또는 보기 로드 read_sql_table() 데이터베이스에서 Pandas 데이터 프레임으로 데이터를 읽는 또 다른 기술입니다.

읽기_sql_테이블?

Pandas 라이브러리는 다음을 제공합니다. read_sql_table 쿼리를 실행하지 않고 전체 SQL 테이블을 읽고 결과를 Pandas 데이터 프레임으로 반환하도록 특별히 설계된 함수입니다.

구문 read_sql_table() 다음과 같습니다.

pandas.read_sql_table(table_name, con, schema=None, index_col=None, coerce_float=True, parse_dates=None, columns=None, chunksize=None)

를 제외하고 table_name 및 스키마, 매개 변수는 다음과 같은 방식으로 설명됩니다. read_sql().

  • table_name: 매개변수 table_name 데이터베이스에 있는 SQL 테이블의 이름입니다.
  • schema: 이 선택적 매개변수는 테이블 이름을 포함하는 스키마의 이름입니다.

데이터베이스에 대한 연결을 만든 후 다음을 사용합니다. read_sql_table 로드하는 기능 Student 테이블을 Pandas DataFrame으로 변환합니다.

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_table('Student', engine)
print(df.head()) engine.dispose()

출력:

 rollNumber firstName lastName email dateOfBirth
0 1 Mark Simson [email protected] 2000-02-23
1 2 Peter Griffen [email protected] 2001-04-15
2 3 Meg Aniston [email protected] 2001-09-20

메모리 집약적일 수 있는 큰 테이블이라고 가정합니다. 어떻게 사용할 수 있는지 알아보겠습니다. chunksize 이 문제를 해결하기 위한 매개변수입니다.

모범 사례, 업계에서 인정하는 표준 및 포함된 치트 시트가 포함된 Git 학습에 대한 실습 가이드를 확인하십시오. 인터넷 검색 Git 명령을 중지하고 실제로 배움 이것!

암호:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df_iterator = pd.read_sql_table('Student', engine, chunksize = 1) for df in df_iterator: print(df.head()) engine.dispose()

출력:

 rollNumber firstName lastName email dateOfBirth
0 1 Mark Simson [email protected] 2000-02-23
0 2 Peter Griffen [email protected] 2001-04-15
0 3 Meg Aniston [email protected] 2001-09-20

참고하세요 chunksize 내 테이블에 1개의 레코드만 있기 때문에 여기서는 3을 사용하고 있습니다.

Pandas의 SQL 구문으로 직접 DB 쿼리하기

데이터베이스에서 인사이트를 추출하는 것은 데이터 분석가와 과학자에게 중요한 부분입니다. 이를 위해 우리는 read_sql_query() 기능.

read_sql_query()란 무엇입니까?

팬더 사용 read_sql_query() 함수를 사용하여 SQL 쿼리를 실행하고 결과를 DataFrame으로 직접 가져올 수 있습니다. 그만큼 read_sql_query() 함수는 특별히 생성됩니다. SELECT 진술. 등의 다른 작업에는 사용할 수 없습니다. DELETE or UPDATE.

구문 :

pandas.read_sql_query(sql, con, index_col=None, coerce_float=True, params=None, parse_dates=None, chunksize=None, dtype=None, dtype_backend=_NoDefault.no_default)

모든 매개변수 설명은 read_sql() 기능. 다음은 예입니다. read_sql_query():

암호:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_query('Select firstName, lastName From Student Where rollNumber = 1', engine)
print(df) engine.dispose()

출력:

 firstName lastName
0 Mark Simson

Pandas로 SQL 파일 작성

데이터를 분석하는 동안 몇 가지 항목을 수정해야 하거나 데이터가 포함된 새 테이블 또는 보기가 필요하다는 것을 발견했다고 가정합니다. 새 레코드를 업데이트하거나 삽입하려면 한 가지 방법을 사용하는 것입니다. read_sql() 쿼리를 작성합니다. 그러나 이 방법은 시간이 오래 걸릴 수 있습니다. Pandas는 다음과 같은 훌륭한 방법을 제공합니다. to_sql() 이와 같은 상황을 위해.

이 섹션에서는 먼저 데이터베이스에 새 테이블을 만든 다음 기존 테이블을 편집합니다.

SQL 데이터베이스에서 새 테이블 생성

새 테이블을 만들기 전에 먼저 논의하겠습니다. to_sql() 상세히.

to_sql()?

최대 XNUMXW 출력을 제공하는 to_sql() Pandas 라이브러리의 기능을 사용하면 데이터베이스를 작성하거나 업데이트할 수 있습니다. 그만큼 to_sql() 함수는 DataFrame 데이터를 SQL 데이터베이스에 저장할 수 있습니다.

구문 to_sql():

DataFrame.to_sql(name, con, schema=None, if_exists='fail', index=True, index_label=None, chunksize=None, dtype=None, method=None)

namecon 실행하려면 매개변수가 필수입니다. to_sql(); 그러나 다른 매개변수는 추가적인 유연성과 사용자 지정 옵션을 제공합니다. 각 매개변수에 대해 자세히 살펴보겠습니다.

  • name: 생성하거나 변경할 SQL 테이블의 이름.
  • con: 데이터베이스의 연결 개체입니다.
  • schema: 테이블의 스키마입니다(선택 사항).
  • if_exists: 이 파라미터의 기본값은 “fail”입니다. 이 매개변수를 사용하면 테이블이 이미 존재하는 경우 취할 조치를 결정할 수 있습니다. 옵션에는 "fail", "replace" 및 "append"가 있습니다.
  • index: index 매개 변수는 부울 값을 허용합니다. 기본적으로 True로 설정되며 DataFrame의 인덱스가 SQL 테이블에 기록됨을 의미합니다.
  • index_label: 이 선택적 매개변수를 사용하면 인덱스 열에 대한 열 레이블을 지정할 수 있습니다. 기본적으로 인덱스는 테이블에 기록되지만 이 매개변수를 사용하여 특정 이름을 지정할 수 있습니다.
  • chunksize: SQL 데이터베이스에서 한 번에 기록되는 행 수입니다.
  • dtype: 이 매개변수는 열 이름으로 키가 있고 데이터 유형으로 값이 있는 사전을 허용합니다.
  • method: method 매개변수를 사용하면 데이터를 SQL에 삽입하는 데 사용되는 방법을 지정할 수 있습니다. 기본적으로 없음으로 설정되어 있습니다. 즉, pandas는 데이터베이스를 기반으로 가장 효율적인 방법을 찾습니다. 방법 매개변수에는 두 가지 기본 옵션이 있습니다.
    • multi: 단일 SQL 쿼리에 여러 행을 삽입할 수 있습니다. 그러나 모든 데이터베이스가 다중 행 삽입을 지원하는 것은 아닙니다.
    • 호출 가능 함수: 여기에서 삽입을 위한 사용자 지정 함수를 작성하고 메서드 매개 변수를 사용하여 호출할 수 있습니다.

다음은 사용하는 예입니다. to_sql():

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') data = {'Name': ['Paul', 'Tom', 'Jerry'], 'Age': [9, 8, 7]}
df = pd.DataFrame(data) df.to_sql('Customer', con=engine, if_exists='fail') engine.dispose()

Customer라는 새 테이블이 "Name" 및 "Age"라는 두 개의 필드가 있는 데이터베이스에 생성됩니다.

데이터베이스 스냅샷:

to_sql()의 출력

Pandas Dataframes로 기존 테이블 업데이트

데이터베이스에서 데이터를 업데이트하는 것은 특히 대용량 데이터를 처리할 때 복잡한 작업입니다. 그러나 to_sql() Pandas의 기능을 사용하면 이 작업을 훨씬 쉽게 수행할 수 있습니다. 데이터베이스의 기존 테이블을 업데이트하려면 to_sql() 기능은 다음과 함께 사용할 수 있습니다. if_exists 매개변수가 "대체"로 설정되었습니다. 기존 테이블을 새 데이터로 덮어씁니다.

다음은 예입니다 to_sql() 이전에 생성된 Customer 테이블. 에서 Customer 테이블에서 Paul이라는 고객의 나이를 9세에서 10세로 업데이트하려고 합니다. 그렇게 하려면 먼저 DataFrame에서 해당 행을 수정한 다음 to_sql() 데이터베이스를 업데이트하는 기능.

암호:

import pandas as pd
from sqlalchemy import create_engine engine = create_engine('sqlite:///C/SQLite/student.db') df = pd.read_sql_table('Customer', engine) df.loc[df['Name'] == 'Paul', 'Age'] = 10 df.to_sql('Customer', con=engine, if_exists='replace') engine.dispose()

데이터베이스에서 Paul의 나이가 업데이트됩니다.

to_sql()의 출력

결론

결론적으로 Pandas와 SQL은 모두 SQL 데이터베이스에서 데이터를 읽고 쓰는 것과 같은 데이터 분석 작업을 위한 강력한 도구입니다. Pandas는 SQL 데이터베이스에 연결하고, 데이터베이스에서 Pandas 데이터 프레임으로 데이터를 읽고, 데이터 프레임 데이터를 다시 데이터베이스에 쓰는 쉬운 방법을 제공합니다.

Pandas 라이브러리를 사용하면 데이터 프레임에서 데이터를 쉽게 조작할 수 있으며 SQL은 데이터베이스에서 데이터를 쿼리하기 위한 강력한 언어를 제공합니다. 데이터를 읽고 쓰는 데 Pandas와 SQL을 모두 사용하면 특히 데이터가 매우 큰 경우 데이터 분석 작업에서 시간과 노력을 절약할 수 있습니다. 전반적으로 SQL과 Pandas를 함께 활용하면 데이터 분석가와 과학자가 워크플로를 간소화할 수 있습니다.

spot_img

변하기 쉬운

최신 인텔리전스

spot_img