Re: [sqlalchemy] returned_defaults fails after upgrading from 0.9.4

[Mike Bayer]

this is likely
http://docs.sqlalchemy.org/en/rel_1_0/changelog/migration_10.html#the-insert-from-select-construct-now-implies-inline-true
- use explicit returning().  returned_defaults implies only a single row
INSERT which is not the case for INSERT from SELECT.




On 11/01/2015 01:29 AM, gbr wrote:
> I've just tried to upgrade from 0.9.4 to the latest 1.0.9 version (yes,
> I'm late) and am running into some problems. Most notably,
> `returned_defaults` from an insert query does not seem to work as
> before. This is part of my code:
> 
> new_record = select([user_id, parent_id, 1 + func.max(some_table.c.id)])
> qry = insert(revision_table).from_select(
> ['user_id', 'parent_id', 'id'],
> new_record
> ).return_defaults()
> result = session.execute(qry)
> new_id = result.returned_defaults['id']
>
>   File "/python2.7/site-packages/sqlalchemy/engine/result.py", line 822,
> in returned_defaults
> return self.context.returned_defaults
> AttributeError: 'PGExecutionContext_psycopg2' object has no attribute
> 'returned_defaults'
> 
> I've also upgraded psycopg2 to the latest, but the issue remains.
> `session` is a sqlalchemy.orm.scoping.scoped_session object.
> 
> Hope this information is enough and someone can help.
> 
> 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.

-- 
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] Re: Temporarily drop and re-create constraint

[Mike Bayer]

On 10/31/15 5:48 PM, r...@rosenfeld.to wrote:
> On Friday, October 30, 2015 at 6:02:47 PM UTC-5, r...@rosenfeld.to wrote:
> 
> I would like to temporarily drop a foreign key constraint while
> loading data and then revert the constraint's removal when done.  
> I'm hoping to do this without needing any specific knowledge of the
> constraints definition at the time I re-create it.   By that I mean
> something similar to this fantasy example that's missing a couple
> key imaginary functions.
> 
> |
> inspector =sqlalchemy.engine.reflection.Inspector.from_engine(engine)
> foreign_keys =inspector.get_foreign_keys(table_name)
> forforeign_key inforeign_keys:
>ifforeign_key['name']==key_name_to_drop:
>foreign_key_data_to_recreate =foreign_key
>  
>  
> sqlalchemy.schema.DropConstraint(SOME_FUNCTION(foreign_key_data_to_recreate))
> 
> # Load the data
> 
> 
> sqlalchemy.schema.AddConstraint(SOME_OTHER_FUNCTION(foreign_key_data_to_recreate))
> |
> 
> The above is just one way I imagine it could work.  But maybe
> instead of `get_foreign_keys` and grabbing the data, I can directly
> grab the ForeignKey object of interest to delete from the table and
> add back later.
> 
> My goal is to not need to re-code the details of the foreign key at
> the time I need to reapply it to the table.
> 
> 
> Thanks much,
> Rob
> 
> 
> 
> I've been working on this some more and have figured out something that
> works.   I'm guessing it could be generalized to work with any
> constraint, but won't work on that until I need it.  The metadata I'm
> using is the one I pass to declarative_base, but I'm not sure it matters.
> 
> |
> from sqlalchemy.schema import DropConstraint
> from sqlalchemy.schema import AddConstraint
> from sqlalchemy import Table
> 
> 
> class WorkWithoutForeignKey(object):
> def __init__(self, engine, table_name, foreign_key_name):
> table = Table(table_name, metadata, autoload=True,
> autoload_with=engine)
> for column in table.columns:
> foreign_keys = column.foreign_keys
> for foreign_key in foreign_keys:
> if foreign_key.constraint.name == foreign_key_name:
> self.foreign_key_constraint = foreign_key.constraint
> 
> self.connection = engine.connect()
> 
> def __enter__(self):
> self.connection.execute(DropConstraint(self.foreign_key_constraint))
> 
> def __exit__(self, exc_type, exc_val, exc_tb):
> self.connection.execute(AddConstraint(self.foreign_key_constraint))
> 
> 
> with WorkWithoutForeignKey(engine, 'my_table',
> 'fk_schema_table_column_foreigncolumn'):
> # Load the data

