Re: [sqlalchemy] MySQL default driver for python3

['Van Klaveren, Brian N.' via sqlalchemy]

Hi Tomek,

You actually want mysqlclient, which is the maintained fork of mysqldb:

https://pypi.org/project/mysqlclient/

Brian


On Mar 14, 2019, at 7:41 AM, Tomek Rożen 
mailto:tomek.ro...@gmail.com>> wrote:

Hi,

'mysqldb' is the default driver, however it does not support python3. Any 
chance to update the default driver for python3? Otherwise I have to always 
remember to use "mysql+pymysql://..." in DB URLs.

Alternatively, is there a way to override that default from application code?

Thanks,
Tomek



--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to 
sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to 
sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Re: Security Implications of Directly Executing Queries Compiled to Strings

['Van Klaveren, Brian N.' via sqlalchemy]

As has been said, if you are generating the SQL, you will be fine so long as 
you use parameters and no blind string interpolation.

This isn't really any different that any other API in that regard - obviously 
you don't want to allow a non-substituted first name field of the form `'; DROP 
TABLE USERS;`

If you are worried about security in terms of accepting SQL (which sounds like 
it's a different concern than yours), you will really want a full-fledged 
parser.

In astronomy, we have a long history of actually letting users execute SQL 
directly via a REST API (there's even a specification called the "Table Access 
Protocol"). There's some SQL parsers out there in Python, but not much that's 
easily reusable or full features. I had started porting the Presto parser to 
Python (github.com/slaclab/lacquer)  but I 
ended up with something kind of buggy (someday I'll try to get back to it... 
Antlr4 has good Python support now) and we had switched that service to Java 
because we needed better threading and I switched to directly using the Presto 
SQL parser. You could easily write a query validator, for example as a CLI or 
as a REST API, to verify there's only one statement with the presto framework 
if you want good SQL support and to verify the statements and operations are 
okay. This is probably out of scope for your needs, but it may be something to 
bring up to your team if you are worried about security more generally for this 
service.

Brian


On Mar 8, 2019, at 9:56 AM, Walt 
mailto:waltas...@gmail.com>> wrote:



On Friday, March 8, 2019 at 11:32:01 AM UTC-6, Jonathan Vanasco wrote:
Do you control the HTTP API or is this someone else's system?

It's someone else's. I'm living in a world where folks have generated their SQL 
with regular old string processing & interpolation in Python because there's no 
database connection to work with. I'm hoping to replace this with sqlalchemy + 
.compile()-ing.

Does the API just execute the raw sql it is provided, and you're looking to 
generate that?  What you want to do is pipe a SqlAlchemy query into a function 
that can compile it into the right statement for your database.  Below is an 
example of PostgreSQL that worked on SqlAlchemy 1 (probably 1.3. too but I 
haven't tested):

Yep, just executes it raw. I've got the basic examples working, I just wanted 
to understand the limitations of using SQLAlchemy vs. DBAPI literal binding, 
particularly any security implications, before I start advocating for more 
people in my organization to start using this workflow.

The security concerns you brought up deal with how/what SqlAlchemy treats as 
trusted user input or not.  Most functions in SqlAlchemy will escape the values 
by default, very few will not and are documented with a dragon in the database. 
 If you are using values for those items in Sql you need to filter them 
yourself.

Thanks, so the limitations the documentation is bringing up are more that 
SQLAlchemy doesn't know how to bind the same variety of types as does the DBAPI?

For 99.9% of use cases though, you can just compile your sql to the database's 
dialect and just send it without worry.

Hooray!

Thank you for your response!

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to 
sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to 
sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Proper way to handle new 128 character identifier limit in Oracle >= 12.2

['Van Klaveren, Brian N.' via sqlalchemy]

Hi,

Oracle 12.2 now allows 128 character length identifiers:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/newft/new-features.html#GUID-64283AD6-0939-47B0-856E-5E9255D7246B

It'd be great if sqlalchemy knew about this, but what's the proper way of 
handling this? Just use the existing dialect and monkey patch 
max_identifier_length or create a new dialect?

Thanks,
Brian


-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Custom type compilers interplace with with_variant

['Van Klaveren, Brian N.' via sqlalchemy]

Hi,

I want to create a custom type for TINYINT and DOUBLE.

I've defined them as custom types. 

I want to use with_variant for them, so that in sqlite they print out as 
TINYINT and DOUBLE. 

But I also want them to use the variants defined for other databases, like 
Oracle and Postgres.

The first part works fine, the second part, not so much.

What's really the best way to do this?

Code is attached below.

Brian


from sqlalchemy.ext.compiler import compiles
from sqlalchemy import SmallInteger, Float
from sqlalchemy import types
from sqlalchemy.dialects import mysql, oracle, postgresql
from sqlalchemy import create_engine, MetaData, Column, Boolean
from sqlalchemy.schema import Table

MYSQL = "mysql"
ORACLE = "oracle"
POSTGRES = "postgresql"
SQLITE = "sqlite"

class TINYINT(SmallInteger):
"""The non-standard TINYINT type."""
__visit_name__ = 'TINYINT'


class DOUBLE(Float):
"""The non-standard DOUBLE type."""
__visit_name__ = 'DOUBLE'

@compiles(TINYINT)
def compile_tinyint(type_, compiler, **kw):
return "TINYINT"


@compiles(DOUBLE)
def compile_double(type_, compiler, **kw):
return "DOUBLE"

byte_map = {
MYSQL: mysql.TINYINT(),
ORACLE: oracle.NUMBER(3),
POSTGRES: postgresql.SMALLINT(),
}

double_map = {
MYSQL: mysql.DOUBLE(),
ORACLE: oracle.BINARY_DOUBLE(),
POSTGRES: postgresql.DOUBLE_PRECISION(),
}

def byte(**kwargs):
return _vary(TINYINT(), byte_map, kwargs)


def double(**kwargs):
return _vary(DOUBLE(), double_map, kwargs)

def _vary(type, variant_map, overrides):
for dialect, variant in overrides.items():
variant_map[dialect] = variant
for dialect, variant in variant_map.items():
type.with_variant(variant, dialect)
return type


metadata = MetaData()

t = Table("MyTable", metadata,
  Column("my_byte", byte()),
  Column("my_double", double())
  )


def metadata_dump(sql, *multiparams, **params):
# print or write to log or file etc
print(sql.compile(dialect=engine.dialect))

engine = create_engine("sqlite://", strategy='mock', executor=metadata_dump)
metadata.create_all(engine)


def metadata_dump(sql, *multiparams, **params):
# print or write to log or file etc
print(sql.compile(dialect=engine.dialect))

engine = create_engine("oracle://", strategy='mock', executor=metadata_dump)
metadata.create_all(engine)


-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Is FLOAT(53) the best way to guarantee a double-precision generic column type

[Van Klaveren, Brian N.]

I'm trying to work on a universal table generator based on in-memory table 
objects. The code is based on the work from the pandas to_sql.

I'll be targeting Oracle, Postgres, MySQL, and SQLite for sure.

It seems like making sure to use Float(53) is the best way to guarantee that a 
column will be generated with a double-precision Floating point in all of these 
database backends without introspecting the engine at runtime.

Pandas does that here:
https://github.com/pandas-dev/pandas/blob/master/pandas/io/sql.py#L879

Is this generally true?

Also, should I use the generic Float type, or the SQL FLOAT type? It doesn't 
seem like there's a huge functional difference.

Brian

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Adding a relationship for specific rows based on foreign value

[Van Klaveren, Brian N.]

Hi,

I want to represent a One to Many relationship with an additional default value 
that depends on a value in the foreign table.

I think what I want is something like the following:


class UserDatabase(Base):
db_id = Column(Integer, primary_key=True)
repo_id = Column(Integer, ForeignKey("UserDatabase.db_id"), nullable=True)
name = Column(String(128))
description = Column(Text)
conn_host = Column(String(128))
conn_port = Column(Integer)
schemas = relationship("UserDatabaseSchema", lazy="dynamic")
# Where is_default_schema == True
default_schema = relationship("UserDatabaseSchema", primaryjoin=???, 
lazy="dynamic")

class UserDatabaseSchema(Base):
schema_id = Column(Integer, primary_key=True)
db_id = Column(Integer, ForeignKey("UserDatabase.db_id"))
name = Column(String(128))
description = Column(Text)
is_default_schema = Column(Boolean)


but I'm not sure if primaryjoin is the proper argument for relationship and, if 
it is, what the expression should be. Or is this something that's best handled 
a different way?


Thanks,
Brian

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Additional error handling using MySQLdb.connection extended API

[Van Klaveren, Brian N.]

Hi,

I need access to MySQLdb.connection.error() and MySQLdb.connection.errno(), as 
we have a database which is throwing custom error codes that are higher than 
CR_MAX_ERROR (for reference: 
https://github.com/PyMySQL/mysqlclient-python/blob/master/_mysql.c#L124)

My actual code looks like something along these lines:


from sqlalchemy import create_engine, text
engine = create_engine(url)
sql = "SELECT... "

try:
   conn = engine.connect()
   rows = conn.execute(text(sql))
except Exception as e:
   if hasattr(conn.connection, "error"):
   print(conn.connection.errno())
   print(conn.connection.error())

I've also tried:

err_conn = conn._branch()
try:
   err_conn = engine.connect()
   rows = err_conn.execute(text(sql))
except Exception as e:
   if hasattr(err_conn.connection, "error"):
   print(err_conn.connection.errno())
   print(err_conn.connection.error())


with no luck.

I'm not sure how to write a minimal test that just uses vanilla MySQL, because 
I'm not sure how to trigger an error with a errno > CR_MAX_ERROR other than in 
our system.

I'm losing the information somehow, via a connection being closed/reopened or 
something? I'm not really sure. What might be the a way to handle this without 
just using raw_connection or writing a new dialect?

Thanks,
Brian

-- 
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] get_schema_names doesn't work with SQLite attached databases

[Van Klaveren, Brian N.]

Hi,

I'm trying to find the attached databases of a SQLite database. I was expecting 
Inspector.get_schema_names to return something like:


$ sqlite3 foo.db
sqlite> create table a (a_id integer);

sqlite3 bar.db
sqlite> create table b (b_id integer);


from sqlalchemy import create_engine
from sqlalchemy.inspection import inspect

engine = create_engine("sqlite://", echo=True)

engine.execute("attach database 'foo.db' as foo")
engine.execute("attach database 'bar.db' as bar")

refl = inspect(engine)

refl.get_table_names(schema="foo") # works
refl.get_table_names(schema="bar") # works

refl.get_columns("a", schema="foo") # works
refl.get_columns("b", schema="bar") # works

refl.get_schema_names() # doesn't work, returns []



It doesn't seem the SQLite dialect supports this, but it does seem like the 
SQLite dialect could support this via the equivalent of this:


@reflection.cache
def get_schema_names
dl = connection.execute("PRAGMA database_list")
return [r[1] for r in dl]


Is this reasonable? Could it be included in the SQLite dialect?

Brian

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Retrieving a row/cursor's description object from a ResultSetProxy

[Van Klaveren, Brian N.]

Hi,

I want to get extra type information from a given column after performing a 
query like the following:

results = engine.execute(text(SELECT a, b FROM Attributes))

It seems the only way to really do this is to use cursor from 
results.cursor.description.

Is this the preferred method, or is there a better alternative?

I ask because my database may have decimals larger than double precision, and 
integers larger than 64 bits, and I'm confused as to what the call:
type(row[0]).

...would return in this case.

Brian

-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] The use of SQLAlchemy for a long term project

[Van Klaveren, Brian N.]

Thanks for the detailed response. I didn't think to look to Red Hat's to see if 
they backported security fixes, so that's good to know.

As for the undefined behavior with respect to less-than idiomatic programming, 
I think that's something we'll definitely need to keep in mind and hopefully be 
able to enforce with code reviews.

I've personally been wanting to use SQLAlchemy, but it's important we 
understand implications for decisions like this when the lifetime of code is 
guaranteed to surpass a decade. I'm pretty sure we'll end up using SQLAlchemy 
for the long term.

Thanks again,
Brian


On Apr 18, 2015, at 2:47 PM, Mike Bayer 
mike...@zzzcomputing.commailto:mike...@zzzcomputing.com wrote:



On 4/17/15 6:58 PM, Van Klaveren, Brian N. wrote:
Hi,

I'm investigating the use and dependency on SQLAlchemy for a long-term 
astronomy project. Given Version 1.0 just came out, I've got a few questions 
about it.

1. It seems SQLAlchemy generally EOLs versions after about two releases/years. 
Is this an official policy? Is this to continue with version 1.0 as well? Or is 
it possible 1.0 might be a something of a long-term release.
2. While well documented and typically minimal, SQLAlchemy does have occasional 
API and behavioral changes to be aware of between versions. Is the 1.0 API more 
likely to be stable on the time frame of ~4 years?

Put another way, would you expect that it should be easier to migrate from 
version 1.0 to the 1.4 (or whatever the current version is) of SQLAlchemy in 
five years than it would be to migrate from 0.6 to 1.0 today.

I know these questions are often hard to answer with any certainty, but these 
sorts of projects typically outlive the software they are built on and are 
often underfunded as far as software maintenance goes, so we try to plan 
accordingly.

(Of course, some people just give up and through everything in VMs behind 
firewalls)
Well the vast majority of bugs that are fixed, like 99% of them, impact only 
new development, that is, they only have a positive impact someone who is 
writing new code, using new features of their database backend, or otherwise 
attempting to do something new; they typically only serve to raise risk and 
decrease stability of code that is not under active development and is 
stabilized on older versions of software.

These kinds of issues mean that some way of structuring tables, mapped classes, 
core SQL or DDL objects, ORM queries, or calls to a Session produce some 
unexpected result, but virtually always, this unexpected result is consistent 
and predictable.   An application that is sitting on 0.5 or 0.6 and is running 
perfectly fine, because it hasn't hit any of these issues, or quite often 
because it has and is working around them (or even relying upon their behavior) 
would not benefit at all from these kinds of fixes being backported, but would 
instead have a greater chance of hitting a regression or a change in 
assumptions if lots of bugfixes were being backported from two or three major 
versions forward.

So it's not like we don't backport issues three or four years back because it's 
too much trouble, it's because these backports wouldn't benefit anyone and they 
would only serve to wreak havoc with old and less maintained applications when 
some small new feature or improvement in behavioral consistency breaks some 
assumption made by that application.

As far as issues that are more appropriate for backporting, which would be 
security fixes and stability enhancements, we almost never have issues like 
that; the issues we have regarding stability, like memory leaks and race 
conditions, again typically occur in conjunction with a user application doing 
something strange and unexpected (e.g. new development), and as far as security 
issues the only issue we ever had like that even resembled a security issue was 
issue 2116 involving limit/offset integers not being escaped, which was 
backported from 0.7 to 0.6.  Users who actually needed enterprise-level 
longevity who happened to be using for example the Red Hat package could see 
the backport for this issue backported all the way to their 0.5 and 0.3 
packages.  But presence of security/memory leak/stability issues in modern 
versions is extremely rare, and we generally only see new issues involving 
memory or stability as a result of new features (e.g. regressions).

There's also the class of issues that involve performance enhancements.   Some 
of these features would arguably be appropriate to backport more than several 
major versions, but again they are often the result of significant internal 
refactorings and definitely would raise risk for an older application not 
undergoing active development.   An older application that wants to take 
advantage of newer performance features would be better off going through the 
upgrade process than risking running on top of a library that is a hybrid of 
very old code and backported newer approaches, which will see

[sqlalchemy] The use of SQLAlchemy for a long term project

[Van Klaveren, Brian N.]

Hi,

I'm investigating the use and dependency on SQLAlchemy for a long-term 
astronomy project. Given Version 1.0 just came out, I've got a few questions 
about it.

1. It seems SQLAlchemy generally EOLs versions after about two releases/years. 
Is this an official policy? Is this to continue with version 1.0 as well? Or is 
it possible 1.0 might be a something of a long-term release.
2. While well documented and typically minimal, SQLAlchemy does have occasional 
API and behavioral changes to be aware of between versions. Is the 1.0 API more 
likely to be stable on the time frame of ~4 years?

Put another way, would you expect that it should be easier to migrate from 
version 1.0 to the 1.4 (or whatever the current version is) of SQLAlchemy in 
five years than it would be to migrate from 0.6 to 1.0 today.

I know these questions are often hard to answer with any certainty, but these 
sorts of projects typically outlive the software they are built on and are 
often underfunded as far as software maintenance goes, so we try to plan 
accordingly. 

(Of course, some people just give up and through everything in VMs behind 
firewalls)

Brian


-- 
You received this message because you are subscribed to the Google Groups 
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.