Why Python has no equivalent of JDBC of Java?

[Marco Sulla]

I programmed in Python 2 and 3 for many years, and I find it a fantastic
language.

Now I'm programming in Java by m ore than 2 years, and even if I found its
code much more boilerplate, I admit that JDBC is fantastic.

One example over all: Oracle. If you want to access an Oracle DB from
Python, you have to:

1. download the Oracle instantclient and install/unzip it
2. on Linux, you have also to install/unzip Development and Runtime package
3. on windows, you have to add the instantclient to PATH
4. on Linux, you have to create a script to source that sets PATH,
ORACLE_HOME and LD_LIBRARY_PATH

Finally, you can use cx_Oracle.

Java? You have only to download ojdbcN.jar and add it to Maven/Gradle.

Why Python has no equivalent to JDBC?

[Chris Angelico]

I've no idea what the hassles are with Oracle, as it's a database
engine that I don't use. But with PostgreSQL, which I *do* use, I can
assure you that it's much easier:

$ pip install psycopg2
>>> import psycopg2

Job done.

If Oracle is harder to use, it may be a specific issue with installing
the Oracle client. Have you tried using pip to install cx_oracle?

ChrisA

[Andrew Z]

Marco,
You clearly know more about python/java universe than i do.
But im infinitely thankful to cx team for putting out the package.
Feature and performance wise , even with non supported oracle timesten, it
was fantastic.
Id always go after "native" vs jdbc. But i understand that most of apps
have a very general workflow of " select some, insert some/delete" thus
most of the native features are not required..

Sorry, its Sunday, float off the subject.

On Sun, May 19, 2019, 17:33 Marco Sulla via Python-list <

pytho...@python.org> wrote:

> I programmed in Python 2 and 3 for many years, and I find it a fantastic
> language.
>
> Now I'm programming in Java by m ore than 2 years, and even if I found its
> code much more boilerplate, I admit that JDBC is fantastic.
>
> One example over all: Oracle. If you want to access an Oracle DB from
> Python, you have to:
>
> 1. download the Oracle instantclient and install/unzip it
> 2. on Linux, you have also to install/unzip Development and Runtime package
> 3. on windows, you have to add the instantclient to PATH
> 4. on Linux, you have to create a script to source that sets PATH,
> ORACLE_HOME and LD_LIBRARY_PATH
>
> Finally, you can use cx_Oracle.
>
> Java? You have only to download ojdbcN.jar and add it to Maven/Gradle.
>
> Why Python has no equivalent to JDBC?

> --
> https://mail.python.org/mailman/listinfo/python-list
>

[Andrew Z]

The pg python lib requires
https://www.postgresql.org/docs/current/libpq.html
pip pulls it as a part of the lib install, whereas in oracle case you
install the driver yourself first.(suize matters)

//for some reason i thought the driver libs come as part of cx . But it was
a year ago and my memory may play tricks on me.


On Sun, May 19, 2019, 17:44 Chris Angelico <ros...@gmail.com> wrote:

> On Mon, May 20, 2019 at 7:34 AM Marco Sulla via Python-list

> <pytho...@python.org> wrote:
> >
> > I programmed in Python 2 and 3 for many years, and I find it a fantastic
> > language.
> >
> > Now I'm programming in Java by m ore than 2 years, and even if I found
> its
> > code much more boilerplate, I admit that JDBC is fantastic.
> >
> > One example over all: Oracle. If you want to access an Oracle DB from
> > Python, you have to:
> >
> > 1. download the Oracle instantclient and install/unzip it
> > 2. on Linux, you have also to install/unzip Development and Runtime
> package
> > 3. on windows, you have to add the instantclient to PATH
> > 4. on Linux, you have to create a script to source that sets PATH,
> > ORACLE_HOME and LD_LIBRARY_PATH
> >
> > Finally, you can use cx_Oracle.
> >
> > Java? You have only to download ojdbcN.jar and add it to Maven/Gradle.
> >
> > Why Python has no equivalent to JDBC?
>

