merge raw sql statement is not working is sqlalchemy connection execute

  • Last Update :
  • Techknowledgy :

Recently ran into this issue, apparently certain statements like merge need to be treated as committal. Please see this for more details.

with engine.begin() as conn:
   conn.execute(statement1)
conn.execute(statement2)
#...and so on

Be sure you use simple test data, that can be easy validated such as follows:

create table test1 as
select 1 id from dual;

create table test2 as
select 1 id from dual union all
select 2 id from dual
;

This snipped returns 1 row merged (here inserted as you do not update) as expected:

pop_stmt = ""
"merge into test1 a
using(select id from test2) b
on(a.id = b.id)
when not matched then
insert(a.id) values(b.id)
""
"

with engine.connect() as con:
   rs = con.execute(text(pop_stmt).execution_options(autocommit = True))
print(rs.rowcount)

1

Suggestion : 2

Recently ran into this issue, apparently anycodings_sql certain statements like merge need to be anycodings_sql treated as committal. Please see this anycodings_sql for more details.,Here we are not getting any error, the anycodings_oracle execution of the statement shows successful anycodings_oracle from python but data was not inserted in the anycodings_oracle table in the DB,This snipped returns 1 row merged (here anycodings_sql inserted as you do not update) as anycodings_sql expected:,This statement works perfectly for my anycodings_sql using sqlalchemy.__version__ '1.3.10' anycodings_sql and Oracle 12.2.0.1.0

When we are executing raw merge sql anycodings_oracle statement through python using sqlalchemy it anycodings_oracle is not adding the expected rows and anycodings_oracle execution showing as successful from python anycodings_oracle but when the query was executed in the db anycodings_oracle the table is populated with required data. anycodings_oracle For the same below code if i use any update anycodings_oracle or select statement from python it was anycodings_oracle successfully giving the expected output

from sqlalchemy.sql
import text
from sqlalchemy
import create_engine, types
oracle_connection_string = ('oracle+cx_oracle://{username}:{password}@' +
   cx_Oracle.makedsn('{hostname}', '{port}', service_name = '{service_name}'))

engine = create_engine(oracle_connection_string.format(
   username = 'test',
   password = 'test',
   hostname = 'test.com',
   port = '1521',
   service_name = 'test.net'
))
pop_stmt = ""
"merge into test1 a
using(select id from test2) b
on(a.id = b.id)
when not matched then
insert(a.id) values(b.id)
""
"

with engine.connect() as con:
   con.execute(text(pop_stmt).execution_options(autocommit = True))

Recently ran into this issue, apparently anycodings_sql certain statements like merge need to be anycodings_sql treated as committal. Please see this anycodings_sql for more details.

with engine.begin() as conn:
   conn.execute(statement1)
conn.execute(statement2)
#...and so on

Be sure you use simple test data, that anycodings_sql can be easy validated such as follows:

create table test1 as
select 1 id from dual;

create table test2 as
select 1 id from dual union all
select 2 id from dual
;

This snipped returns 1 row merged (here anycodings_sql inserted as you do not update) as anycodings_sql expected:

pop_stmt = ""
"merge into test1 a
using(select id from test2) b
on(a.id = b.id)
when not matched then
insert(a.id) values(b.id)
""
"

with engine.connect() as con:
   rs = con.execute(text(pop_stmt).execution_options(autocommit = True))
print(rs.rowcount)

1

Suggestion : 3

We’ll briefly explore how to use SQLAlchemy and then dive deeper into how to execute raw SQL statements from within the comfort of the Python domain language.,There we have it! Simple and effective methods for executing raw SQL statements in SQLAlchemy.,The alternative method is to skip using text() and pass a raw SQL string to the .execute() method. For example, here we’ll use .execute() to view the new records we inserted above:,SQLAlchemy is a SQL tool built with Python that provides developers with an abundance of powerful features for designing and managing high-performance databases.

import sqlalchemy
from sqlalchemy
import create_engine
from sqlalchemy
import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy
import inspect
print sqlalchemy.__version__
Out[ * ]: 1.0 .9
metadata = MetaData()
books = Table('book', metadata,
   Column('id', Integer, primary_key = True),
   Column('title', String),
   Column('primary_author', String),
)

