SQL Python, handling one or many values in Where Statement

Question

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:

TSnake · Accepted Answer · 2021-05-22 10:59:18Z

2

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(','))

edited May 22, 2021 at 10:59

answered May 22, 2021 at 10:51

TSnake

4783 silver badges10 bronze badges

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

Comments

TH_SPT · Accepted Answer · 2021-05-22 09:45:45Z

1

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().

answered May 22, 2021 at 9:45

TH_SPT

386 bronze badges

Comments

Michael Norman · Accepted Answer · 2021-05-22 18:39:00Z

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.

Collectives™ on Stack Overflow

SQL Python, handling one or many values in Where Statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related