Re: [sqlalchemy] Re: Register function example (perhaps) to remove tab, newline or carriage return from column.

[Jonathan Vanasco]

On Wednesday, February 14, 2018 at 6:49:47 AM UTC-5, Simon King wrote:
 

> For what it's worth, I would do it something like this:  
>

that's elegant. I'd rather do that now too. 

-- 
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: Register function example (perhaps) to remove tab, newline or carriage return from column.

[Simon King]

On Fri, Feb 9, 2018 at 7:38 PM, Jeremy Flowers
 wrote:
> And I do want to use this functionality repeatedly on many columns, hence
> the idea of registering a function.
>

For what it's worth, I would do it something like this:

def removechars(col, chars):
for c in chars:
col = sa.func.REPLACE(col, c, '')
return col

def removewhitespace(col):
return removechars(col, '\t\r\n')

print removewhitespace(Jobdtl.jobdtl_cmd)

Hope that helps,

Simon

-- 
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] Re: Register function example (perhaps) to remove tab, newline or carriage return from column.

[Jonathan Vanasco]

On Sunday, February 11, 2018 at 8:07:23 PM UTC-5, Jeremy Flowers wrote:
>
> I'm afraid it just doesn't work. I would think you have to import String 
> too..
>

Please run the entirety of what I pasted above as it's own file and share 
the error.  That is a self-contained example that runs as a file under 
Python 2 and 3.  String is imported at the top section under 'standard 
imports'

In the example above, I use two forms (SELECT and INSERT), and iterate over 
a string / number / None, and strings containing the characters you strip.  

-- 
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] Re: Register function example (perhaps) to remove tab, newline or carriage return from column.

[Jeremy Flowers]

I'm afraid it just doesn't work. I would think you have to import String 
too..

from sqlalchemy.types import String

The only difference between your and my code, is i don't have a default and 
a sqlite.. I tried it as default, and oracle... not both.
Also the custom function is in a separate module that I load into my main 
one.. But it should not make an iota of difference.
Python 3.6.4
Sqlalchemy 1.2.2
sqlacodegen-1.1.6 to generate a module of domain classes, in which I 
override 
__repr__ on Base ( which is an implementation of 
whatever  declarative_base() returns.


return compiler.SQLCompiler.visit_select(self, select, **kwargs)
  File 
"C:\opt\python\python-3.6.4-64-bit\lib\site-packages\sqlalchemy\sql\compiler.py",
 
line 1807, in visit_select
text, select, inner_columns, froms, byfrom, kwargs)
  File 
"C:\opt\python\python-3.6.4-64-bit\lib\site-packages\sqlalchemy\sql\compiler.py",
 
line 1886, in _compose_select_body
for f in froms])
  File 
"C:\opt\python\python-3.6.4-64-bit\lib\site-packages\sqlalchemy\sql\compiler.py",
 
