CODEWITHSUNDEEP
pythonmysqlmysql-workbenchmysql-python
Jreed
Jreed

Reputation: 115

Query works in MySQL workbench, but not in MySQL connector

So my problem is with the fact that when I try and run a query, this specific one:

SELECT call_start_time, call_end_time, sid FROM calls WHERE (call_start_time BETWEEN
'2014-01-08 00:00:00' AND '2014-01-09 00:00:00') AND voice_info_id IS NOT NULL AND
call_start_time IS NOT NULL AND call_end_time IS NOT NULL

It runs in workbench and returns the correct data I want, but when I try to run the query in the below code using MySQL connector and Python, it complains that the syntax is incorrect even though it is correct. I've looked thoroughly at the connector documentation and I can't find anything that supremely differs between what I'm doing and the example on the Querying Data Using Connector/Python page on the connector documentation. The code that I'm using is below: 

from time import *
import numpy as np
import matplotlib.pyplot as plt
import mysql.connector
from datetime import datetime
from sets import Set

config = {
    'user': 'xxxxxxxxx',
    'password': 'xxxxxxxxx',
    'host': '127.0.0.1',
    'database': 'xxxxxxxxx',
    'raise_on_warnings': True,
}

tick = set([])
epoch_start_call_set = set([])
epoch_end_call_set = set([])
from mysql.connector import errorcode
try:
    cnx = mysql.connector.connect(**config)
    cursor = cnx.cursor()
    print("DATABASE CONNECTION ESTABLISHED")
    print

    userIn = raw_input("Type Start Date (MM-DD-YYYY): ")
    userEnd = raw_input("Type End Date (MM-DD-YYYY): ")
    startdate = datetime.strptime(userIn, '%m-%d-%Y')
    enddate = datetime.strptime(userEnd, '%m-%d-%Y')
    epoch_s = mktime(startdate.timetuple())
    epoch_e = mktime(enddate.timetuple())
    while epoch_s <= epoch_e:
        current_tick = epoch_s + 60
        epoch_s += 60
        tick.add(current_tick)

    query = "SELECT call_start_time, call_end_time, sid FROM calls" \
            "WHERE call_start_time BETWEEN %s AND %s " \
            "AND voice_info_id IS NOT NULL AND call_start_time IS NOT NULL " \
            "AND call_end_time IS NOT NULL"
    cursor.execute(query, (startdate, enddate))


except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Your password or username is incorrect, please try again")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Your database does not exist, please try again")
    else:
        print(err)
else:
    cnx.close()

The data that I'm looking through is here in workbench, there is a lot more, but this is the sample I'm testing on. https://i.sstatic.net/sz82K.png

This is the result of when I run the query in workbench: https://i.sstatic.net/1nphY.png

Which is the result that I want. Is there a particular reason my query is failing? I've tried rewriting and manually substituting in the exact dates in the query instead of using the parameter injections, but nothing seems to fix it.

Upvotes: 1

Views: 1260

Answers (1)

Marc B
Marc B

Reputation: 360872

Typos:

query = "SELECT call_start_time, call_end_time, sid FROM calls" \
                                                              ^---
         "WHERE call_start_time BETWEEN %s AND %s " \
         ^---

You're missing a space at the indicated spot, producing:

SELECT ... FROM callsWHERE
                     ^---

Upvotes: 2

Related Questions