<div id="readability-page-1" class="page"><div>DuckDB 에서 Posgresql 에 연결된 Database, DB, Table 에 접근하고바로 쿼리를 날려볼 수 있다.<p data-app="{&quot;type&quot;:&quot;text&quot;,&quot;data&quot;:[{&quot;type&quot;:&quot;anchor&quot;,&quot;url&quot;:&quot;https://duckdb.org/docs/guides/database_integration/postgres&quot;,&quot;data&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;text&quot;:&quot;https://duckdb.org/docs/guides/database_integration/postgres&quot;}],&quot;target&quot;:&quot;_blank&quot;}]}"><a target="_blank" href="https://duckdb.org/docs/guides/database_integration/postgres">https://duckdb.org/docs/guides/database_integration/postgres</a>먼저 macos(intel version) 에서 brew 를 통해서 Postgres 를 설치하고서비스로 등록하면 끝!&gt;&gt; brew install postgresql&gt;&gt; brew services start postgresql이러면 default 설정으로 잡혀있는데 다시 터미널에서&gt;&gt; psql postgres&gt;&gt;# CREATE DATABASE mydatabase;<p data-app="{&quot;type&quot;:&quot;text&quot;,&quot;data&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;text&quot;:&quot;\u003e\u003e# CREATE USER \u0027YOUR_NAME\u0027 WITH ENCRYPTED PASSWORD \u0027YOUR_PWD\u0027&quot;}]}">&gt;&gt;# CREATE USER 'YOUR_NAME' WITH ENCRYPTED PASSWORD 'YOUR_PWD'<p data-app="{&quot;type&quot;:&quot;text&quot;,&quot;data&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;text&quot;:&quot;\u003e\u003e# GRANT ALL PRIVILEGES ON DATABASE mydatabase TO \u0027YOUR_NAME\u0027;&quot;}]}">&gt;&gt;# GRANT ALL PRIVILEGES ON DATABASE mydatabase TO 'YOUR_NAME';이러면 mydatabase 데이터베이스가 생성된다.이제 쥬피터에서 해당 Postgres 에 접속해서 테이블에 쿼리를 보내보자.DuckDB 와 연결을 위해서 필요한 라이브러리를 설치하고,<p data-app="{&quot;type&quot;:&quot;text&quot;,&quot;data&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;text&quot;:&quot;\u003e\u003e pip install jupysql pandas matplotlib duckdb-engine psycopg2-binary&quot;}]}">&gt;&gt; pip install jupysql pandas matplotlib duckdb-engine psycopg2-binary간단하게 Postgre 에 테이블을 만들어서 간단한 데이터를 넣어본다.import psycopg2from contextlib import contextmanager@contextmanager<p data-app="{&quot;type&quot;:&quot;text&quot;,&quot;data&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;text&quot;:&quot;def get_db_connection(dbname, user, password, host\u003d\&quot;localhost\&quot;, port\u003d\&quot;5432\&quot;):&quot;}]}">def get_db_connection(dbname, user, password, host="localhost", port="5432"):&nbsp;&nbsp;&nbsp;&nbsp;conn = None&nbsp;&nbsp;&nbsp;&nbsp;try:<p data-app="{&quot;type&quot;:&quot;text&quot;,&quot;data&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;text&quot;:&quot; conn \u003d psycopg2.connect(dbname\u003ddbname, user\u003duser, password\u003dpassword, host\u003dhost, port\u003dport)&quot;}]}">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;conn = psycopg2.connect(dbname=dbname, user=user, password=password, host=host, port=port)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;yield conn&nbsp;&nbsp;&nbsp;&nbsp;finally:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if conn:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;conn.close()@contextmanager<p data-app="{&quot;type&quot;:&quot;text&quot;,&quot;data&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;text&quot;:&quot;def get_db_cursor(dbname, user, password, host\u003d\&quot;localhost\&quot;, port\u003d\&quot;5432\&quot;):&quot;}]}">def get_db_cursor(dbname, user, password, host="localhost", port="5432"):&nbsp;&nbsp;&nbsp;&nbsp;conn = None&nbsp;&nbsp;&nbsp;&nbsp;try:<p data-app="{&quot;type&quot;:&quot;text&quot;,&quot;data&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;text&quot;:&quot; conn \u003d psycopg2.connect(dbname\u003ddbname, user\u003duser, password\u003dpassword, host\u003dhost, port\u003dport)&quot;}]}">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;conn = psycopg2.connect(dbname=dbname, user=user, password=password, host=host, port=port)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cursor = conn.cursor()&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;yield cursor&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;conn.commit()&nbsp;&nbsp;&nbsp;&nbsp;except Exception as e:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if conn:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;conn.rollback()&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;raise e&nbsp;&nbsp;&nbsp;&nbsp;finally:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if cursor:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;cursor.close()&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;if conn:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;conn.close()# 데이터베이스 설정dbname = "mydatabase"user = "YOUR_NAME"password = "YOUR_PWD"host = "localhost"port = "5432"# 데이터베이스 작업 수행with get_db_cursor(dbname, user, password, host, port) as cur:&nbsp;&nbsp;&nbsp;&nbsp;# 테이블 생성&nbsp;&nbsp;&nbsp;&nbsp;cur.execute("""&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CREATE TABLE IF NOT EXISTS test_table (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;id SERIAL PRIMARY KEY,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;name VARCHAR(100),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;age INT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;""")&nbsp;&nbsp;&nbsp;&nbsp;# 데이터 삽입<p data-app="{&quot;type&quot;:&quot;text&quot;,&quot;data&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;text&quot;:&quot; cur.execute(\&quot;INSERT INTO test_table (name, age) VALUES (%s, %s)\&quot;, (\&quot;Alice\&quot;, 30))&quot;}]}">&nbsp;&nbsp;&nbsp;&nbsp;cur.execute("INSERT INTO test_table (name, age) VALUES (%s, %s)", ("Alice", 30))<p data-app="{&quot;type&quot;:&quot;text&quot;,&quot;data&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;text&quot;:&quot; cur.execute(\&quot;INSERT INTO test_table (name, age) VALUES (%s, %s)\&quot;, (\&quot;Bob\&quot;, 25))&quot;}]}">&nbsp;&nbsp;&nbsp;&nbsp;cur.execute("INSERT INTO test_table (name, age) VALUES (%s, %s)", ("Bob", 25))# 데이터 조회with get_db_cursor(dbname, user, password, host, port) as cur:&nbsp;&nbsp;&nbsp;&nbsp;cur.execute("SELECT * FROM test_table")&nbsp;&nbsp;&nbsp;&nbsp;rows = cur.fetchall()&nbsp;&nbsp;&nbsp;&nbsp;for row in rows:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;print(row)이런 결과가 프린트된다.&gt;&gt; (1, 'Alice', 30) (2, 'Bob', 25)이제 DuckDB 로 조회!<div data-app="{&quot;type&quot;:&quot;img&quot;,&quot;align&quot;:&quot;content-full&quot;,&quot;mobileAlign&quot;:&quot;full&quot;,&quot;url&quot;:&quot;http://t1.daumcdn.net/brunch/service/user/1aft/image/4AbcH_PFBZDuj2LLdCYe1Eraqps.png&quot;,&quot;caption&quot;:&quot;&quot;,&quot;width&quot;:&quot;1580&quot;,&quot;height&quot;:&quot;2202&quot;,&quot;originalName&quot;:&quot;&quot;}"><img src="https://img1.daumcdn.net/thumb/R1280x0/?fname=http://t1.daumcdn.net/brunch/service/user/1aft/image/4AbcH_PFBZDuj2LLdCYe1Eraqps.png"></div><p data-app="{&quot;type&quot;:&quot;text&quot;,&quot;data&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;text&quot;:&quot;새로운 connnection (예를 들어, con2 로 duckdb 엔진에 하나 더 붙여서) 을 통해서 sqlite 에 있는 테이블도 읽어서 사용 가능하다.&quot;}]}">새로운 connnection (예를 들어, con2 로 duckdb 엔진에 하나 더 붙여서) 을 통해서 sqlite 에 있는 테이블도 읽어서 사용 가능하다.<p data-app="{&quot;type&quot;:&quot;text&quot;,&quot;data&quot;:[{&quot;type&quot;:&quot;anchor&quot;,&quot;url&quot;:&quot;https://duckdb.org/docs/guides/database_integration/sqlite&quot;,&quot;data&quot;:[{&quot;type&quot;:&quot;text&quot;,&quot;text&quot;:&quot;https://duckdb.org/docs/guides/database_integration/sqlite&quot;}],&quot;target&quot;:&quot;_blank&quot;}]}"><a target="_blank" href="https://duckdb.org/docs/guides/database_integration/sqlite">https://duckdb.org/docs/guides/database_integration/sqlite</a></div></div>

유윤식

DuckDB 에서 Posgresql 에 연결된 Database, DB, Table 에 접근하고 바로 쿼리를 날려볼 수 있다. https://duckdb.org/docs/guides/database_integration/postgres  먼저 macos(intel version) 에서 brew 를 통해서 Postgres 를 설치하고 서비스로 등록하면 끝!

Python: DuckDB(5) - #Postgresql #Plugin

댓글