Author: cored0mp

Hey Gang!

OK today I am having trouble with my transaction processing application implemented in python/MySQL. Here is some "working" testing code.

import psycopg2
from psycopg2 import Error
import binascii
from binascii import unhexlify
import mysql.connector as mysql


sql='''CREATE PROCEDURE testprocedure(OUT tacos INT)
BEGIN

 show tables;
 SET tacos := 1 ;
END'''

blank_connection_string = {
                'user': 'someusername',
                'password': 'daniwebisthebest',
                'host': 'localhost',
                'database': 'fundatabase',
                'raise_on_warnings': True }


connection=mysql.connect(**blank_connection_string, autocommit=True)
cursor=connection.cursor()


cursor.execute(sql)

cursor.callproc("testprocedure", (0, ) )
for r in cursor.stored_results():
        print(r)
        results=r.fetchall()
        print(results)
        for result in results:
                print(result)

cursor.execute("drop procedure testprocedure")

cursor.close( )
connection.close()

So it works in that everything that I can fit into a stored procedure certainly executes. But I'm getting nonsense as far as returning the variable "tacos" into any format that python can access. To wit, here is what I'm getting for script output:

MySQLCursorBuffered: (a result of CALL testprocedure(@_testpr..
[('sometable1',), ('funtablename',), ('othertable',)]
('sometable1',)
('funtablename',)
('othertable',)

In other words it seems to be doing a pretty solid job of returning the results that I DON'T need sent back to me, but is not returning the result of the actual SP that I need.

How do I resolve this?

Hey! I've been asked to store some data from a client in mysql in an encoded format using python. Nothing could be easier, right?

I will later need to decode the same data using mysql exclusively. Given that constraint, I thought that base64 would be the go to since nearly every contemporary data tool that we've heard of is base64-literate.

So from flatfile it goes into the db using python no problem.

When I try to decode the data for warehousing later, the mysql from_base64() function returns a hexadecimal string instead of....ascii string? Or whatever. Human readable string.

I've found various hacks online that...should work? But I want to code this as correctly as possible because portability is so important, and it's tough to remember whatever hack you used to get it running five years ago. I'm convinced I'm doing it wrong.

Is there a better way to python-encode data that's consistently reversible using mysql for processing?

Thanks in advance!

More programming fun!

OK this time around I'm trying to create a table with the statement,

CREATE TABLE tablename ( recnumber SERIAL  PRIMARY KEY, recordvalues VARCHAR ( 500 ) NOT NULL, datecreated DATE )

And it basically works, and I can add records using the dba account. However when I try to add records using the application db account I get the error,

permission denied for sequence _seq

A search engine claims that I need to assign "Usage" permissions, but this is turning into a rabbit hole of then needing to assign 'Usage' rights specific to something called a 'sequence.' But I feel like that will only be a dead end.

Am I better off granting . privs to the application service account? Or perhaps just running the application using the dba account? Or maybe I should switch back to Mysql?

It seems like with postgres there are a lot of hoops to jump through but that the nuances are reducing than adding to the flexibility of what I'm able to configure as a DBA. This is not a rant post and I am quite happy to switch back to Mysql if that's what everyone else is doing. No point in using Postgres if it's a step backwards.

Thanks in advance!

How do I create a table in postgres that's associated with a specific database?

Obviously from the command line one can do

Create table tablename ();

But how do I make sure that the table is associated with the correct database?

CREATE table dbname.tablename();

is giving me the error:

ERROR:  schema "dbname" does not exist