> I've no idea what the hassles are with Oracle, as it's a database
> engine that I don't use. But with PostgreSQL, which I *do* use, I can
> assure you that it's much easier:
>
> $ pip install psycopg2
> >>> import psycopg2
>
> Job done.
>
> If Oracle is harder to use, it may be a specific issue with installing
> the Oracle client. Have you tried using pip to install cx_oracle?
>
> ChrisA

> --
> https://mail.python.org/mailman/listinfo/python-list
>

[Thomas Jollans]

On 19/05/2019 14.27, Marco Sulla via Python-list wrote:
> I programmed in Python 2 and 3 for many years, and I find it a fantastic
> language.
>
> Now I'm programming in Java by m ore than 2 years, and even if I found its
> code much more boilerplate, I admit that JDBC is fantastic.

Python has a the "Python Database API" (DB API 2.0)
https://www.python.org/dev/peps/pep-0249/

All SQL database modules that I know of use this API. It's more
decentralized than JDBC (not that I know much about JDBC), but it does
the trick: as long as the SQL syntax is sufficiently compatible, you can
easily swap in one database for another.

>
> One example over all: Oracle. If you want to access an Oracle DB from
> Python, you have to:

If Oracle wants to be a pain in the arse, that's Oracle's doing. The
open source databases aren't like that.

-- Thomas

[Marco Sulla]

On Mon, 20 May 2019 at 17:32, Thomas Jollans <tj...@tjol.eu> wrote:

> Python has a the "Python Database API" (DB API 2.0)
> https://www.python.org/dev/peps/pep-0249/
>

So why Oracle need instantclient for using cx_Oracle? They say they use
DB-API:

> *cx_Oracle* is a Python extension module that enables access to Oracle
> Database. It conforms to the Python database API 2.0 specification
<http://www.python.org/topics/database/DatabaseAPI-2.0.html> with
> a considerable number of additions and a couple of exclusions.

https://oracle.github.io/python-cx_Oracle/

On Sun, 19 May 2019 at 23:47, Chris Angelico <ros...@gmail.com> wrote:

> I've no idea what the hassles are with Oracle, as it's a database
> engine that I don't use. But with PostgreSQL, which I *do* use, I can
> assure you that it's much easier
>

I use Postgres if I can choose, but companies uses Oracle unluckily. Oracle
and MSSQL. And I must say that surprisingly, being a Microsoft product, I
find MSSQL more simple to install than Oracle, like Postregres, and has an
easier SQL syntax. Like Postgres.

[Andrew Z]

What does 249 specification mention about drivers?

> --
> https://mail.python.org/mailman/listinfo/python-list
>

[Michael Torrie]

On 05/20/2019 04:23 PM, Andrew Z wrote:
> What does 249 specification mention about drivers?

Nothing that I can see.

But it stands to reason that at some point the Python code is going to
have to interface with the SQL database server's API. And when the
database in question is proprietary, the original poster should probably
not be surprised that a special driver install is required. I assume
it's also required for JDBC also, but since Java is owned by Oracle,
they probably install such things automatically.

[Chris Angelico]

On Tue, May 21, 2019 at 8:25 AM Andrew Z <for...@gmail.com> wrote:
>
> What does 249 specification mention about drivers?
>

Nothing. PEP 249 defines how a Python app communicates with the
database module. For instance:

import psycopg2
db = psycopg2.connect("...")
with db, db.cursor() as cur:
cur.execute("select * from people where title = %s", [title])
for person in cur:
print(person)

You could replace the first two lines with, say, mysql.connector, or
sqlite3, and the rest of the code doesn't need to care. The only part
that needs to be database-engine-specific is the connection string
passed to the connect() function.

Some of these modules require lower level drivers, because they're
simple wrappers around lower-level C APIs. Others are linked
statically with the entire code required to make the connection (the
sqlite3 module, I believe, is like that). Still others are pure Python
implementations of wire protocols, which means they don't need
anything more than the Python standard library and its socket
services. In the case of Oracle, it sounds like it depends on a
previously-installed Oracle client.

