sql alchemy inconsistant error on program close

  • Last Update :
  • Techknowledgy :


import mysql as db

#set up connection to mysql
engine = db.create_engine(CORRECT CONNECTION INFO)
con = engine.connect()

Suggestion : 2

I am getting the following error when I try to perform a global search while using an oracle database. I'm not sure if this is an issue with sqlalchemy-datatables, sqlalchemy, or cx_Oracle.,I was able to get rid of the error by removing the .cast(Text) from the filter_for function in datatables.py. This change will break searches for a postgres database.,This query fails with the same error in sqlplus. I can fix it there manually by changing lower(CAST(users.fname AS CLOB)) LIKE lower(:param_1) to lower(to_char(users.fname)) LIKE lower(:param_1), Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

DataTables warning: table id = userTable - (cx_Oracle.DatabaseError) ORA - 00932: inconsistent datatypes: expected - got CLOB[SQL: SELECT count( * ) AS count_1 FROM(SELECT users.fname AS users_fname FROM users WHERE lower(CAST(users.fname AS CLOB)) LIKE lower(: param_1)) anon_1]
   [parameters: {
      'param_1': '%a%'

Suggestion : 3

A common reason this can occur is that the application uses ORM sessions and does not call Session.close() upon them one the work involving that session is complete. Solution is to make sure ORM sessions if using the ORM, or engine-bound Connection objects if using Core, are explicitly closed at the end of the work being done, either via the appropriate .close() method, or by using one of the available context managers (e.g. “with:” statement) to properly release the resource.,To pre-buffer results in the above situation using the regular Session in the same way that the asyncio extension does it, the prebuffer_rows execution option may be used as follows:,The Engine must instead be associated directly with the sessionmaker or Session. The MetaData object should no longer be associated with any engine:,This error is emitted when the relationship.single_parent flag is used, and more than one object is assigned as the “parent” of an object at once.

engine = create_engine("mysql://u:p@host/db", pool_size = 10, max_overflow = 20)
>>> from sqlalchemy
import column
   print(column('x') == 5)
x =: x_1
>>> from sqlalchemy.dialects.postgresql import insert
>>> from sqlalchemy import table, column
>>> my_table = table('my_table', column('x'), column('y'))
>>> insert_stmt = insert(my_table).values(x='foo')
>>> insert_stmt = insert_stmt.on_conflict_do_nothing(
... index_elements=['y']
... )
>>> print(insert_stmt)
Traceback (most recent call last):


Compiler <sqlalchemy.sql.compiler.StrSQLCompiler object at 0x7f04fc17e320>
   can't render element of type
   <class 'sqlalchemy.dialects.postgresql.dml.OnConflictDoNothing'>
>>> from sqlalchemy.dialects
import postgresql
   print(insert_stmt.compile(dialect = postgresql.dialect()))
statement = query.statement
print(statement.compile(dialect = postgresql.dialect()))
class Bar(Base):
   __tablename__ = 'bar'

id = Column(Integer, primary_key = True)
cprop = deferred(Column(Integer))

__table_args__ = (
   CheckConstraint(cprop > 5),

Suggestion : 4

If you plan to use explicit transactions, you must disable the AUTOCOMMIT execution option in SQLAlchemy.,By default, SQLAlchemy enables this option. When this option is enabled, INSERT, UPDATE, and DELETE statements are committed automatically upon execution, even when these statements are run within an explicit transaction.,As much as possible, Snowflake SQLAlchemy provides compatible functionality for SQLAlchemy applications. For information on using SQLAlchemy, see the SQLAlchemy documentation.,An easier way to build an environment is through Anaconda, which provides a complete, precompiled technology stack for all users, including non-Python experts such as data analysts and students. For Anaconda installation instructions, see the Anaconda install documentation. The Snowflake SQLAlchemy package can then be installed on top of Anaconda using pip.

pip install--upgrade snowflake - sqlalchemy
#!/usr/bin/env python
from sqlalchemy import create_engine

engine = create_engine(
    connection = engine.connect()
    results = connection.execute('select current_version()').fetchone()
python validate.py
from sqlalchemy
import create_engine
engine = create_engine(

Suggestion : 5

#2 David S said 2020-11-24T15:07:14Z , #7 Sara said 2021-02-27T20:41:27Z , #1 Matt said 2020-08-12T20:33:36Z

You can test this yourself very easily. Take any Flask-SQLAlchemy application (you can use one of mine) and after making sure your database is up to date, remove or comment out a column in one of the models. Then generate a migration:

(venv) $ flask db migrate - m "remove a column"

If you open the generated migration script everything will look correct. Below you can see the migration that was generated after I removed a column named about_me from the User model:

"remove a column

Revision ID: ec813e760b53
Revises: 834 b1a697901
Create Date: 2020 - 07 - 19 18: 18: 44.066766

from alembic
import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = 'ec813e760b53'
down_revision = '834b1a697901'
branch_labels = None
depends_on = None

def upgrade():
   # # # # commands auto generated by Alembic - please adjust!# # #
op.drop_column('user', 'about_me')
# # # # end Alembic commands # # #

def downgrade():
   # # # # commands auto generated by Alembic - please adjust!# # #
op.add_column('user', sa.Column('about_me', sa.VARCHAR(length = 140), nullable = True))
# # # # end Alembic commands # # #

The problem occurs when you try to upgrade the database with this migration:

(venv) $ flask db upgrade
   [2020 - 07 - 19 18: 21: 14, 268] INFO in __init__: Microblog startup
INFO[alembic.runtime.migration] Context impl SQLiteImpl.
INFO[alembic.runtime.migration] Will assume non - transactional DDL.
INFO[alembic.runtime.migration] Running upgrade 834 b1a697901 - > ec813e760b53, remove a column
Traceback(most recent call last):
   File "/Users/mgrinberg/Documents/dev/python/microblog/venv/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1283, in _execute_context
      File "/Users/mgrinberg/Documents/dev/python/microblog/venv/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute cursor.execute(statement, parameters) sqlite3.OperationalError: near "DROP": syntax error

      The above exception was the direct cause of the following exception:

      Traceback(most recent call last): [...removed a long list of uninteresting stack frames...] sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) near "DROP": syntax error[SQL: ALTER TABLE user DROP COLUMN about_me]
      (Background on this error at: http: //sqlalche.me/e/e3q8)

If you use the two-step initialization:

migrate = Migrate()

def create_app():
   migrate.init_app(app, db, render_as_batch = True)

Now that you have batch mode enabled, try to generate the migration again:

(venv) $ flask db migrate - m "remove a column"
Thanks Miguel!And that Flask - Migrate batch configuration was the option all the time ? Hours of rewriting migrate scripts with SQLite db...
Thank you
for this excellent piece of information!I had been struggling with this issue in the back of my mind off and on
for awhile in a "for fun"
project I 've been working on while learning Flask.  I was glad to find your post because it directly addressed my issue and provided a solution!
Thank you so much
for this!Such a simple fix: )
This worked really well.Is there a reason to revert back to non - batch mode ? Or is this the ideal
for any migration ?
Fantastic!Thank you!

Suggestion : 6

Errors Syntax errors Runtime errors Logical errors Exercise 1 ,Here are some examples of syntax errors in Python:,There are five syntax errors:,Find all the syntax errors in the code snippet above, and explain why they are errors.

myfunction(x, y):
   return x + y


if mark >= 50
print("You passed!")

if arriving:

if flag:
   print("Flag is set!")
dividend = float(input("Please enter the dividend: "))
divisor = float(input("Please enter the divisor: "))
quotient = dividend / divisor
quotient_rounded = math.round(quotient)
for x in range(a, b):
   print("(%f, %f, %f)" % my_list[x])
product = 0
for i in range(10):
   product *= i

sum_squares = 0
for i in range(10):
   i_sq = i ** 2
sum_squares += i_sq

nums = 0
for num in range(10):
   num += num
age = int(input("Please enter your age: "))
print("I see that you are %d years old." % age)
except ValueError:
   print("Hey, that wasn't a number!")
dividend = int(input("Please enter the dividend: "))
divisor = int(input("Please enter the divisor: "))
print("%d / %d = %f" % (dividend, divisor, dividend / divisor))
except(ValueError, ZeroDivisionError):
   print("Oops, something went wrong!")

Suggestion : 7

The most common reason of an error in a Python program is when a certain statement is not in accordance with the prescribed usage. Such an error is called a syntax error. The Python interpreter immediately reports it, usually along with the reason. , Many times though, a program results in an error after it is run even if it doesn't have any syntax error. Such an error is a runtime error, called an exception. A number of built-in exceptions are defined in the Python library. Let's see some common error types. , In Python 3.x, print is a built-in function and requires parentheses. The statement above violates this usage and hence syntax error is displayed. , The ZeroDivisionError is thrown when the second operator in the division is zero.

>>> print "hello"
SyntaxError: Missing parentheses in call to 'print'.Did you mean print("hello") ?
>>> L1=[1,2,3]
>>> L1[3]
Traceback (most recent call last):
File "<pyshell#18>", line 1, in <module>

      IndexError: list index out of range
>>> import notamodule
Traceback (most recent call last):
File "<pyshell#10>", line 1, in <module>

      import notamodule
      ModuleNotFoundError: No module named 'notamodule'
>>> D1={'1':"aa", '2':"bb", '3':"cc"}
>>> D1['4']
Traceback (most recent call last):
File "<pyshell#15>", line 1, in <module>

KeyError: '4'
>>> from math import cube
Traceback (most recent call last):
File "<pyshell#16>", line 1, in <module>

      from math import cube
      ImportError: cannot import name 'cube'
>>> it=iter([1,2,3])
>>> next(it)
>>> next(it)
>>> next(it)
>>> next(it)
Traceback (most recent call last):
File "<pyshell#23>", line 1, in <module>


Suggestion : 8

Sometimes, you might see a traceback that is very long – sometimes they might even be 20 levels deep! This can make it seem like something horrible happened, but really it just means that your program called many functions before it ran into the error. Most of the time, you can just pay attention to the bottom-most level, which is the actual place where the error occurred.,Containers like lists and strings will generate errors if you try to access items in them that do not exist. This type of error is called an IndexError.,Containers like lists and strings will generate errors if you try to access items in them that do not exist. This type of error is called an IndexError. ,People can typically figure out what is meant by text with no punctuation, but people are much smarter than computers. If Python doesn’t know how to read the program, it will just give up and inform you with an error. For example:

# This code has an intentional error.You can type it directly or
# use it
for reference to understand the error message below.
def favorite_ice_cream():
   ice_creams = [

IndexError Traceback (most recent call last)
<ipython-input-1-70bd89baa4df> in <module>()
      6 print(ice_creams[3])
      ----> 8 favorite_ice_cream()

      <ipython-input-1-70bd89baa4df> in favorite_ice_cream()
         4 "vanilla", "strawberry"
         5 ]
         ----> 6 print(ice_creams[3])
         8 favorite_ice_cream()

         IndexError: list index out of range
def some_function()
msg = "hello, world!"
return msg
  File "<ipython-input-3-6bb841ea1423>", line 1
     def some_function()
     SyntaxError: invalid syntax
def some_function():
   msg = "hello, world!"
return msg
  File "<ipython-input-4-ae290e7659cb>", line 4
     return msg
     IndentationError: unexpected indent