reformat row count in sqalchemy

Question

I'm running a simple command to fetch and print the number of rows in a table within a MySQL db so that RunDeck can log it, and was hoping for either some help with formatting the result or a prettier way to do it.

from sqlalchemy import create_engine

devhost = 'host'
devuser = 'user'
devpassword = 'scipiodestroyedhannibalatzama'

engine = f"mysql+pymysql://{devuser}:{devpassword}@{devhost}/germans_got_lucky"
connection = create_engine(engine)

rows = str(connection.execute("SELECT COUNT(Day) FROM teutoburg_forest").fetchall())
    print(rows)

returns

[(1706,)]

but I'd like it to return

Is there a simple way to do this?

score 1 · Accepted Answer · answered May 06 '21 at 15:24

Three possibilities here, first:

from sqlalchemy import create_engine

devhost = 'host'
devuser = 'user'
devpassword = 'scipiodestroyedhannibalatzama'

engine = f"mysql+pymysql://{devuser}:{devpassword}@{devhost}/germans_got_lucky"
connection = create_engine(engine)

rows = len(connection.execute("SELECT Day FROM teutoburg_forest").fetchall())
print(rows)

The second:

from sqlalchemy import create_engine

devhost = 'host'
devuser = 'user'
devpassword = 'scipiodestroyedhannibalatzama'

engine = f"mysql+pymysql://{devuser}:{devpassword}@{devhost}/germans_got_lucky"
connection = create_engine(engine)

rows = connection.execute("SELECT Day FROM teutoburg_forest").count
print(rows)

or the third:

from sqlalchemy import create_engine

devhost = 'host'
devuser = 'user'
devpassword = 'scipiodestroyedhannibalatzama'

engine = f"mysql+pymysql://{devuser}:{devpassword}@{devhost}/germans_got_lucky"
connection = create_engine(engine)

rows = connection.execute("SELECT COUNT(Day) FROM teutoburg_forest").fetchone()
print(rows[0])

option 3 is much cleaner than what i was using. thank you! – zabada May 06 '21 at 16:15 — zabada, May 06 '21 at 16:15

score 1 · Answer 2 · answered May 06 '21 at 18:09

1

.scalar() gives what you need:

conn.execute(text("SELECT count(tickers.ticker) FROM tickers")).scalar()

I also used text() clause as it is required in sqlalchemy>=1.4

answered May 06 '21 at 18:09

van

74,297
13
168
171

score 0 · Answer 3 · answered May 06 '21 at 15:24

0

This answer worked perfectly to strip away the unneeded characters.

rows = str(connection.execute("SELECT COUNT(Day) FROM teutoburg_forest").fetchall())
print(rows)
[(1706,)]
print(''.join([n for n in rows if n.isdigit()]))
1706

answered May 06 '21 at 15:24

zabada

67
1
10

reformat row count in sqalchemy

3 Answers3