that approach is fine, you can use reflection and/or the Inspector to
get at those foreign key constraints also automatically if you weren't
defining them explicitly.



> |
> 
> 
> 
> 
> 
>  
> 
> -- 
> 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] returned_defaults fails after upgrading from 0.9.4

[gbr]

I've just tried to upgrade from 0.9.4 to the latest 1.0.9 version (yes, I'm 
late) and am running into some problems. Most notably, `returned_defaults` 
from an insert query does not seem to work as before. This is part of my 
code:

new_record = select([user_id, parent_id, 1 + func.max(some_table.c.id)])
qry = insert(revision_table).from_select(
['user_id', 'parent_id', 'id'],
new_record
).return_defaults()
result = session.execute(qry)
new_id = result.returned_defaults['id']

  File "/python2.7/site-packages/sqlalchemy/engine/result.py", line 822, in 
returned_defaults
return self.context.returned_defaults
AttributeError: 'PGExecutionContext_psycopg2' object has no attribute 
'returned_defaults'

I've also upgraded psycopg2 to the latest, but the issue remains. `session` 
is a sqlalchemy.orm.scoping.scoped_session object.

Hope this information is enough and someone can help.

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.

[sqlalchemy] sqlalchemy.exc.operationalerror: (cx_oracle.operationalerror) ora-03114: not connected to oracle

[Nana Okyere]

New to python, web development and hence flask. I have a small application 
that accepts excel inputs, extracts the data and writes it out oracle. My 
stack includes flask-sqlalchemy, cx_oracle. When I upload a small excel 
file with about 10 K rows, it works just fine. When I throw really large 
files at it up to a million rows, it craps out and I get :

sqlalchemy.exc.operationalerror: (cx_oracle.operationalerror) ora-03114: not 
connected to oracle

My view function:

@theapp.route('/upload', methods=('GET', 'POST'))
def upload():
start_time = time.time()
form = UploadForm()
if form.validate_on_submit():
file_name = secure_filename(form.upload.data.filename)
form.upload.data.save(os.path.join(theapp.config['UPLOAD_FOLDER'], 
file_name))
input_file = os.path.join(theapp.config['UPLOAD_FOLDER'], file_name)
wb = load_workbook(input_file, data_only = True, read_only=True, 
use_iterators=True)
sheet = wb.worksheets[0]
allrows = sheet.iter_rows() #this is a generator
headerobject = next(allrows) # row object for headerobject