line 1885, in 
[f._compiler_dispatch(self, asfrom=True, **kwargs)
  File 
"C:\opt\python\python-3.6.4-64-bit\lib\site-packages\sqlalchemy\sql\visitors.py",
 
line 81, in _compiler_dispatch
return meth(self, **kw)
  File 
"C:\opt\python\python-3.6.4-64-bit\lib\site-packages\sqlalchemy\dialects\oracle\base.py",
 
line 918, in visit_select
return compiler.SQLCompiler.visit_select(self, select, **kwargs)
  File 
"C:\opt\python\python-3.6.4-64-bit\lib\site-packages\sqlalchemy\sql\compiler.py",
 
line 1785, in visit_select
for name, column in select._columns_plus_names
  File 
"C:\opt\python\python-3.6.4-64-bit\lib\site-packages\sqlalchemy\sql\compiler.py",
 
line 1779, in 
self._label_select_column(
  File 
"C:\opt\python\python-3.6.4-64-bit\lib\site-packages\sqlalchemy\sql\compiler.py",
 
line 1557, in _label_select_column
**column_clause_args
  File 
"C:\opt\python\python-3.6.4-64-bit\lib\site-packages\sqlalchemy\sql\visitors.py",
 
line 81, in _compiler_dispatch
return meth(self, **kw)
  File 
"C:\opt\python\python-3.6.4-64-bit\lib\site-packages\sqlalchemy\sql\compiler.py",
 
line 683, in visit_label
OPERATORS[operators.as_] + \
TypeError: unsupported operand type(s) for +: 'NoneType' and 'str'


On Saturday, 10 February 2018 21:37:08 UTC, Jonathan Vanasco wrote:
>
> The following works for me on Python 2&3
>
> how are you generating an error ?
>
>
>
> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
> - - -
> #
> # Use this file to build your own SSCCE
> # SSCCE = Short, Self Contained, Correct (Compatible) Example
> # see http://sscce.org/
> #
> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
> - - -
>
>
> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
> - - -
> # Standard imports
>
> import sqlalchemy
> import sqlalchemy.orm
>
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import Boolean, Integer, Column, Unicode, ForeignKey, 
> String
>
>
> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
> - - -
> # You probably don't need to overwrite this
> Base = declarative_base()
>
> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
> - - -
> # Define some models that inherit from Base
>
> class Foo(Base):
> __tablename__ = 'foo'
> id = Column(Integer, primary_key=True)
> name = Column(Unicode)
>
>
> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
> - - -
> # set the engine
>
> engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
> Base.metadata.create_all(engine)
>
> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
> - - -
> # do a simple query to trigger the mapper error
>
> sessionFactory = sqlalchemy.orm.sessionmaker(bind=engine)
> s = sessionFactory()
>
>
> # - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
> - - -
> # test
>
> from sqlalchemy.sql import expression
> from sqlalchemy.ext.compiler import compiles
>
>
> class stripctrl(expression.FunctionElement):
> type = String()
> name = 'stripctrl'
>
>
> @compiles(stripctrl)
> def stripctrl_default(element, compiler, **kw):
> args = list(element.clauses)
> return "REPLACE(REPLACE(REPLACE(%s, CHR(9), ''), CHR(10), ''), 
> CHR(13), '')" % (compiler.process(args[0]))
>
>
> @compiles(stripctrl, 'sqlite')
> def stripctrl_sqlite(element, compiler, **kw):
> args = list(element.clauses)
> return "replace(replace(replace(%s, char(9), ''), char(10), ''), 
> char(13), '')" % (compiler.process(args[0]))
>
>
> cases = (None, "Aaaa", 123, 'A\ta', 'B\rb', 'C\nc,')
> for idx, case in enumerate(cases):
> f = Foo()
> f.id = idx
> f.name = case
> s.add(f)
> s.commit()
> 
> print("--SELECT")
> q = s.query(stripctrl(Foo.name)).all()
> print (q)
>
> print ("-- INSERT")
> for idx, case in 

[sqlalchemy] Re: Register function example (perhaps) to remove tab, newline or carriage return from column.

[Jonathan Vanasco]

The following works for me on Python 2&3

how are you generating an error ?



# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- -
#
# Use this file to build your own SSCCE
# SSCCE = Short, Self Contained, Correct (Compatible) Example
# see http://sscce.org/
#
# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- -


# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- -
# Standard imports

import sqlalchemy
import sqlalchemy.orm

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Boolean, Integer, Column, Unicode, ForeignKey, String


# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- -
# You probably don't need to overwrite this
Base = declarative_base()

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- -
# Define some models that inherit from Base

class Foo(Base):
__tablename__ = 'foo'
id = Column(Integer, primary_key=True)
name = Column(Unicode)


# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- -
# set the engine

engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
Base.metadata.create_all(engine)

# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- -
# do a simple query to trigger the mapper error

sessionFactory = sqlalchemy.orm.sessionmaker(bind=engine)
s = sessionFactory()


# - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
- -
# test

from sqlalchemy.sql import expression
from sqlalchemy.ext.compiler import compiles


class stripctrl(expression.FunctionElement):
type = String()
name = 'stripctrl'


@compiles(stripctrl)
def stripctrl_default(element, compiler, **kw):
args = list(element.clauses)
return "REPLACE(REPLACE(REPLACE(%s, CHR(9), ''), CHR(10), ''), CHR(13), 
'')" % (compiler.process(args[0]))


@compiles(stripctrl, 'sqlite')
def stripctrl_sqlite(element, compiler, **kw):
args = list(element.clauses)
return "replace(replace(replace(%s, char(9), ''), char(10), ''), 
char(13), '')" % (compiler.process(args[0]))


