##########################################################################
# Connectionless query
##########################################################################

# String concatenation using + operator
engine = create_engine('postgresql://user@localhost/database')
echo("database connexion: ok")
# ruleid: sqlalchemy-execute-raw-query
engine.execute("INSERT INTO person (name) VALUES ('" + name + "')")
# ruleid: sqlalchemy-execute-raw-query
engine.execute("INSERT INTO person (name) VALUES ('" + name + "')", multi=False)

# String concatenation using + operator
engine = create_engine('postgresql://user@localhost/database')
# ruleid: sqlalchemy-execute-raw-query
engine.execute("INSERT INTO person (firstname, lastname) VALUES ('" + firstname + "','" + lastname + "')")

# String formating using % operator (old style)
engine = create_engine('postgresql://user@localhost/database')
# ruleid: sqlalchemy-execute-raw-query
engine.execute("INSERT INTO person (name) VALUES ('%s')" % (name))

# String formating (new style)
engine = create_engine('postgresql://user@localhost/database')
# ruleid: sqlalchemy-execute-raw-query
engine.execute("INSERT INTO person (name) VALUES ('{}')".format(name))

# String concatenation using fstrings
engine = create_engine('postgresql://user@localhost/database')
# ruleid: sqlalchemy-execute-raw-query
engine.execute(f"INSERT INTO person (name) VALUES ('{name}')")

# String concatenation using + operator
engine = create_engine('postgresql://user@localhost/database')
query = "INSERT INTO person (name) VALUES ('" + name + "')"
# ruleid: sqlalchemy-execute-raw-query
engine.execute(query)

# String formating using % operator (old style)
engine = create_engine('postgresql://user@localhost/database')
query = "INSERT INTO person (name) VALUES ('%s')" % (name)
# ruleid: sqlalchemy-execute-raw-query
engine.execute(query)

# String formating (new style)
engine = create_engine('postgresql://user@localhost/database')

query = "INSERT INTO person (name) VALUES ('{}')".format(name)
# ruleid: sqlalchemy-execute-raw-query
engine.execute(query)

# String formating using fstrings
engine = create_engine('postgresql://user@localhost/database')

query = f"INSERT INTO person (name) VALUES ('{name}')"
# ruleid: sqlalchemy-execute-raw-query
engine.execute(query)

# fstrings
engine = create_engine('postgresql://user@localhost/database')
query: str = f"INSERT INTO person (name) VALUES ('{name}')"
# ruleid: sqlalchemy-execute-raw-query
engine.execute(query)

# Query without concatenation
# ok: sqlalchemy-execute-raw-query
engine = create_engine('postgresql://user@localhost/database')
engine.execute("INSERT INTO person (name) VALUES ('Frodon Sacquet')")

##########################################################################
# Execute query without "With" block
##########################################################################

# Execute query from string concatenation using + operator
engine = create_engine('postgresql://user@localhost/database')
connection = engine.connect()
# ruleid: sqlalchemy-execute-raw-query
connection.execute("INSERT INTO person (name) VALUES ('" + name + "')")

# Execute query from String formating using % operator (old style)
engine = create_engine('postgresql://user@localhost/database')
connection = engine.connect()
# ruleid: sqlalchemy-execute-raw-query
connection.execute("INSERT INTO person (name) VALUES ('%s')" % (name))

# Execute query from string formating (new style)
engine = create_engine('postgresql://user@localhost/database')
connection = engine.connect()
# ruleid: sqlalchemy-execute-raw-query
connection.execute("INSERT INTO person (name) VALUES ('{}')".format(name))

# Execute query from string concatenation fstrings
engine = create_engine('postgresql://user@localhost/database')
connection = engine.connect()
# ruleid: sqlalchemy-execute-raw-query
connection.execute(f"INSERT INTO person (name) VALUES ('{name}')")

##########################################################################
# Execute query in With block
##########################################################################

# Execute query in With block from String concatenation using + operator
engine = create_engine('postgresql://user@localhost/database')
with engine.connect() as connection:
    # ruleid: sqlalchemy-execute-raw-query
    connection.execute("INSERT INTO person (name) VALUES ('" + name + "')")

# Execute query in With block from string formating using % operator (old style)
engine = create_engine('postgresql://user@localhost/database')
with engine.connect() as connection:
    # ruleid: sqlalchemy-execute-raw-query
    connection.execute("INSERT INTO person (name) VALUES ('%s')" % (name))

# Execute query in With block from String formating (new style)
engine = create_engine('postgresql://user@localhost/database')
with engine.connect() as connection:
    # ruleid: sqlalchemy-execute-raw-query
    connection.execute("INSERT INTO person (name) VALUES ('{}')".format(name))

# Execute query in With block from String concatenation  fstrings
engine = create_engine('postgresql://user@localhost/database')
with engine.connect() as connection:
    # ruleid: sqlalchemy-execute-raw-query
    connection.execute(f"INSERT INTO person (name) VALUES ('{name}')")