ChrisA

[Andrew Z]

Exactly right. Im not sure why Marco is wondering about native drivers not
to be a part of python module (for oracle).
Id be very unhappy, if a python module come with a native drivers for
something as complex as a database.

Cx guys gave you the power to install what and how your project requires.
In Microsoft case .. that would be a different discussion :)

On a flip side, if you dont need oracle specific features, then maybe u
should use something basic for data storage. //but thats a different
discussion


On Mon, May 20, 2019, 19:18 Michael Torrie <tor...@gmail.com> wrote:

> On 05/20/2019 04:23 PM, Andrew Z wrote:
> > What does 249 specification mention about drivers?
>

> --
> https://mail.python.org/mailman/listinfo/python-list
>

[Chris Angelico]

On Tue, May 21, 2019 at 11:10 AM Andrew Z <for...@gmail.com> wrote:
>
> Exactly right. Im not sure why Marco is wondering about native drivers not
> to be a part of python module (for oracle).
> Id be very unhappy, if a python module come with a native drivers for
> something as complex as a database.

I wouldn't be. It's just a client, and sometimes the clients are
pretty simple (see, for instance, the mysql.connector module, which
AIUI is a pure-python implementation of the MySQL wire protocol). The
main reason Python doesn't include any database modules other than
sqlite3 is that there are just way too many of them (PostgreSQL,
MySQL, Oracle, MS SQL, IBM DB2, etc, etc, etc).

> On a flip side, if you dont need oracle specific features, then maybe u
> should use something basic for data storage. //but thats a different
> discussion

I rather doubt that this is open to discussion. From the sound of
things, the choice of database back end has already been made, and the
Python script just has to cope. Otherwise, PostgreSQL would be an
entirely better alternative.

ChrisA

[Paul Rubin]

Andrew Z <for...@gmail.com> writes:
> Exactly right. Im not sure why Marco is wondering about native drivers not
> to be a part of python module (for oracle).
> Id be very unhappy, if a python module come with a native drivers for
> something as complex as a database.

Why would you be unhappy? PHP comes with ODBC if you want to use it
with Oracle.

[Adriaan Renting]

I think it's partially a design philosophy difference.

Java was meant to be generic, run anywhere and abstract and hide
differences in its underlying infrastructure. This has led to the Java
VM, and also JDBC I guess.

Python was more of a script interpreted C-derivative, much closer to
the bare metal, and thus much less effort was made to hide and
abstract.

It might also have to do with the different developer cultures. Java
was much more Cathedral, and Python much more Bazaar.
Bazaar type development seems to be much less able to come up with high
level abstraction.

At least that's my guess.

Adriaan

>>> On 19-5-2019 at 14:27, Marco Sulla via Python-list
<pytho...@python.org>
wrote:

> I programmed in Python 2 and 3 for many years, and I find it a
fantastic
> language.
>
> Now I'm programming in Java by m ore than 2 years, and even if I
found its
> code much more boilerplate, I admit that JDBC is fantastic.
>

> One example over all: Oracle. If you want to access an Oracle DB
from
> Python, you have to:
>

[Paul Moore]

On Tue, 21 May 2019 at 13:50, Adriaan Renting <ren...@astron.nl> wrote:
>
>
> I think it's partially a design philosophy difference.
>
> Java was meant to be generic, run anywhere and abstract and hide
> differences in its underlying infrastructure. This has led to the Java
> VM, and also JDBC I guess.
>
> Python was more of a script interpreted C-derivative, much closer to
> the bare metal, and thus much less effort was made to hide and
> abstract.

