mysql data export for specific value using python

Question

I'm trying to get specific rows of data from mysql database table1. I'm looking wherever a certain id shows up in one of the columns.

I am having trouble with my code in the SELECT WHERE statement under 'query'. When i ask for a specific id (id_2_lookup) I get the entire table exported. where am I going wrong?

# Establish a MySQL connection
from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()

#to export to excel
import xlsxwriter
from xlsxwriter.workbook import Workbook

#to get the datetime functions
import datetime
from datetime import datetime


#creates the workbook
workbook = xlsxwriter.Workbook('imheretoo.xlsx')
worksheet = workbook.add_worksheet()

#formatting definitions
bold    = workbook.add_format({'bold': True})
date_format = workbook.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'})
timeShape =  '%Y-%m-%d %H:%M:%S'


query = ("SELECT sent_time, delivered_time, customer_name, id1_active, id2_active, id3_active, id1_inactive, id2_inactive, id3_inactive, location_active, location_inactive FROM table1 ")
"WHERE id1_active  '%s'"
id_2_lookup = [input('Who are you looking for:'),0]




# Execute sql Query
cursor.execute(query)
result = cursor.fetchall()


#sets up the header row
worksheet.write('A1','sent_time',bold)
worksheet.write('B1', 'delivered_time',bold)
worksheet.write('C1', 'customer_name',bold)
worksheet.write('D1', 'id1_active',bold)
worksheet.write('E1', 'id2_active',bold)
worksheet.write('F1', 'id3_active',bold)
worksheet.write('G1', 'id1_inactive',bold)
worksheet.write('H1', 'id2_inactive',bold)
worksheet.write('I1', 'id3_inactive',bold)
worksheet.write('J1', 'location_active',bold)
worksheet.write('K1', 'location_inactive',bold)
worksheet.autofilter('A1:K1')



print("sent_time", "delivered_time", "customer_name", "id1_active", "id2_active", "id3_active", "id1_inactive", "id2_inactive", "id3_inactive", "location_active", "location_inactive")
for row in result:
    print(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9],row[10])

# Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the headers
for r, row in enumerate(result, start=1):  #where you want to start printing results inside workbook
    for c, col in enumerate(row):
        worksheet.write_datetime(r,0,row[0], date_format)
        worksheet.write_datetime(r,1, row[1], date_format)
        worksheet.write(r,2, row[2])
        worksheet.write(r,3, row[3])
        worksheet.write(r,4, row[4])
        worksheet.write(r,5, row[5])
        worksheet.write(r,6, row[6])
        worksheet.write(r,7, row[7])
        worksheet.write(r,8, row[8])
        worksheet.write(r,9, row[9])
        worksheet.write(r,10, row[10])




#close out everything and save
cursor.close()
workbook.close()
conn.close()

#print number of rows and bye-bye message
print ("- - - - - - - - - - - - -")
rows = len(result)
print ("I just imported "+ str(rows) + " rows from MySQL!")
print ("")
print ("Good to Go!!!")
print ("")

I want to give you all the information I have with all the other files and what I'm shooting for to see if you can replicate and actually get it working just to see if it's not just my system or some configuration I have..

I have a database with one table.Lets call it ‘table1’ The table is broken down with columns like this:

Lets say that these are two or more customers delivering goods to and from each other. Each customer has three id#s.

I created a ‘config.ini’ file to make my life a bit easier

[mysql]
host = localhost
database = db_name
user = root
password = blahblah

I created a ‘python_mysql_dbconfig.py’

from configparser import ConfigParser

def read_db_config(filename=’config.ini’, section=’mysql’):
“”” Read database configuration file and return a dictionary object
:param filename: name of the configuration file
:param section: section of database configuration
:return: a dictionary of database parameters
“””
# create parser and read ini configuration file
parser = ConfigParser()
parser.read(filename)

# get section, default to mysql
db = {}
if parser.has_section(section):
items = parser.items(section)
for item in items:
db[item[0]] = item[1]
else:
raise Exception(‘{0} not found in the {1} file’.format(section, filename))

return db

I now have the code we've been working on but now updated with what changes we had made but am still getting errors...

# Establish a MySQL connection
from mysql.connector import MySQLConnection, Error
from python_mysql_dbconfig import read_db_config
db_config = read_db_config()
conn = MySQLConnection(**db_config)
cursor = conn.cursor()

#to export to excel
import xlsxwriter
from xlsxwriter.workbook import Workbook

#to get the datetime functions
import datetime
from datetime import datetime


#creates the workbook
workbook = xlsxwriter.Workbook('imheretoo.xlsx')
worksheet = workbook.add_worksheet()

#formatting definitions
bold    = workbook.add_format({'bold': True})
date_format = workbook.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'})
timeShape =  '%Y-%m-%d %H:%M:%S'

#actual query

id_2_lookup= [input('Who are you looking for:'),0]

query = (
    """SELECT sent_time, delivered_time, customer_name, id1_active, id2_active, id3_active, id1_inactive, id2_inactive, id3_inactive, location_active, location_inactive FROM table1 "
    "WHERE id1_active = %s""",(id_2_lookup)
)

# Execute sql Query

query = query % id_2_lookup
cursor.execute(query)
result = cursor.fetchall()



#sets up the header row
worksheet.write('A1','sent_time',bold)
worksheet.write('B1', 'delivered_time',bold)
worksheet.write('C1', 'customer_name',bold)
worksheet.write('D1', 'id1_active',bold)
worksheet.write('E1', 'id2_active',bold)
worksheet.write('F1', 'id3_active',bold)
worksheet.write('G1', 'id1_inactive',bold)
worksheet.write('H1', 'id2_inactive',bold)
worksheet.write('I1', 'id3_inactive',bold)
worksheet.write('J1', 'location_active',bold)
worksheet.write('K1', 'location_inactive',bold)
worksheet.autofilter('A1:K1')



