CODEWITHSUNDEEP
pythonsqlsqldatatypes
william tepe
william tepe

Reputation: 47

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

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.

enter image description here

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

Upvotes: 0

Views: 1828

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269683

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.

Upvotes: 1

apomene
apomene

Reputation: 14389

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

Upvotes: 0

Related Questions