CODEWITHSUNDEEP
pythonsql
Michael Norman
Michael Norman

Reputation: 547

SQL Python, handling one or many values in Where Statement

I have this dummy code below. Pretty much what I am trying to do is create a table with all the customers and their ID's in a division or division(s) that is specific in an excel sheet. In the excel cell, there can be either one division listed or several. The code below works if there are multiple divisions, however if there is only one, then the code does not work as I am creating a tuple and the where clause essentially becomes something like where division_id in (92,) which runs a SQL error. How can I write this code so that it can both handle multiple divisions and just single divisions.

Code:

filename = 'test.csv'
division = test.iloc[0][4]
division_tuple = tuple(map(int, division.split(",")))


sql = cs.execute("""
INSERT INTO sample_table_name
select c.customer_id,
c.customer_name
from(
t.customer_id
t.customer_name


from customer_table t

where division_id in {}
group by 1,2) c ;
""".format(division_tuple))

I get the following error if the excel sheet only has one listed division:

Error from Code:

Traceback (most recent call last): division_tuple = tuple(map(int, division.split(","))) AttributeError: 'numpy.int64' object has no attribute 'split'

I have also attatched an image of what the cell would look like with one division, if there are multiple, it would be the same but like "92,100,203" etc.

Data:

enter image description here

Upvotes: 1

Views: 91

Answers (3)

Michael Norman
Michael Norman

Reputation: 547

Thank you to Shashwat to helping me fix my Numpy error. After implementing that change, I did not recieve a Numpy error, but a format one. So made the changes below

filename = 'test.csv'
division = test.iloc[0][4]
division_tuple = tuple(map(int, str(division).split(",")))


sql = cs.execute("""
INSERT INTO sample_table_name
select c.customer_id,
c.customer_name
from(
t.customer_id
t.customer_name


from customer_table t

where division_id in (%s) --changed format here 
group by 1,2) c ;
""",(division_tuple)) #and here

Although this would technically make the where clause look like "where division_id in (92,)" when there is a single value, this would still be the correct way to run it despite to trailing comma after 92.

Upvotes: 0

TSnake
TSnake

Reputation: 480

It throws an error: AttributeError: 'numpy.int64' object has no attribute 'split' which means division variable isn't a string, but a numpy.int64.

So, if there are multiple values, it comes as a string. However if there's one value, it is a numpy.int64

As, a workaround, you can change

division_tuple = tuple(map(int, division.split(",")))

to

division_tuple = tuple(map(int, str(division).split(",")))

I've tested it like this:

import numpy
a = numpy.int64(5)
print(str(a).split(','))

Upvotes: 2

TH_SPT
TH_SPT

Reputation: 38

I don't know the type for your division variable but from your error it's call it is not string for .split() it must be string value

So can you try to change division to string object to use with .split().

Upvotes: 1

Related Questions