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.