engine = create_engine('sqlite:///bookstore.db')
metadata.create_all(engine)
engine = create_engine('sqlite:///bookstore.db')
engine = create_engine('postgresql://user:password@host/database')
inspector = inspect(engine)
inspector.get_columns('book')
Out[ * ]: [{
      'autoincrement': True,
      'default': None,
      'name': u 'id',
      'nullable': False,
      'primary_key': 1,
      'type': INTEGER()
   },
   {
      'autoincrement': True,
      'default': None,
      'name': u 'title',
      'nullable': True,
      'primary_key': 0,
      'type': VARCHAR()
   },
   {
      'autoincrement': True,
      'default': None,
      'name': u 'primary_author',
      'nullable': True,
      'primary_key': 0,
      'type': VARCHAR()
   }
]

Suggestion : 4

Explicit execution delivers the SQL text or constructed SQL expression to the Connection.execute() method of Connection:,Explicit, connectionless execution delivers the expression to the Engine.execute() method of Engine:,The Connection object is procured by calling the Engine.connect() method of the Engine object, and provides services for execution of SQL statements as well as transaction control.,Deprecated since version 2.0: passing a string to Connection.execute() is deprecated and will be removed in version 2.0. Use the text() construct with Connection.execute(), or the Connection.exec_driver_sql() method to invoke a driver-level SQL string.

engine = create_engine('mysql://scott:tiger@localhost/test')
from sqlalchemy
import text

with engine.connect() as connection:
   result = connection.execute(text("select username from users"))
for row in result:
   print("username:", row['username'])
with engine.connect() as connection:
   with connection.begin():
   r1 = connection.execute(table1.select())
connection.execute(table1.insert(), {
   "col1": 7,
   "col2": "this is some data"
})
# runs a transaction
with engine.begin() as connection:
   r1 = connection.execute(table1.select())
connection.execute(table1.insert(), {
   "col1": 7,
   "col2": "this is some data"
})
# method_a starts a transaction and calls method_b
def method_a(connection):
   with connection.begin(): # open a transaction
method_b(connection)

# method_b also starts a transaction
def method_b(connection):
   with connection.begin(): # open a transaction - this runs in the
# context of method_a 's transaction
connection.execute(text("insert into mytable values ('bat', 'lala')"))
connection.execute(mytable.insert(), {
   "col1": "bat",
   "col2": "lala"
})

# open a Connection and call method_a
with engine.connect() as conn:
   method_a(conn)
# method_a calls method_b
def method_a(connection):
   method_b(connection)

# method_b uses the connection and assumes the transaction
# is external
def method_b(connection):
   connection.execute(text("insert into mytable values ('bat', 'lala')"))
connection.execute(mytable.insert(), {
   "col1": "bat",
   "col2": "lala"
})

# open a Connection inside of a transaction and call method_a
with engine.begin() as conn:
   method_a(conn)

Suggestion : 5

Executes a SQL statement construct,We can now execute this SQL query with the execute() method of connection object. The complete code is as follows −,SQL expressions are constructed using corresponding methods relative to target table object. For example, the INSERT statement is created by executing insert() method as follows −,The update() method on target table object constructs equivalent UPDATE SQL expression.

Any version of Python higher than 2.7 is necessary to install SQLAlchemy. The easiest way to install is by using Python Package Manager, pip. This utility is bundled with standard distribution of Python.

pip install sqlalchemy

In case of anaconda distribution of Python, SQLAlchemy can be installed from conda terminal using the below command −

conda install - c anaconda sqlalchemy

It is also possible to install SQLAlchemy from below source code −

python setup.py install

The create_engine() function takes the database as one argument. The database is not needed to be defined anywhere. The standard calling form has to send the URL as the first positional argument, usually a string that indicates database dialect and connection arguments. Using the code given below, we can create a database.

>>> from sqlalchemy
import create_engine
   >>>
   engine = create_engine('sqlite:///college.db', echo = True)

For a MySQL database, use the below command −

engine = create_engine("mysql://user:pwd@localhost/college", echo = True)