Python Iterating, FOR loop passing list to Parameters in a MySQL Query

Question

I'm trying to iterate through a Python list to have multiple queries in MySQL, but the "%s" parameter is including quotes and I get only zeros (0) in my query (when I know I should get a different number).

The code:

    def export_data():  

         infos = [
                 'Conta Corrente Itau',
                 'Cartao Itau Master',
                 'Cartao Itau VISA',
                 'Carteira'
                 ]

        chart_of_accounts = list(infos)          

        for account in chart_of_accounts:
            cnx = mariadb.connect(user='root', password='', database='base_completa')
            cursor = cnx.cursor() 
            params = (account, account)
            query = """
                    SELECT Y, M,(@total := @total + Fluxo) AS ValorTotal
                    FROM (
                    SELECT year(data) AS Y, month(data) AS M, 
                            (
                                SUM(IF(Credito="%s", valor, 0))-
                                SUM(IF(Debito="%s", valor, 0))
                            ) AS Fluxo
                        FROM ledger
                        GROUP BY YEAR(DATA), MONTH(DATA)
                        ) AS T,
                    (SELECT @total:=0) AS n;
                    """ % (params)

            cursor.execute(query)
            rows = cursor.fetchall()
            desc = cursor.description

            lista = [dict(itertools.izip([col[0] for col in desc], row)) 
                for row in rows]

            cnx.commit()

            print account
            print json.dumps(lista)

        return lista

    results = export_data()

I also tried:

cursor.execute(query, (chart_of_accounts[account] for account in chart_of_accounts))

But I still get zero. I think it is including the quotes inside the parameter so the query it is making is:

SUM(IF(Credito=""Conta Corrente Itau"", valor, 0))

EDIT: (Actually, it was not passing the list element to the query, so it was querying:

SUM(IF(Credito="%s", valor, 0))

But I don't know for sure.

It seems to work fine other than that, as my "checking prints" are giving me the expected results.

RESOLVED:

When having more than one parameter in the MySQL Query, I found 1 thread that answered my question: add a "params" variable that aggregates the parameters.

params = (account, account)

And then add % (params) after the end of the query (""")

Updated the code with a functioning one. Still don't know if that is good practice or the best way to do it, but it works.

user8834780 · Accepted Answer · 2017-11-29 03:41:44Z

1

The cursor doesn't allow multiple queries to be executed.

Instead of cursor = cnx.cursor() try this:

    with open(file_location+'\\' + 'file_name' +'.sql','r') as inserts:
        sqlScript = inserts.read()
        for statement in sqlScript.split(';'):
            with cnx.cursor() as cur:
                cur.execute(statement)

    query="Select * from output_table"

You can also easily create a DataFrame from output like so:

df=pd.read_sql_query(query, cnx)

Make sure your queries are separated by ";" and do not put ";" after the last query or it will fail.

--Edit--

Here you need to have your script from your query=... statement in file_name.sql file. The way this is written, the data you need should end up in a (temp) table so that all you need to get the data is to call:

Select * from output_table

edited Nov 29, 2017 at 3:41

answered Nov 29, 2017 at 3:20

user8834780

1,6805 gold badges25 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

lowercase00 Over a year ago

When substituting the whole thing (from cursor.execute to cnx.commite) by the df = pd.read_sql_query I got the following error: sqlalchemy.exc.ArgumentError: Could not parse rfc1738 URL from string 'Conta Corrente Itau' - researching what that means exactly and how to solve it right now, I'll see if pandas df's are a better option (it does reduces the code size).

user8834780 Over a year ago

This is a database connection issue to sqlalchemy. If it worked previously, did you make changes to cnx = ...? My changes should come instead of calling cnx.cursor() and executing the script, since that will not allow to execute multiple

lowercase00 Over a year ago

not really, it was in the 5min time frame. But I'm not using SQLAlchemy... I'm using MySQL.Connector (MariaDB), should I change to SQLAlchemy?

user8834780 Over a year ago

No need to switch, you said you got a sqlalchemy.exc.ArgumentError that is why I asked

lowercase00 Over a year ago

maybe pandas expect sqlalchemy... regarding your first suggestion, just to understand: the "split(";")" is splitting the queries that are being iterated, is that right? starting to make sense now... so I would iterate through my list right after the "with cnx.cursor()"?

|

lowercase00 · Accepted Answer · 2017-12-01 01:16:55Z

0

RESOLVED:

When having more than one parameter in the MySQL Query, I found 1 thread that answered my question: add a "params" variable that aggregates the parameters.

params = (account, account)

And then add % (params) after the end of the query (""")

Updated the code with a functioning one. Still don't know if that is good practice or the best way to do it, but it works.

answered Dec 1, 2017 at 1:16

lowercase00

2,6964 gold badges24 silver badges39 bronze badges

Collectives™ on Stack Overflow

Python Iterating, FOR loop passing list to Parameters in a MySQL Query

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related