JayDeBeApi
The JayDeBeApi module allows you to connect from Python code to databases using Java JDBC. It provides a Python DB-API v2.0 to that database.
https://pypi.org/project/JayDeBeApi/
install
jdbc를 사용하다보니 java가 설치돼 있어야 한다.
Python Version
$ python --version
Python 3.6.6
pip를 이용한 설치
$ pip install JayDeBeApi
Collecting JayDeBeApi
Downloading https://files.pythonhosted.org/packages/2a/63/5fbffcbf0463fe26f55ee8ff08bcbb812cab4df2decddfac645cbac966ed/JayDeBeApi-1.1.1-py3-none-any.whl
Collecting JPype1 (from JayDeBeApi)
Downloading https://files.pythonhosted.org/packages/c4/4b/60a3e63d51714d4d7ef1b1efdf84315d118a0a80a5b085bb52a7e2428cdc/JPype1-0.6.3.tar.gz (168kB)
100% |████████████████████████████████| 174kB 4.5MB/s
Installing collected packages: JPype1, JayDeBeApi
Running setup.py install for JPype1 ... done
Successfully installed JPype1-0.6.3 JayDeBeApi-1.1.1
sample
tibero에 jdbc로 접근해 단순한 ddl,dml 을 수행해보고 resultset을 받아옴. executemany 메소드를 이용해서 batch insert도 할 수 있는 것 같다.
#!/app/python/bin/python
import jaydebeapi
conn = jaydebeapi.connect(
"com.tmax.tibero.jdbc.TbDriver",
"jdbc:tibero:thin:@localhost:8629:tibero",
["tibero","tmax"],
"tibero6-jdbc.jar",
)
cur = conn.cursor()
sql = "SELECT LEVEL AS NUM,SYSDATE-LEVEL AS DT FROM DUAL CONNECT BY LEVEL < 10"
cur.execute(sql)
print(cur.fetchall())
sql = "CREATE TABLE TEST_TAB AS SELECT LEVEL AS NUM,SYSDATE-LEVEL AS DT FROM DUAL CONNECT BY LEVEL < 10"
cur.execute(sql)
sql = "SELECT * FROM TEST_TAB"
cur.execute(sql)
print(cur.fetchall())
cur.close()
import datetime
cur = conn.cursor()
data = [
(str(10),datetime.datetime(2018,11,24).strftime("%Y/%m/%d")),
(str(11),datetime.datetime(2018,11,23).strftime("%Y/%m/%d")),
(str(12),datetime.datetime(2018,11,22).strftime("%Y/%m/%d")),
(str(13),datetime.datetime(2018,11,21).strftime("%Y/%m/%d")),
(str(14),datetime.datetime(2018,11,20).strftime("%Y/%m/%d")),
(str(15),datetime.datetime(2018,11,19).strftime("%Y/%m/%d")),
(str(16),datetime.datetime(2018,11,18).strftime("%Y/%m/%d")),
(str(17),datetime.datetime(2018,11,17).strftime("%Y/%m/%d")),
(str(18),datetime.datetime(2018,11,16).strftime("%Y/%m/%d")),
(str(19),datetime.datetime(2018,11,15).strftime("%Y/%m/%d")),
]
stmt = """INSERT INTO TEST_TAB VALUES (?, ?)"""
cur.executemany(stmt, data)
sql = "SELECT * FROM TEST_TAB"
cur.execute(sql)
print(cur.fetchall())
cur.close()
conn.close()
알아보기 어렵지만 됨
./sample.py
[(1.0, '2018-12-03 18:04:09'), (2.0, '2018-12-02 18:04:09'), (3.0, '2018-12-01 18:04:09'), (4.0, '2018-11-30 18:04:09'), (5.0, '2018-11-29 18:04:09'), (6.0, '2018-11-28 18:04:09'), (7.0, '2018-11-27 18:04:09'), (8.0, '2018-11-26 18:04:09'), (9.0, '2018-11-25 18:04:09')]
[(1.0, '2018-12-03 18:04:09'), (2.0, '2018-12-02 18:04:09'), (3.0, '2018-12-01 18:04:09'), (4.0, '2018-11-30 18:04:09'), (5.0, '2018-11-29 18:04:09'), (6.0, '2018-11-28 18:04:09'), (7.0, '2018-11-27 18:04:09'), (8.0, '2018-11-26 18:04:09'), (9.0, '2018-11-25 18:04:09')]
[(1.0, '2018-12-03 18:04:09'), (2.0, '2018-12-02 18:04:09'), (3.0, '2018-12-01 18:04:09'), (4.0, '2018-11-30 18:04:09'), (5.0, '2018-11-29 18:04:09'), (6.0, '2018-11-28 18:04:09'), (7.0, '2018-11-27 18:04:09'), (8.0, '2018-11-26 18:04:09'), (9.0, '2018-11-25 18:04:09'), (10.0, '2018-11-24 00:00:00'), (11.0, '2018-11-23 00:00:00'), (12.0, '2018-11-22 00:00:00'), (13.0, '2018-11-21 00:00:00'), (14.0, '2018-11-20 00:00:00'), (15.0, '2018-11-19 00:00:00'), (16.0, '2018-11-18 00:00:00'), (17.0, '2018-11-17 00:00:00'), (18.0, '2018-11-16 00:00:00'), (19.0, '2018-11-15 00:00:00')]
SQL> ls
NAME SUBNAME TYPE
---------------------------------- ------------------------ --------------------
TEST_TAB TABLE
SQL> select * from test_tab;
NUM DT
---------- --------------------------------
1 2018/12/03
2 2018/12/02
3 2018/12/01
4 2018/11/30
5 2018/11/29
6 2018/11/28
7 2018/11/27
8 2018/11/26
9 2018/11/25
10 2018/11/24
11 2018/11/23
12 2018/11/22
13 2018/11/21
14 2018/11/20
15 2018/11/19
16 2018/11/18
17 2018/11/17
18 2018/11/16
19 2018/11/15
19 rows selected.