cases = (None, "Aaaa", 123, 'A\ta', 'B\rb', 'C\nc,')
for idx, case in enumerate(cases):
f = Foo()
f.id = idx
f.name = case
s.add(f)
s.commit()

print("--SELECT")
q = s.query(stripctrl(Foo.name)).all()
print (q)

print ("-- INSERT")
for idx, case in enumerate(cases):
f = Foo()
f.id = idx + 100
f.name = stripctrl(case)
s.add(f)
s.commit()



-- 
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] Re: Register function example (perhaps) to remove tab, newline or carriage return from column.

[Jeremy Flowers]

I've not been able to get that version working. Tried % method to pass 
params into format a string. Also tried string,format() method with {} 
syntax. No joy.
For example:

@compiles(stripctrl)
def stripctrl_default(element, compiler, **kw):

args = list(element.clauses)
  return
'REPLACE(REPLACE(REPLACE(%s, CHR(9), %s), CHR(10), %s), CHR(13), %s)' % 
(compiler.process(args[0]), '', '', '')

Or:

@compiles(stripctrl)
def stripctrl_default(element, compiler, **kw):

args = list(element.clauses)
return
'REPLACE(REPLACE(REPLACE({1}, CHR(9), {2}), CHR(10), {2}), CHR(13), 
{2})'.format(compiler.process(args[0]), '')

Or: (double quotes around whole string with two singles inside each time)
@compiles(stripctrl)
def stripctrl_default(element, compiler, **kw):

args = list(element.clauses)
return
"REPLACE(REPLACE(REPLACE(%s, CHR(9), ''), CHR(10), ''), CHR(13), '')" % 
(compiler.process(args[0]),)
gives:
TypeError: unsupported operand type(s) for +: 'NoneType' and 'str'


Splitting string into two lines to comply with PEP ..
@compiles(stripctrl)
def stripctrl_default(element, compiler, **kw):

args = list(element.clauses)
return
"REPLACE(REPLACE(REPLACE(%s, CHR(9), ''), CHR(10), ''), CHR(13), '')"
% (compiler.process(args[0]),)

   % (compiler.process(args[0]),)
^
SyntaxError: invalid syntax

Then get  back to first error with this
@compiles(stripctrl)
def stripctrl_default(element, compiler, **kw):

args = list(element.clauses)
return
"REPLACE(REPLACE(REPLACE(%s, CHR(9), ''), CHR(10), ''), CHR(13), '')" \
% (compiler.process(args[0]),)


TypeError: unsupported operand type(s) for +: 'NoneType' and 'str'


There's a similar issue here, but can't seem to reconcile to my issue:
https://stackoverflow.com/questions/15036594/python-3-3-typeerror-unsupported-operand-types-for-nonetype-and-str

On Friday, 9 February 2018 23:28:10 UTC, Jonathan Vanasco wrote:
>
> that won't work as you expect.
>
> You're applying the python character replacements to the arguments, and 
> then emitting that in sql.  You'll end up with the same 
> InstrumentedAttribute issue, because you're trying to operate on the Python 
> column instance in the orm defintion.
>
> the string returned by the custom compiler should be a sql function, 
> something like:
>
> """REPLACE(REPLACE(REPLACE(%s, CHR(9), ''), CHR(10), ''), CHR(13), 
> '')  % compiler.process(args[0])
>
> That would create sql from your example that reads:
>
> SELECT REPLACE(REPLACE(REPLACE( alias is>, CHR(9), ''), CHR(10), ''), CHR(13), '') LIMIT 1;
>
> If you just want to insert data, you could just use a python function that 
> does that string cleanup.
>
>

-- 
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] Re: Register function example (perhaps) to remove tab, newline or carriage return from column.

[Jeremy Flowers]

Hi Jonathan.
Thanks for the heads up.
I also subsequently wondered if the part of the right of the % was a 
tuple.. Have to read up a bit more about that... If so, then I'd possibly 
need the extra comma in the parentheses...

"""REPLACE(REPLACE(REPLACE(%s, CHR(9), ''), CHR(10), ''), CHR(13), '')  
% compiler.process(args[0],)