In practice, I think it was more to do with the "Pure Java"
philosophy/movement, which resulted in a lot of investment into
reinventing/re-implementing code in Java - in this particular case,
the network protocols that database clients and servers use to
communicate. Because a commercial product like Oracle doesn't document
those protocols, open-source reimplementations are hard, if not
impossible. The Java drivers for Oracle are supplied by Oracle
themselves - Oracle could also provide pure-Python implementations of
the protocols, but they don't - so Python interfaces have to rely on
the libraries Oracle *do* provide.

The same is true of other database interfaces - although in the case
of open source databases it *is* possible to implement the protocol in
pure Python. It's just far less convenient when interfacing to the
existing C libraries is pretty straightforward. For Java interfaces,
linking to "native" libraries is more complex, and generally frowned
on, so there's pressure to implement a "pure Java" solution.

Not having to manage native binaries is a big advantage Java has,
certainly. But conversely, it's meant that building the Java ecosystem
required a massive amount of effort. Luckily, commercial interests
have paid for much of that effort and have made the resulting
libraries freely available. Who knows where we would be if Python had
received a similar level of investment :-)

Paul

[Bischoop]

On 2019-05-19, Marco Sulla <mail.py...@marco.sulla.e4ward.com> wrote:
>blablabla
>blablablalbla
>blablalblalbalblabla

There's no perfect language mate, in some one is easier in other not,
normal surprised you notice it so late.

[Christian Gollwitzer]

Am 21.05.19 um 14:27 schrieb Adriaan Renting:

> Java was meant to be generic, run anywhere and abstract and hide
> differences in its underlying infrastructure. This has led to the Java
> VM, and also JDBC I guess.
>
> Python was more of a script interpreted C-derivative, much closer to
> the bare metal, and thus much less effort was made to hide and
> abstract.

Python closer to the metal than Java? This is nonsense. It is exactly
the opposite. As a simple empirical proof, there are compilers which
compile Java to native code (gcj) with comparable performance than C
code, while such a thing is almost not doable for Python, only for
restricted subsets. Java code can be "manually compiled" into C++ code
with only a few tweaks, most notably you need a garbage collector, but
that's it - apart from a huge library, maybe.
Python code with dynamic typing cannot be statically compiled in the
same way, which you pay for by a factor of 100 in execution speed.

Christian

[Marco Sulla]

On Tue, 21 May 2019 at 01:20, Michael Torrie <tor...@gmail.com> wrote:

> On 05/20/2019 04:23 PM, Andrew Z wrote:

> I assume
>

Nope, it requires the instantclient and, on Linux only, also the
installclient-devel package. And a bunch of obscure and terrible settings,
like LD_LIBRARY_PATH...

On Tue, 21 May 2019 at 03:42, Chris Angelico <ros...@gmail.com> wrote:

> From the sound of
> things, the choice of database back end has already been made, and the
> Python script just has to cope. Otherwise, PostgreSQL would be an
> entirely better alternative.
>

Yes it's that the problem.
Well I love Postgres, but IMHO the big disadvantages over Oracle are two:
BLOBs and performance.

On Tue, 21 May 2019 at 15:14, Paul Moore <p.f....@gmail.com> wrote:

> Because a commercial product like Oracle doesn't document
> those protocols, open-source reimplementations are hard, if not
> impossible.
>

IMHO this is the more logical answer to my doubts.

On Tue, 21 May 2019 at 18:17, Dennis Lee Bieber <wlf...@ix.netcom.com>
wrote:

> It wouldn't surprise me to find that they include the JDBC
> driver layer for Oracle database in the Java package}


It would surprise _me_. Oracle did not even uploaded ojdbc to the maven
repository...

I discovered it two years and an half ago, when I started to program in
Java too. Sorry if I didn't had the time to post but I'm working. And sorry
again, I don't know the whole basics of every programming language in the
world. I only programmed in Python 2 and 3, Cython, C, C++, Fortran, Qt,
PHP, Visual Basic .NET, LabView and Javascript.

On Sun, 26 May 2019 at 19:53, Christian Gollwitzer <auri...@gmx.de> wrote:

> Python closer to the metal than Java? This is nonsense.
>

