Design of Version 5

[mkleehammer]

Hi all, there are lots of improvements we can make once we drop support for the older versions. It's well past time, so I'd like to gather the ideas here: new Python features, problems with the current design, etc.

One of the most important would be what minimum Python version to support.

I'd really like to use the stable API to eliminate the coordination with new versions of Python.

There is quite a bit of metadata pyodbc captures per connection, stored in a "connection info" that is not exposed. These are stored by connection-string hash. It might be useful to expose this as a connection factory object, that also holds user configuration like encodings. We could even provide pre-configured ones for common databases.

Currently pyodbc is all C. Would it help maintenance if the outer shell were Python and we dropped into C for the major work? The language transition is expensive, so we'd probably want most of the data structures in Python so that small functions don't require it.

Thoughts?

[keitherskine]

A very good discussion to have, thanks @mkleehammer . Here are a few initial thoughts of mine.

1. One downside to pyodbc being all C is that it's not so easy for developers to be clear about the published API. That was essentially the reason I had for creating the pyodbc.pyi file. If we could have an outer shell of Python hopefully that would make pyodbc.pyi obsolete.
2. Right now, only Python 3.7 and above are officially supported. We could aim for that, although I don't think it would be that difficult to support Python 3.6 as well.
3. There is still the old chestnut of text encoding and how to handle different encodings. Remember, pyodbc has to handle text not just in table data, but also in metadata (e.g. column names), the SQL statements themselves (although this is generally just passed to the driver), and also in things like error messages. Different RDBMS's and drivers do different things in different circumstances. Pyodbc does have the facility to set encodings in most cases, but perhaps pyodbc could be a little flexible in other cases by inferring the encoding. I appreciate this could be a serious rabbit-hole, but a lot of the issues we see are about UTF32 and UTF16 encoded text. It's not that difficult to figure out when text has been encoded in UTF32 or UTF16 (essentially, count the null bytes). We could make a better attempt to figure out the right encoding, or if necessary just return the bytes as is.
4. Add support for datetime offset fields, to better handle time values with timezone info.
5. I agree it would be great to add some way of setting the target DBMS so that pyodbc could configure itself appropriately. Either through an explicit API or by inferring it.
6. One thing I would suggest should be out of scope for this update is any more support for PEP 249. Pyodbc already covers the great majority of PEP 249 and I don't think we need to do any more at this point.

[mkleehammer]

Regarding (3), I always thought there should be a way to run a test if necessary and print out the settings. If there are some "standard" factories available like UTF8Factory, we could suggest them. Otherwise print out the necessary code to start with an empty factory and set the encodings.

[mkleehammer]

I've always tried to keep to standard Python as much as possible, but I'd really like to switch to pytest. I don't see anything moving on that front in Python development that would make it obsolete anytime soon.

[ndmlny-qs]

I would like to help with this effort. I think I will start with the following items.

1. Determine a coverage for the Python tests (using pytest-cov) in branch py3.
2. Begin updating/creating tests where pytest shows gaps in the tests.

More than likely I will create a branch called py3-pytest and branch off the existing py3 branch. Let me know if there are any objections, or ideas/do/don'ts about how to contribute to the project.

[keitherskine]

I just want to give a nudge to this discussion, specifically about dropping support for Python 2.7. I've been looking at ways of modernising the CICD pipelines, making them more PEP517 compliant, use of pytest, linting, etc., but that is being made difficult by having to deal with 2.7. To be honest, it's kind of a blocker at the moment.

@mkleehammer @gordthompson I'd like to suggest we do one more release on version 4, which would include support for Python 2.7 and then we say goodbye to 2.7, and move on to version 5 of pyodbc. I think the sooner we can do this the better. How does that sound?

[gordthompson]

I'd like to suggest we do one more release on version 4, which would include support for Python 2.7 and then we say goodbye to 2.7, and move on to version 5 of pyodbc. I think the sooner we can do this the better. How does that sound?

Sounds fine to me, especially if 2.7 support is now getting in the way of other things.

[mkleehammer]

FYI, I've started a py3 branch for version 5 that drops 2.7 support.

[mkleehammer]

Now that I've released 5.0.0 alpha2, I'm going to close this and we can have more specific discussions for the next couple of versions.