passing integer as VARCHAR() datatype in an SQL query from python

Question

The database i am using (WRDS) mainly uses a web form for queries, so for that reason, primary keys in the database are stored as VARCHAR(6) despite being integers.

My query looks like this - select gvkey from comp.sec_dprc where gvkey in (12142, 12141, 12138, 1300, 1722, 12635, 12850, 3144);

Since this query is just a string passed to a function in python, i have no control over the datatype of the list of keys, so they are being read as numeric. This means my query does not find any matches.

I would like to be able to specify the varchar(6) datatype from the query, if possible . I tried;

cast((12142, 12141, 12138, 1300, 1722, 12635, 12850, 3144) as varchar(6)

and

(cast(12142 as varchar(6), cast(12141as varchar(6), cast(12138 as varchar(6), etc)

So far, i have not had any success. I have had even less success with WRDS customer support. If there is as solution i am missing, i would be absolutely ecstatic to find it.

Cheers

Tag your question with the database you are using. Also show what the data looks like in the tables. — Gordon Linoff
– Gordon Linoff, Commented Jan 24, 2020 at 13:45

Gordon Linoff · Accepted Answer · 2020-01-24 13:47:26Z

1

If you know the values are numbers, then convert the column to a number:

where cast(gvkey as int) in (12142, 12141, 12138, 1300, 1722, 12635, 12850, 3144)

The syntax and best approach might differ by database, but the idea is the same.

Note: You can get conversion errors if you are wrong about the contents being only digits.

answered Jan 24, 2020 at 13:47

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Comments

apomene · Accepted Answer · 2020-01-24 13:47:12Z

0

Why not go the opposite way and specify inside IN clause types to be VARCHAR?. Try like:

select gvkey from comp.sec_dprc where gvkey 
in ('12142', '12141', '12138', '1300', '1722', '12635', '12850', '3144')

answered Jan 24, 2020 at 13:47

apomene

14.4k10 gold badges51 silver badges76 bronze badges

1 Comment

william tepe Over a year ago

An excellent thought, but this did not work for some reason... It was one of the first things i tried

Collectives™ on Stack Overflow

passing integer as VARCHAR() datatype in an SQL query from python

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related