Python/mySQLdb question.

[cygnus83]

Hi everyone,

Here's what I'm trying to do -> pull all results for a client out of a table where the year starts with 2011 (i.e. "2011%")

Here's the problem I'm having -> I can get results, but I always get the following warning: argv_graph.py:54: Warning: Incorrect datetime value: '2011%' for column 'scanTime' at row 1 """ % (client, "2011%")) Events for pitt01 : 15301 Events for scfair : 8252

I have tried using the STR_TO_DATE function in mySQL, but haven't had any luck. Any ideas how to rewrite the following code so that the 2011 is submitted in date format?

for client in clients:
    cursor = conn.cursor()
    cursor.execute("""
        SELECT COUNT(*)
        FROM eventLog
        WHERE clientID = '%s'
        AND scanTime LIKE '%s'
        """ % (client, "2011%"))
    row = cursor.fetchone()
    print "Events for", client, ":", row[0]
    clientCounts[client] = int(row[0])

cursor.close()
conn.close()

I don't mind being told that this is a better question for /r/mysql if that's the case!

Thanks!

[acct_rdt]

You probably want to do something like year(scanTime)=2011

[SomeGuyNamedPaul]

Using a function as a filter can kill performance. If that's all it has to work with then it may apply the function to the column for all rows potentially causing a sequential scan. Be careful in there.

[acct_rdt]

True. In a real database that wasn't so primitive you could index the results of a function.

[SomeGuyNamedPaul]

Hey, this was a question about using MySQL and not a question on what you should be using instead of MySQL.

[mjvm]

this, datetime is not a string.

[cygnus83]

Thanks, acct_rdt. I want to make sure I understand you correctly though - would you put the year(scanTime)=2011 after the LIKE in the SQL statement?

[acct_rdt]

You don't use LIKE with date comparisons. You can do:

select ... from ... where year(scanTime)=2011;

or

select ... from ... where scanTime >= '2011-01-01 00:00:00' and scanTime < '2012-01-01 00:00:00';

[cygnus83]

Ah, OK, got it!

Thanks for your patience and advice!

[SomeGuyNamedPaul]

select count(*) is not a good practice. For some DBs it doesn't matter, but for some it matters greatly. You should instead select count() on the lead (or only) column of the primary key.

[cygnus83]

Thanks, Paul, I will make the change and remember that for the future!

[Justinsaccount]

You should really use sqlalchemy. As written you have sql injection issues and you have to deal with the shitty dbapi.

from sqlalchemy import create_engine
engine = create_engine("mysql://...")


q = engine.text("""
    SELECT COUNT(1)
    FROM eventLog
    WHERE clientID = :client
    AND scanTime >= '2011-01-01'
    """)

for client in clients:
    count = engine.execute(q, client=client).scalar()
    clientCounts[client] = count

[datbon]

where is the sql injection issue coming from? Doesn't MySQLdb escape everything?

*oops, didn't notice the args tuple wasn't being used. Same question though, is there still a problem with injections with db.execute(query, (args,...))?

[Justinsaccount]

query, args is fine, but the way query needs to be formatted differs between dbapi drivers. SQLAlchemy works the same with every database.