##########################################################################
# Execute query in With block and using a variable
##########################################################################

# Execute query in With block from variable set by string concatenation using + operator
engine = create_engine('postgresql://user@localhost/database')
with engine.connect() as connection:
    query = "INSERT INTO person (name) VALUES ('" + name + "')"
    # ruleid: sqlalchemy-execute-raw-query
    connection.execute(query)

# Execute query in With block from variable (type) set by String concatenation using + operator
engine = create_engine('postgresql://user@localhost/database')
with engine.connect() as connection:
    query: str = "INSERT INTO person (name) VALUES ('" + name + "')"
    # ruleid: sqlalchemy-execute-raw-query
    connection.execute(query)


# Execute query in With block from variable set by String formating using % operator (old style)
engine = create_engine('postgresql://user@localhost/database')
with engine.connect() as connection:
    query = "INSERT INTO person (name) VALUES ('%s')" % (name)
    # ruleid: sqlalchemy-execute-raw-query
    connection.execute(query)

# Execute query in With block from variable (type) set by String formating using % operator (old style)
engine = create_engine('postgresql://user@localhost/database')
with engine.connect() as connection:
    query: str = "INSERT INTO person (name) VALUES ('%s')" % (name)
    # ruleid: sqlalchemy-execute-raw-query
    connection.execute(query)

# Execute query in With block from variable set by String formating (new style)
engine = create_engine('postgresql://user@localhost/database')
with engine.connect() as connection:
    query = "INSERT INTO person (name) VALUES ('{}')".format(name)
    # ruleid: sqlalchemy-execute-raw-query
    connection.execute(query)

# Execute query in With block from variable (typed) set by String formating (new style)
engine = create_engine('postgresql://user@localhost/database')
with engine.connect() as connection:
    query: str = "INSERT INTO person (name) VALUES ('{}')".format(name)
    # ruleid: sqlalchemy-execute-raw-query
    connection.execute(query)

# Execute query in With block from variable set by String concatenation  fstrings
engine = create_engine('postgresql://user@localhost/database')
with engine.connect() as connection:
    
    query = f"INSERT INTO person (name) VALUES ('{name}')"
    # ruleid: sqlalchemy-execute-raw-query
    connection.execute(query)

# Execute query in With block from variable (typed) set by String concatenation  fstrings
engine = create_engine('postgresql://user@localhost/database')
with engine.connect() as connection:
    
    query: str = f"INSERT INTO person (name) VALUES ('{name}')"
    # ruleid: sqlalchemy-execute-raw-query
    connection.execute(query)

########################################################################

# Query using prepared statement with named parameters
# ok: sqlalchemy-execute-raw-query
engine = create_engine('postgresql://user@localhost/database')
stmt = text("INSERT INTO table (name) VALUES(:name)")
connection.execute(stmt, name='Frodon Sacquet')

# SQL Composition and prepared statement
# ok: sqlalchemy-execute-raw-query
engine = create_engine('postgresql://user@localhost/database')
query = select(literal_column("users.fullname", String) + ', ' + literal_column("addresses.email_address").label("title")).where(and_(literal_column("users.id") == literal_column("addresses.user_id"), text("users.name BETWEEN 'm' AND 'z'"), text("(addresses.email_address LIKE :x OR addresses.email_address LIKE :y)"))).select_from(table('users')).select_from(table('addresses'))
# deepruleid: sqlalchemy-execute-raw-query
conn.execute(query, {"x":"%@aol.com", "y":"%@msn.com"}).fetchall()


# SQL Composition using SQL Expression
connection_string = 'sqlite:///db.sqlite'
engine = create_engine(connection_string, echo=True)
with engine.connect() as connection:
  meta = MetaData()
  meta.reflect(bind=connection)
  product_table = meta.tables['product']
  # ok: sqlalchemy-execute-raw-query
  stmt = (
    select(product_table)
    .where(product_table.columns[field_name] == value_name)
  )
  result = connection.execute(stmt)

# Insert multi data record using SQL Expression
connection_string = 'sqlite:///db.sqlite'
engine = create_engine(connection_string, echo=True)
with engine.connect() as connection:
  meta = MetaData()
  meta.reflect(bind=connection)
  product_table = meta.tables['product']
  # ok: sqlalchemy-execute-raw-query
  stmt = insert(product_table)
  values = [
      {field_name: 'hazelnut', field_price: 5},
      {field_name: 'banana', field_price: 8}
  ]
  print(stmt)
  connection.execute(stmt, values)

# Insert multi data record using SQL Expression
connection_string = 'sqlite:///db.sqlite'
engine = create_engine(connection_string, echo=True)
with engine.connect() as connection:
  meta = MetaData()
  meta.reflect(bind=connection)
  product_table = meta.tables['product']
  stmt = insert(product_table) + 'test'
  values = [
      {field_name: 'hazelnut', field_price: 5},
      {field_name: 'banana', field_price: 8}
  ]
  # ruleid: sqlalchemy-execute-raw-query
  connection.execute(stmt, values)