print("sent_time", "delivered_time", "customer_name", "id1_active", "id2_active", "id3_active", "id1_inactive", "id2_inactive", "id3_inactive", "location_active", "location_inactive")
for row in result:
    print(row[0], row[1], row[2], row[3], row[4], row[5], row[6], row[7], row[8], row[9],row[10])

# Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the headers
for r, row in enumerate(result, start=1):  #where you want to start printing results inside workbook
    for c, col in enumerate(row):
        worksheet.write_datetime(r,0,row[0], date_format)
        worksheet.write_datetime(r,1, row[1], date_format)
        worksheet.write(r,2, row[2])
        worksheet.write(r,3, row[3])
        worksheet.write(r,4, row[4])
        worksheet.write(r,5, row[5])
        worksheet.write(r,6, row[6])
        worksheet.write(r,7, row[7])
        worksheet.write(r,8, row[8])
        worksheet.write(r,9, row[9])
        worksheet.write(r,10, row[10])




#close out everything and save
cursor.close()
workbook.close()
conn.close()

#print number of rows and bye-bye message
print ("- - - - - - - - - - - - -")
rows = len(result)
print ("I just imported "+ str(rows) + " rows from MySQL!")
print ("")
print ("Good to Go!!!")
print ("")

Your where clause isn't actually part of your query. Try printing out your query to make sure it's doing what you want. — JCVanHamme
– JCVanHamme, Commented Dec 16, 2015 at 16:10
You don't use at all the value in id_2_lookup. Print your query before querying for debug — Tom Ron
– Tom Ron, Commented Dec 16, 2015 at 16:10
ok. I'm really new at this...sorry. I think i set it up to print out my query please tell me if I'm doing it wrong query = """ SELECT sent_time, delivered_time, customer_name, id1_active, id2_active, id3_active, id1_inactive, id2_inactive, id3_inactive, location_active, location_inactive FROM table1 WHERE id1_active '%s' """ id_2_lookup = [input('Who are you looking for:'),0] print(query) — Daniel
– Daniel, Commented Dec 16, 2015 at 16:21
That looks like it should print the query. However, as @TomRon pointed out, you're not using the value of id_2_lookup. Prior to your print statement add a line query % (id_2_lookup,). That will insert the value of id_2_lookup at the %s in your query string. — JCVanHamme
– JCVanHamme, Commented Dec 16, 2015 at 17:00
@JCVanHamme I did what you told me and I got an error that reads this"Traceback (most recent call last):File "C:\Python34\lookup_id.py", line 36, in <module> cursor.execute(query) File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", — Daniel
– Daniel, Commented Dec 16, 2015 at 17:12

JCVanHamme · Accepted Answer · 2015-12-17 02:35:58Z

2

In the code you originally posted, the where clause isn't actually being included as part of the query. What I didn't notice at first was that you're including the results of your input call in a list that you're then trying to feed to the cursor.execute call. That won't work since you only have one marker to replace in your query string. If we assume that the user is entering the value for id1_active, then I think you want the following:

query = (
    "SELECT sent_time, delivered_time, customer_name, id1_active, id2_active, id3_active, id1_inactive, id2_inactive, id3_inactive, location_active, location_inactive FROM table1 "
    "WHERE id1_active = %s"
)
id_2_lookup = input('Who are you looking for:')
# Execute sql Query
cursor.execute(query, (id_2_lookup, ))

This will try to match the user entered data against id1_active. Obviously, if that's not what you intended you'll have to adjust your query as necessary.

You could, in principle, insert the input values yourself and not rely on cursor.execute to do it for you. In that case,

query = (
    "SELECT sent_time, delivered_time, customer_name, id1_active, id2_active, id3_active, id1_inactive, id2_inactive, id3_inactive, location_active, location_inactive FROM table1 "
    "WHERE id1_active = %s"
)
id_2_lookup = input('Who are you looking for:')
# Execute sql Query
query = query % id_2_lookup
cursor.execute(query)

This will insert the value directly into the query so that cursor.execute only sees the query string and doesn't need to do any replacement. Generally speaking, though, your way is better.

edited Dec 17, 2015 at 2:35

answered Dec 16, 2015 at 18:17

JCVanHamme

1,0507 silver badges12 bronze badges

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

10 Comments

Daniel Over a year ago

does any of this make a difference if I'm using Python34?

Daniel Over a year ago

Traceback (most recent call last): File "C:\Python34\lookup_id.py", line 37, in <module> cursor.execute(query, id_2_lookup) File "C:\Python34\lib\site-packages\mysql\connector\cursor.py", line 492, in execute stmt = operation.encode(self._connection.python_charset) AttributeError: 'tuple' object has no attribute 'encode'

Daniel Over a year ago

I don't want to do anything with customer_name as of yet...just id1_active. so I modified your example to reflect this: query = ( """SELECT sent_time, delivered_time, customer_name, id1_active, id2_active, id3_active, id1_inactive, id2_inactive, id3_inactive, location_active, location_inactive FROM table1 " "WHERE id1_active = %s""",(id_2_lookup)

Daniel Over a year ago

if I do your second example I get this :

Daniel Over a year ago

Traceback (most recent call last): File "C:\Python34\lookup_id.py", line 37, in <module> query = query % id_2_lookup TypeError: unsupported operand type(s) for %: 'tuple' and 'list'

|

Collectives™ on Stack Overflow

mysql data export for specific value using python

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related