On Friday, 9 February 2018 23:28:10 UTC, Jonathan Vanasco wrote:
>
> that won't work as you expect.
>
> You're applying the python character replacements to the arguments, and 
> then emitting that in sql.  You'll end up with the same 
> InstrumentedAttribute issue, because you're trying to operate on the Python 
> column instance in the orm defintion.
>
> the string returned by the custom compiler should be a sql function, 
> something like:
>
> """REPLACE(REPLACE(REPLACE(%s, CHR(9), ''), CHR(10), ''), CHR(13), 
> '')  % compiler.process(args[0])
>
> That would create sql from your example that reads:
>
> SELECT REPLACE(REPLACE(REPLACE( alias is>, CHR(9), ''), CHR(10), ''), CHR(13), '') LIMIT 1;
>
> If you just want to insert data, you could just use a python function that 
> does that string cleanup.
>
>

-- 
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] Re: Register function example (perhaps) to remove tab, newline or carriage return from column.

[Jonathan Vanasco]

that won't work as you expect.

You're applying the python character replacements to the arguments, and 
then emitting that in sql.  You'll end up with the same 
InstrumentedAttribute issue, because you're trying to operate on the Python 
column instance in the orm defintion.

the string returned by the custom compiler should be a sql function, 
something like:

"""REPLACE(REPLACE(REPLACE(%s, CHR(9), ''), CHR(10), ''), CHR(13), 
'')  % compiler.process(args[0])

That would create sql from your example that reads:

SELECT REPLACE(REPLACE(REPLACE(, CHR(9), ''), CHR(10), ''), CHR(13), '') LIMIT 1;

If you just want to insert data, you could just use a python function that 
does that string cleanup.

-- 
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] Re: Register function example (perhaps) to remove tab, newline or carriage return from column.

[Jeremy Flowers]

So I tried following along with this.

1) created a file called *custfunc.py*

In it I placed this:

from sqlalchemy.sql import expression
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.types import String


class stripctrl(expression.FunctionElement):
type = String()
name = 'stripctrl'


@compiles(stripctrl)
def stripctrl_default(element, compiler, **kw):

args = list(element.clauses)
return "%s" % (compiler
   .process(args[0])
   .replace("\t", "")
   .replace("\n", "")
   .replace("\r", ""))

2) Imported class into my main code..

from custfunc import stripctrl
print(session.query( stripctrl(Jobdtl.jobdtl_cmd) ).first()

I think that's right


On Friday, 9 February 2018 20:05:59 UTC, Jonathan Vanasco wrote:
>
> I should have explained something above better...
>
> The result I assumed you wanted to achieve is "creating SQL that will 
> handle data transformations".  That would allow you to write concise python 
> that will compiled into SQL which will be evaluated on the database 
> server.  You could also use other approaches that transform data in python 
> before hitting the database server.
>
> IMHO, using a custom compiler approach is easier than trying to do 
> something with `sqlalchemy.func` because it gives you more room to 
> customize how the sql compiles under different databases.
>

-- 
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] Re: Register function example (perhaps) to remove tab, newline or carriage return from column.

[Jeremy Flowers]

I had hoped something as simple as:
  Jobdtl.jobdtl_cmd
  .replace("\t", "")
  .replace("\n", "")
  .replace("\r", "")
  .label('command'), 
Can you cast InstrumentedAttribute to a string and back?
As it stands you get:
AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' 
object associated with Jobdtl.jobdtl_cmd has an attribute 'replace'

On Friday, 9 February 2018 19:18:55 UTC, Jeremy Flowers wrote:
>
> I'm aware with Oracle I could probably do something like this:
> REPLACE(REPLACE(REPLACE(o.jobdtl_cmd, CHR(9), ''), CHR(10), ''), CHR(13), 
> '') as COMMAND
>
> Is there a database agnostic way of doing this in SQLAlchemy?
>
> Would  it be possible to provide a list of numbers, like 9,10,13, that 
> would get iterated through to strip out the character and replace with an 
> empty string?
>
> I was looking under func, thinking it maybe something you could do there...
>
> Perhaps via Register function?
>
> http://docs.sqlalchemy.org/en/latest/core/functions.html#sqlalchemy.sql.functions.register_function
>
> Does anyone have an example of a register function?
>
> Many thanks again.
>

-- 
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.