Re: [sqlalchemy] DefaultClause
I see, thus, this definition:
Column('abc', Unicode(20), server_default='abc')
Column('adef', Numeric(12,3), server_default=text('1.5')),
is equivalent to this one:
Column('abc', Unicode(20), DefaultClause('abc'))
Column('def', Numeric(12,3), DefaultClause(text('1.5'))),
But what about ColumnDefault and default as explained at:
http://docs.sqlalchemy.org/en/rel_0_9/core/defaults.html#server-side-defaults
(Scalar Defaults and Default Objects API)
I tried both, but I can't understand how it works.
I tried this definition:
test = Table('test', database.metadata,
Column('def', Integer, ColumnDefault(12)),
Column('rbc', Integer, default=12),
)
which produce this table (with no defaults):
CREATE TABLE test (
def INTEGER,
rbc INTEGER
)
j
On 01/09/2015 04:55 PM, Michael Bayer wrote:
you can use DefaultClause if you give it a text() object so that the quoting
behavior is defined. server_default is a shortcut for that.
jo jose.soa...@sferacarta.com wrote:
Do you suggest to use server_default instead of DefaultClause in all cases?
j
Il giorno giovedì 8 gennaio 2015 15:17:20 UTC+1, Michael Bayer ha scritto:
use server_default with text() as documented at
http://docs.sqlalchemy.org/en/rel_0_9/core/defaults.html#server-side-defaults.
jo jose@sferacarta.com wrote:
Hi all,
I have a problem with DefautClause. I need to assign a default to a numeric
column.
tbl['rischio_attivita'] = Table('rischio_attivita', database.metadata,
Column('id', Integer, Sequence('rischio_attivita_seq'),
primary_key=True),
Column('cod_attivita', Unicode(10),nullable=False),
Column('cod_caratteristica_rischio', Unicode(10), nullable=False),
Column('tipo_calcolo', Integer),
Column('algoritmo', Unicode(200)),
Column('fattore_x', Numeric(4,2), DefaultClause(1.0)),
ForeignKeyConstraint(['cod_attivita'], ['attivita.codice']),
ForeignKeyConstraint(['cod_caratteristica_rischio'],
['caratteristica_rischio.codice'])
)
I tried to assign a float to it and I got this error:
File
/home/sfera/sicer2/buildout/eggs/SQLAlchemy-0.9.3-py2.6-linux-x86_64.egg/sqlalchemy/util/langhelpers.py,
line 999, in assert_arg_type
(name, ' or '.join('%s' % a for a in argtype), type(arg)))
sqlalchemy.exc.ArgumentError: Argument 'arg' is expected to be one of type 'type 'basestring''
or 'class 'sqlalchemy.sql.elements.ClauseElement'' or 'class
'sqlalchemy.sql.elements.TextClause'', got 'type 'float''
[sfera@tstsferagamma01 release]$ vi sicer/BASE/model/sql.py
then I changed it to a string as in:
Column('fattore_x', Numeric(4,2), DefaultClause('1.0')),
and now I have this:
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-01722: invalid number
\nCREATE TABLE attivita (\n\tid INTEGER NOT NULL, \n\tcod_attivita NVARCHAR2(10)
NOT NULL, \n\tcod_caratteristica_rischio NVARCHAR2(10) NOT NULL, \n\ttipo_calcolo
INTEGER, \n\talgoritmo NVARCHAR2(200), \n\tfattore_x NUMERIC(4, 2) DEFAULT '1.0',
\n\tPRIMARY KEY (id), \n\tFOREIGN KEY(cod_attivita) REFERENCES attivita (codice),
\n\tFOREIGN KEY(cod_caratteristica_rischio) REFERENCES caratteristica_rischio
(codice)\n)\n\n {}
Is there another way to define a default clause?
thanks.
j
--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
--
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] session query and column names
Hi
I want to make a query with given filters received from client side. client
side sends a json array like this:
[
0DA: {conditionType: null, column: serialNumber, operator:
GREATER_THAN, value: 50},
1DG: {conditionType: AND, column: name, operator: EQUAL, value:
john }
]
I've managed to convert the json array into string query with a template
like this:
{condition_type} {column} {operator} {value}
But this is only the WHERE clause. for complete query i use
session.query(myModel)
which myModel has two relationships with lazy=joined mix with text() for
filters:
session.query(myModel).filter(text(sql_string))
But the problem is the session.query() uses alias column names(with AS
keyword) which my filter part with exact column names couln'd find those
aliased columns.
So Is there a way to tell session.query() use the exact column names? or i
should change my way?
Thanks.
--
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] session query and column names
Why don't you pass the params to session.query as a dictionary into
filter_by as in:
In [1]: by_where_clause=dict(specie_codice='42',
specie_descrizione='Nutrie')
In [2]: print session.query(Specie).filter_by( **by_where_clause ).count()
2015-01-12 12:37:40,518 INFO sqlalchemy.engine.base.Engine SELECT
count(*) AS count_1
FROM (SELECT specie.codice AS specie_codice, specie.descrizione AS
specie_descrizione, specie.cod_gruppo_specie AS
specie_cod_gruppo_specie, specie.categoria_prodotto AS
specie_categoria_prodotto, specie.interparto AS specie_interparto,
specie.gestazione AS specie_gestazione, specie.codice_bdn AS
specie_codice_bdn
FROM specie
WHERE specie.codice = %(codice_1)s AND specie.descrizione =
%(descrizione_1)s) AS anon_1
2015-01-12 12:37:40,518 INFO sqlalchemy.engine.base.Engine {'codice_1':
'42', 'descrizione_1': 'Nutrie'}
1
In [3]:
j
On 01/12/2015 11:45 AM, Mehdi wrote:
Hi
I want to make a query with given filters received from client side.
client side sends a json array like this:
|
[
0DA:{conditionType:null,column:serialNumber,operator:GREATER_THAN,value:50},
1DG:{conditionType:AND,column:name,operator:EQUAL,value:john}
]
|
I've managed to convert the json array into string query with a
template like this:
|
{condition_type} {column} {operator} {value}
|
But this is only the WHERE clause. for complete query i use
session.query(myModel) which myModel has two relationships with
lazy=joined mix with text() for filters:
|
session.query(myModel).filter(text(sql_string))
|
But the problem is the session.query() uses alias column names(with
AS keyword) which my filter part with exact column names couln'd
find those aliased columns.
So Is there a way to tell session.query() use the exact column names?
or i should change my way?
Thanks.
--
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
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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] Alembic 0.7.4 Released
Hey list - Alembic 0.7.4 is released. This release fixes a short series of issues with the autogenerate feature. The changelog is available at: http://alembic.readthedocs.org/en/latest/changelog.html#change-0.7.4 -- 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] Postgres OID column in SQLA 0.8
Hi Mike, Is there anyway, you can make the following changes to SQLA 0.8 ? https://bitbucket.org/zzzeek/sqlalchemy/commits/42bbb7163ada -- 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] Postgres OID column in SQLA 0.8
sorry, 0.8 is not accepting changes other than critical security patches. you can create your own OID type and patch it into sqlalchemy.dialects.postgresql.base.ischema_names directly, assuming this is breaking on reflection for you. Ven Karri karri@gmail.com wrote: Hi Mike, Is there anyway, you can make the following changes to SQLA 0.8 ? https://bitbucket.org/zzzeek/sqlalchemy/commits/42bbb7163ada -- 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. -- 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] Re: Debug ODBC Connection String for Custom Dialect
Looks like the *nix version of the ODBC driver I was using is simply
wrapping the Windows logic for parameters for DNS-less connections contrary
to the documentation
For the record in Teradata for DNS-based connections the ODBC connection
string would be on Linux:
'dsn=mydsn;Username=user;Password=pwd'
*BUT* for DNS-less connections the *nix version of the driver actually uses
this connection string:
'driver=Teradata;UID=user;PWD=pwd'
Possibly they re-used the parsing logic from the Windows driver? At any
rate, thanks for the information. That gave me the information I needed to
figure out what was going on.
On Saturday, January 10, 2015 at 12:25:37 PM UTC-8, Lycovian wrote:
TL;DR:
I'm trying to debug what is actually being sent to the pyodbc.connect
function on connect in a custom dialect. I need to see the connection
string that is being sent to the pyodbc.connect function *right* before
it is sent but it has been difficult for me to unravel the layers of
indirection on the create_engine call.
Long version:
If you care for more information I have this DSN related connect string
for the custom dialect I am writing for Teradata:
engine =
sqlalchemy.create_engine(teradata://testsqlatd:password@td_testsqlatd,
encoding='utf-8', echo=True)
This connection string works and connects properly to my Teradata box
(yay!).
In my custom dialect I have subclassed so I can get some visibility into
the ODBC connect string SQLA is constructing:
def create_connect_args(self, url):
connector = super(TeradataDialect_pyodbc,
self).create_connect_args(url)
print connector
return connector
This code appears to call sqlalchemy/connectors/pyodbc.py
[create_connect_args]. And returns:
[['dsn=td_testsqlatd;UID=testsqlatd;PWD=password'], {}]
I assume that this string is roughly what is passed by SQLA to pyodbc at
some future point as the ODBC connection string. In my case the string
above connects successfully. Oddly enough though UID is not a valid ODBC
connection parameter for the Teradata ODBC driver. For Teradata the
parameter *must *be Username. Same with PWD, this isn't valid for the
Teradata ODBC driver. It should be Password.
According to my testing and the Teradata ODBC docs the valid version of
this ODBC connection string should be:
'dsn=td_testsqlatd;Username=testsqlatd;Password=password'
I have verified directly with pyodbc that the first form of the connect
string fails and the version directly above works, yet somehow in
SQLAlchemy it connects. Because of this I believe that SQLA is rewriting
the string further before connecting to the Teradata ODBC driver via
pyodbc. I can't find out where that is happening though. Because of
this I would like to intercept the pyodbc.connect call and see exactly
what ODBC connection string SQLA is invoking it with.
Any ideas how to log what exactly the connection string that SQLAlchemy is
sending to pyodbc.connect?
--
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] DefaultClause
Jose Soares jose.soa...@sferacarta.com wrote:
I see, thus, this definition:
Column('abc', Unicode(20), server_default='abc')
Column('adef', Numeric(12,3), server_default=text('1.5')),
is equivalent to this one:
Column('abc', Unicode(20), DefaultClause('abc'))
Column('def', Numeric(12,3), DefaultClause(text('1.5'))),
But what about ColumnDefault and default as explained at:
http://docs.sqlalchemy.org/en/rel_0_9/core/defaults.html#server-side-defaults
(Scalar Defaults and Default Objects API)
I tried both, but I can't understand how it works.
ColumnDefault is a python side default function.
If you stick with “default=X” and “server_default=Y” I think it’s less
confusing. Read the list of bullets at the top:
http://docs.sqlalchemy.org/en/rel_0_9/core/defaults.html# which enumerates
each type.
I tried this definition:
test = Table('test', database.metadata,
Column('def', Integer, ColumnDefault(12)),
Column('rbc', Integer, default=12),
)
which produce this table (with no defaults):
CREATE TABLE test (
def INTEGER,
rbc INTEGER
)
j
On 01/09/2015 04:55 PM, Michael Bayer wrote:
you can use DefaultClause if you give it a text() object so that the quoting
behavior is defined. server_default is a shortcut for that.
jo jose.soa...@sferacarta.com wrote:
Do you suggest to use server_default instead of DefaultClause in all cases?
j
Il giorno giovedì 8 gennaio 2015 15:17:20 UTC+1, Michael Bayer ha scritto:
use server_default with text() as documented at
http://docs.sqlalchemy.org/en/rel_0_9/core/defaults.html#server-side-defaults.
jo jose@sferacarta.com wrote:
Hi all,
I have a problem with DefautClause. I need to assign a default to a
numeric column.
tbl['rischio_attivita'] = Table('rischio_attivita', database.metadata,
Column('id', Integer, Sequence('rischio_attivita_seq'),
primary_key=True),
Column('cod_attivita', Unicode(10),nullable=False),
Column('cod_caratteristica_rischio', Unicode(10), nullable=False),
Column('tipo_calcolo', Integer),
Column('algoritmo', Unicode(200)),
Column('fattore_x', Numeric(4,2), DefaultClause(1.0)),
ForeignKeyConstraint(['cod_attivita'], ['attivita.codice']),
ForeignKeyConstraint(['cod_caratteristica_rischio'],
['caratteristica_rischio.codice'])
)
I tried to assign a float to it and I got this error:
File
/home/sfera/sicer2/buildout/eggs/SQLAlchemy-0.9.3-py2.6-linux-x86_64.egg/sqlalchemy/util/langhelpers.py,
line 999, in assert_arg_type
(name, ' or '.join('%s' % a for a in argtype), type(arg)))
sqlalchemy.exc.ArgumentError: Argument 'arg' is expected to be one of type
'type 'basestring'' or 'class 'sqlalchemy.sql.elements.ClauseElement''
or 'class 'sqlalchemy.sql.elements.TextClause'', got 'type 'float''
[sfera@tstsferagamma01 release]$ vi sicer/BASE/model/sql.py
then I changed it to a string as in:
Column('fattore_x', Numeric(4,2), DefaultClause('1.0')),
and now I have this:
sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-01722: invalid number
\nCREATE TABLE attivita (\n\tid INTEGER NOT NULL, \n\tcod_attivita
NVARCHAR2(10) NOT NULL, \n\tcod_caratteristica_rischio NVARCHAR2(10) NOT
NULL, \n\ttipo_calcolo INTEGER, \n\talgoritmo NVARCHAR2(200),
\n\tfattore_x NUMERIC(4, 2) DEFAULT '1.0', \n\tPRIMARY KEY (id),
\n\tFOREIGN KEY(cod_attivita) REFERENCES attivita (codice), \n\tFOREIGN
KEY(cod_caratteristica_rischio) REFERENCES caratteristica_rischio
(codice)\n)\n\n {}
Is there another way to define a default clause?
thanks.
j
--
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+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
--
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.
--
You received this message because you are subscribed to the Google
Re: [sqlalchemy] session query and column names
How could i guess the proper aliased column name then?
In your example your table is specie and column name is codice, so you
used specie_codice=42. but as i said i have joins and longer column
names, so sometimes i get like table1.somelongcolumn AS table1_somelong_7.
On Monday, January 12, 2015 at 3:09:22 PM UTC+3:30, jo wrote:
Why don't you pass the params to session.query as a dictionary into
filter_by as in:
In [1]: by_where_clause=dict(specie_codice='42',
specie_descrizione='Nutrie')
In [2]: print session.query(Specie).filter_by( **by_where_clause ).count()
2015-01-12 12:37:40,518 INFO sqlalchemy.engine.base.Engine SELECT count(*)
AS count_1
FROM (SELECT specie.codice AS specie_codice, specie.descrizione AS
specie_descrizione, specie.cod_gruppo_specie AS specie_cod_gruppo_specie,
specie.categoria_prodotto AS specie_categoria_prodotto, specie.interparto
AS specie_interparto, specie.gestazione AS specie_gestazione,
specie.codice_bdn AS specie_codice_bdn
FROM specie
WHERE specie.codice = %(codice_1)s AND specie.descrizione =
%(descrizione_1)s) AS anon_1
2015-01-12 12:37:40,518 INFO sqlalchemy.engine.base.Engine {'codice_1':
'42', 'descrizione_1': 'Nutrie'}
1
In [3]:
j
On 01/12/2015 11:45 AM, Mehdi wrote:
Hi
I want to make a query with given filters received from client side.
client side sends a json array like this:
[
0DA: {conditionType: null, column: serialNumber, operator:
GREATER_THAN, value: 50},
1DG: {conditionType: AND, column: name, operator: EQUAL, value
: john }
]
I've managed to convert the json array into string query with a template
like this:
{condition_type} {column} {operator} {value}
But this is only the WHERE clause. for complete query i use
session.query(myModel)
which myModel has two relationships with lazy=joined mix with text() for
filters:
session.query(myModel).filter(text(sql_string))
But the problem is the session.query() uses alias column names(with AS
keyword) which my filter part with exact column names couln'd find those
aliased columns.
So Is there a way to tell session.query() use the exact column names? or i
should change my way?
Thanks.
--
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+...@googlegroups.com javascript:.
To post to this group, send email to sqlal...@googlegroups.com
javascript:.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