# Column names from excel file
headerlist = []
for c in headerobject:
if c.value is not None: # If user uses delete button to delete 
contents of header cell, ignore it
if isinstance(c.value, str) is False: # if not a string
return bad_request("Column names have to be strings 
without spaces. Value in cell {} is not a string".format(c.coordinate))
if not c.value.strip(): # Empty string in header
return bad_request("Cell {} has line space(s). Delete 
the contents of the cell".format(c.coordinate))
headerlist.append(c.value.strip())
#print("Excel {}".format(headerlist))

duplicate_elements = [k for k,v in Counter(headerlist).items() if v 
>1]
if duplicate_elements: # A falsy. If there are duplicate elements
return bad_request("Column(s) {} are 
duplicates".format(duplicate_elements))

tableinfo = form.targettableinfo.data # tableinfo object in the 
databse order
tablecolumns = []
for col in tableinfo.fields:
tablecolumns.append(col.name)
#print("Table columns {}".format(tablecolumns))

# Check if the columns match
if not set(headerlist) == set(tablecolumns):
foreign_columns = [x for x in headerlist if x not in 
tablecolumns]
if foreign_columns:
return bad_request("Column(s) {} in your excel file are not 
defined".format(foreign_columns))
unfulfilled_columns = [x for x in tablecolumns if x not in 
headerlist]
if unfulfilled_columns:
return bad_request("Column(s) {} are expected by the table 
but not found in your excel file.".format(unfulfilled_columns))

# Sort the tablecolumns object in the order of the headerlist
sortedtablecolumnsobject = sorted(tableinfo.fields, key=lambda x: 
headerlist.index(x.name))

# create a list of dicts to hold data to be written
output_data = []
for row in allrows:
row_dict = {}
for fobject,cobject in zip(sortedtablecolumnsobject,row):
##
##Validation & assignment
##
if fobject.type.name == "Text":
if cobject.value == None:
row_dict[fobject.name] = cobject.value
else:
row_dict[fobject.name] = 
removeNonAscii(str(cobject.value))
#row_dict[fobject.name] = 
cobject.value.encode('utf-8')
#print(str(cobject.value))
elif fobject.type.name == "Number":
if dao.isnumber(cobject.value):
row_dict[fobject.name] = float(cobject.value)
#print(float(cobject.value))
elif cobject.value is None:
row_dict[fobject.name] = cobject.value
#print(cobject.value)
else:
return bad_request("Cell {} has value {}. A numeric 
value is expected".format(cobject.coordinate, cobject.value))
elif fobject.type.name == "Date":
if type(cobject.value) == datetime.datetime:
row_dict[fobject.name] = cobject.value
elif cobject.value is None:
row_dict[fobject.name] = cobject.value
else:
return bad_request("Cell {} does not contain a 
valid date".format(cobject.coordinate))
##

output_data.append(row_dict)

#print(output_data)
m = db.MetaData()
t = 

[sqlalchemy] Re: Temporarily drop and re-create constraint

[rob]

On Friday, October 30, 2015 at 6:02:47 PM UTC-5, r...@rosenfeld.to wrote:
>
> I would like to temporarily drop a foreign key constraint while loading 
> data and then revert the constraint's removal when done.   I'm hoping to do 
> this without needing any specific knowledge of the constraints definition 
> at the time I re-create it.   By that I mean something similar to this 
> fantasy example that's missing a couple key imaginary functions.
>
> inspector = sqlalchemy.engine.reflection.Inspector.from_engine(engine)
> foreign_keys = inspector.get_foreign_keys(table_name)
> for foreign_key in foreign_keys:
>if foreign_key['name'] == key_name_to_drop:
>foreign_key_data_to_recreate = foreign_key
>sqlalchemy.schema.DropConstraint(SOME_FUNCTION(
> foreign_key_data_to_recreate))
>
> # Load the data
>
> sqlalchemy.schema.AddConstraint(SOME_OTHER_FUNCTION(
> foreign_key_data_to_recreate))
>
> The above is just one way I imagine it could work.  But maybe instead of 
> `get_foreign_keys` and grabbing the data, I can directly grab the 
> ForeignKey object of interest to delete from the table and add back later.
>
> My goal is to not need to re-code the details of the foreign key at the 
> time I need to reapply it to the table.
>
>
> Thanks much,
> Rob
>


I've been working on this some more and have figured out something that 
works.   I'm guessing it could be generalized to work with any constraint, 
but won't work on that until I need it.  The metadata I'm using is the one 
I pass to declarative_base, but I'm not sure it matters.

from sqlalchemy.schema import DropConstraint
from sqlalchemy.schema import AddConstraint
from sqlalchemy import Table


class WorkWithoutForeignKey(object):
def __init__(self, engine, table_name, foreign_key_name):
table = Table(table_name, metadata, autoload=True, 
autoload_with=engine)
for column in table.columns:
foreign_keys = column.foreign_keys
for foreign_key in foreign_keys:
if foreign_key.constraint.name == foreign_key_name:
self.foreign_key_constraint = foreign_key.constraint

self.connection = engine.connect()

def __enter__(self):
self.connection.execute(DropConstraint(self.foreign_key_constraint))

def __exit__(self, exc_type, exc_val, exc_tb):
self.connection.execute(AddConstraint(self.foreign_key_constraint))


with WorkWithoutForeignKey(engine, 'my_table', 
'fk_schema_table_column_foreigncolumn'):
# Load the data





 

-- 
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] sqlalchemy.exc.operationalerror: (cx_oracle.operationalerror) ora-03114: not connected to oracle

[Mike Bayer]

On 10/31/15 2:44 AM, Nana Okyere wrote:
> New to python, web development and hence flask. I have a small
> application that accepts excel inputs, extracts the data and writes it
> out oracle. My stack includes flask-sqlalchemy, cx_oracle. When I upload
> a small excel file with about 10 K rows, it works just fine. When I
> throw really large files at it up to a million rows, it craps out and I
> get :
> 
> |
> sqlalchemy.exc.operationalerror:(cx_oracle.operationalerror)ora-03114:notconnected
> to oracle

SQLAlchemy has no timeout feature, this would be cx_oracle itself
dropping the connection.

the code here doesn't show the kind of table we're dealing with, if it
uses large objects (e.g. CLOB, BLOB) that could contribute to an issue
like this, if the value being sent is too large for memory. Can't
make any determination from the information that's here.





> |
> 
> My view function:
> 
> |
> @theapp.route('/upload', methods=('GET', 'POST'))
> def upload():
> start_time = time.time()
> form = UploadForm()
> if form.validate_on_submit():
> file_name = secure_filename(form.upload.data.filename)
>
> form.upload.data.save(os.path.join(theapp.config['UPLOAD_FOLDER'],
> file_name))
> input_file = os.path.join(theapp.config['UPLOAD_FOLDER'], file_name)
> wb = load_workbook(input_file, data_only = True, read_only=True,
> use_iterators=True)
> sheet = wb.worksheets[0]
> allrows = sheet.iter_rows() #this is a generator
> headerobject = next(allrows) # row object for headerobject
> 
> # Column names from excel file
> headerlist = []
> for c in headerobject:
> if c.value is not None: # If user uses delete button to
> delete contents of header cell, ignore it
> if isinstance(c.value, str) is False: # if not a string
> return bad_request("Column names have to be strings
> without spaces. Value in cell {} is not a string".format(c.coordinate))
> if not c.value.strip(): # Empty string in header
> return bad_request("Cell {} has line space(s).
> Delete the contents of the cell".format(c.coordinate))
> headerlist.append(c.value.strip())
> #print("Excel {}".format(headerlist))
> 
> duplicate_elements = [k for k,v in Counter(headerlist).items()
> if v >1]
> if duplicate_elements: # A falsy. If there are duplicate elements
> return bad_request("Column(s) {} are
> duplicates".format(duplicate_elements))
> 
> tableinfo = form.targettableinfo.data # tableinfo object in the
> databse order
> tablecolumns = []
> for col in tableinfo.fields:
> tablecolumns.append(col.name)
> #print("Table columns {}".format(tablecolumns))
> 
> # Check if the columns match
> if not set(headerlist) == set(tablecolumns):
> foreign_columns = [x for x in headerlist if x not in
> tablecolumns]
> if foreign_columns:
> return bad_request("Column(s) {} in your excel file are
> not defined".format(foreign_columns))
> unfulfilled_columns = [x for x in tablecolumns if x not in
> headerlist]
> if unfulfilled_columns:
> return bad_request("Column(s) {} are expected by the
> table but not found in your excel file.".format(unfulfilled_columns))
> 
> # Sort the tablecolumns object in the order of the headerlist
> sortedtablecolumnsobject = sorted(tableinfo.fields, key=lambda
> x: headerlist.index(x.name))
> 
> # create a list of dicts to hold data to be written
> output_data = []
> for row in allrows:
> row_dict = {}
> for fobject,cobject in zip(sortedtablecolumnsobject,row):
> ##
> ##Validation & assignment
> ##
> if fobject.type.name == "Text":
> if cobject.value == None:
> row_dict[fobject.name] = cobject.value
> else:
> row_dict[fobject.name] =
> removeNonAscii(str(cobject.value))
> #row_dict[fobject.name] =
> cobject.value.encode('utf-8')
> #print(str(cobject.value))
> elif fobject.type.name == "Number":
> if dao.isnumber(cobject.value):
> row_dict[fobject.name] = float(cobject.value)
> #print(float(cobject.value))
> elif cobject.value is None:
> row_dict[fobject.name] = cobject.value
> #print(cobject.value)
> else:
> return bad_request("Cell {} has value {}. A
> numeric value is expected".format(cobject.coordinate, cobject.value))
> elif fobject.type.name ==