pyjdbc使用

数据库连接

import jaydebeapi
url = 'jdbc:oscar://127.0.0.1:2003/osrdb'
user = 'sysdba'
password = 'szoscar55'
jarFile = r'E:\pyjdbc\oscarJDBC8.jar'
driver, driver_args = 'com.oscar.Driver', [user, password]
conn = jaydebeapi.connect(driver,url,driver_args,jarFile)
curs = conn.cursor()

创建表

_sql = """
CREATE TABLE USERS
    (ID INTEGER AUTO_INCREMENT,
    NAME VARCHAR2(1024),
    AGE SMALLINT,
    TEST_BLOB BLOB,
    TEST_CLOB CLOB,
    CREATED_TIME TIMESTAMP WITH TIME ZONE,
    UPDATED_TIME TIMESTAMP WITH TIME ZONE,
    PRIMARY KEY (ID))
"""
curs.execute(_sql)

删除表

_sql = "DROP TABLE USERS"
curs.execute(_sql)

插入数据

_sql = """
INSERT INTO USERS
(NAME,AGE,TEST_BLOB,TEST_CLOB,CREATED_TIME,UPDATED_TIME)
VALUES ('中文名',20,'0x123456789','clob','2022-03-21 18:18:39.993','2022-03-21 18:18:39.993')
"""
curs.execute(_sql)

查询数据

_sql = """
SELECT * FROM USERS ORDER BY USERS.ID DESC LIMIT 1
"""
curs.execute(_sql)
result = curs.fetchall()
print(result)

更新数据

_sql = """
UPDATE USERS SET NAME='TestUpdate',AGE=19 WHERE USERS.NAME = 'TestInsert1' AND ID = 1
"""
r = curs.execute(_sql)

删除数据

_sql = """
delete from USERS WHERE USERS.NAME = 'TestUpdate' AND ID = 1
"""
r = curs.execute(_sql)

关闭连接

curs.close()
conn.close()