Well, it seems to me the majority of python db drivers uses Cython, Python
C API or external C libraries. So in this particular case Python *could* be
more performant than Java. I don't know, you should do some benchmark with
different queries, different in nature, complexity and number of rows
retrieved, on the same db, accessed once with Java and once with Python.
The problem is benchmarking Java not so easy as Python. It's very boring.

[Peter J. Holzer]

On 2019-06-16 15:45:42 +0200, Marco Sulla via Python-list wrote:
> On Tue, 21 May 2019 at 01:20, Michael Torrie <tor...@gmail.com> wrote:
> > On 05/20/2019 04:23 PM, Andrew Z wrote:
> > I assume
> >

It would have helped if you also quoted what Andrew assumed, not just
that he assumed something.

For the record, he assumed that JDBC also needs a native driver but that
this is automatically installed. (the assumption was wrong)

> Nope, it requires the instantclient and, on Linux only, also the
> installclient-devel package. And a bunch of obscure and terrible settings,
> like LD_LIBRARY_PATH...

If "it" means the Oracle JDBC driver, then it doesn't require those
things, which I think was your original point. Anything which uses the
Oracle native driver (Python's cx_Oracle, Perl's DBD:Oracle, etc.) needs
these things, which makes setting them up more complicated.

The fact is that Oracle chose to write a pure-Java implementation of
their driver which is really easy to install (although it might have
some limitations) but they did no such thing for other languages - plus
their license terms make it hard to bundle their driver, so everybody
needs to go through the "install instantclient, configure instantclient,
install language-specific drivers" dance (which btw is much easier now
than it was before instantclient)

So that's the answer to your question: Because Oracle made it so.

> On Tue, 21 May 2019 at 03:42, Chris Angelico <ros...@gmail.com> wrote:
> > From the sound of things, the choice of database back end has
> > already been made, and the Python script just has to cope.
> > Otherwise, PostgreSQL would be an entirely better alternative.
> >
>
> Yes it's that the problem.
> Well I love Postgres, but IMHO the big disadvantages over Oracle are two:
> BLOBs and performance.

Interesting. I think PostgreSQL's bytea type is much easier to work with
than Oracle's BLOBs, and they cover my needs (they have a 1 GB size
limit, but so far I haven't run into that). PostgreSQL also has LOs,
which seem to be even more awkward to use than Oracle's BLOBs, but I
haven't actually used them so I might be wrong.

And over the years I got the impression that PostgreSQL's performance is
at least comparable to that of Oracle - at least for "normal" databases,
not something like RAC (which I never used because we couldn't afford
it anyway). One of our databases was recently migrated to PostgreSQL and
the guy who mostly works with it was quite enthusiastic about the speed
(but there might have been other changes like new hardware ...).

> On Tue, 21 May 2019 at 15:14, Paul Moore <p.f....@gmail.com> wrote:
> > Because a commercial product like Oracle doesn't document
> > those protocols, open-source reimplementations are hard, if not
> > impossible.
> >
>
> IMHO this is the more logical answer to my doubts.

Oracle is also well-known for employing an army of lawyers, so the
technical difficulties might just be the beginning.

[...]

> On Sun, 26 May 2019 at 19:53, Christian Gollwitzer <auri...@gmx.de> wrote:

> > Python closer to the metal than Java? This is nonsense.
> >
>

> Well, it seems to me the majority of python db drivers uses Cython,
> Python C API or external C libraries.

Which I would take as an indication that Python is "further from the
metal". Python isn't really suited for the job so you have to switch to
a lower-level (closer to the metal) language to implement it.

> So in this particular case Python *could* be more performant than
> Java.

Performance is only very indirectly related to being close to the metal.

hp

--
_ | Peter J. Holzer | we build much bigger, better disasters now
|_|_) | | because we have much more sophisticated
| | | h...@hjp.at | management tools.
__/ | http://www.hjp.at/ | -- Ross Anderson <https://www.edge.org/>