0

I am writing a basic banking app to keep my skills fresh and am trying to implement a transaction table into the mix to of course keep track of transactions. This happens as I am trying to insert a new transaction programmatically using Python and psycopg[binary]. I have it the same as I do for creating new accounts and customers so I am at a loss for words after trying everything I can think of including using a formatted string for my parameters and the same problem was occurring. I will include a clip of the code that has the unexpected behavior as well as clips where the code is similar and follows the expected behavior. I even forced an error in the good code so that it would show that it has the data in a single tuple.

def create_transaction(self, transaction: Transaction) -> Transaction:
    logging.info("Beginning DAL function create transaction")
    sql = "insert into banking.transactions values (default, %s, %s, %s, %s) returning transaction_id;"
    cursor = connection.cursor()
    cursor.execute(sql, (transaction.account_id, transaction.transaction_type, transaction.amount, transaction.time_and_date))
    connection.commit()
    transaction_id = cursor.fetchone()[0]
    transaction.transaction_id = transaction_id
    logging.info("Finishing DAL function create transaction")
    return transaction

def create_customer(self, customer: Customer) -> Customer:
    logging.info("Beginning DAL function create customer")
    sql = "insert into banking.customers values (default, %s, %s, %s, %s) returning customer_id;"
    cursor = connection.cursor()
    cursor.execute(sql, (customer.first_name, customer.last_name, customer.username, customer.password))
    connection.commit()
    customer_id = cursor.fetchone()[0]
    customer.customer_id = customer_id
    logging.info("Finishing DAL function create customer")
    return customer

def create_account(self, account: BankAccount) -> BankAccount:
    logging.info("Beginning DAL function create account")
    sql = "insert into banking.bank_accounts values (default, %s, %s) returning account_id;"
    cursor = connection.cursor()
    cursor.execute(sql, (account.customer_id, account.balance))
    connection.commit()
    account_id = cursor.fetchone()[0]
    account.account_id = account_id
    logging.info("Finishing DAL function create account")
    return account

query = 'insert into banking.bank_accounts values (default, %d, %s) returning account_id;' params = (-1, 50.0)

query = 'insert into banking.transactions values (default, %s, %s, %s, %s) returning transaction_id;'

params = ((-1,), ('deposit',), (50.0,), '2022-11-28 21:56:22.000486')

def execute(
    self: _Self,
    query: Query,
    params: Optional[Params] = None,
    *,
    prepare: Optional[bool] = None,
    binary: Optional[bool] = None,
) -> _Self:
    """
    Execute a query or command to the database.
    """
    try:
        with self._conn.lock:
            self._conn.wait(
                self._execute_gen(query, params, prepare=prepare, binary=binary)
            )
    except e.Error as ex:

      raise ex.with_traceback(None)

E psycopg.errors.InvalidTextRepresentation: invalid input syntax for integer: "(-1)"

I have it the same as I do for creating new accounts and customers so I am at a loss for words after trying everything I can think of including using a formatted string for my parameters and the same problem was occurring. I have tried typecasting it to an integer before using the execute method but it happens within that line of code.

dmerc12
  • 1
  • 1
  • 1
    1) This `params = ((-1,), ('deposit',), (50.0,), '2022-11-28 21:56:22.000486')` should be `params = (-1, 'deposit', 50.0, '2022-11-28 21:56:22.000486')`. 2) This `'insert into banking.bank_accounts values (default, %d, %s) ...` is wrong `%d` is no a valid placeholder. 3) Read the [psycopg2 parameters](https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries). – Adrian Klaver Nov 29 '22 at 15:46
  • 1. That I am trying to understand why it is turning my input from the expected latter and into the former. 2. I realize that, I used that error to show that the relatively same code is acting differently in separate modules. I switched it from from %s to %d to show the data type being sent in the bank_accounts module is what is expected. In the transactions module the same methods are turning the data into a tuple of tuples as you pointed out. I am not type casting the params in any way. – dmerc12 Nov 30 '22 at 07:23
  • Then the values in `(transaction.account_id, transaction.transaction_type ...)` are coming in as individual tuples(with value) instead of individual values e.g. `(-1,)` instead of `-1`. I would guess this is happening in `Transaction`. – Adrian Klaver Nov 30 '22 at 16:08

0 Answers0