How to convert the datatype from Integer to Text?

Question

For one Column situation, the the duplicated question with answer is shown in: Parameter substitution for a SQLite "IN" clause

I want to copy data from ColA and ColB of the oldTable into the newTable. The ColA and ColB in oldTable have datatype of INTEGER.

By copying the data, I also want to check if ColA or ColB are a part of a string (in this case, the TextTemp).

My problem is: ColA and ColB have the datatype of INTEGER, and the TextTemp is TEXT format. Different datatype cannot be compared.

Thus the question: How can I convert data in ColA and ColB from INTEGER into TEXT?

Here is my code in Python. The 15, 3, 44 and 9 from ColA or ColB should be copied.

TextTemp = "15 3 44 9"  #String format, but all numbers, with empty spaces
TextTemp = "15 3 44 9".split() #Convert TextTemp into TEXT format
cur.execute('''CREATE TABLE newTable AS  
             SELECT * FROM oldTable 
             WHERE ColA IN (%s)
             OR ColB IN (%s)''' % 
             (','.join('?'*len(TextTemp)), TextTemp)
             (','.join('?'*len(TextTemp)), TextTemp))

The error message: TypeError: not enough arguments for format string

I am pretty sure that the code above is very near its final correct version, but I just don't know how to change it.

PS: I cannot use a for loop, so please help me to solve the problem in the above method.

Do `TextTemp = "15 3 44 9".split()` and follow the instructions in the dupe target. In addition to forming the parameterized query string just pass the split `TextTemp` as the 2nd argument to `cur.execute` as is (it'll be the argument sequence). SQLite is pretty [lenient](https://www.sqlite.org/datatype3.html#comparison_example) when it comes to types, so you should be able to compare them. — Ilja Everilä, Mar 03 '17 at 09:47
the duplicate applies, except you need to use `?` for the actual final placeholder — Antti Haapala -- Слава Україні, Mar 03 '17 at 10:13
@IljaEverilä Thanks for your comment and help. I tried but didnot found the duplicated question. Hier I updated my question, could you please have a look on it? — doglas, Mar 03 '17 at 15:40
@AnttiHaapala Thanks for the comment. I updated the question, could you please have a look on this one? Thanks. — doglas, Mar 03 '17 at 15:45
@IljaEverilä Yes, I am a beginner. Could you tell me how to write the code? Your link is too long for such a short problem. :-) Could you also remove your duplicate mark to this question? Thanks. — doglas, Mar 03 '17 at 19:49
@AnttiHaapala Could you remove your duplicate mark to this question? Because I edited it. Thanks. — doglas, Mar 03 '17 at 19:50

Antti Haapala -- Слава Україні · Accepted Answer · 2017-03-03T20:43:34.287

It is easiest to make placeholders for the query outside the cur.execute. Both of your in queries need n comma separated question marks.

>>> to_find = "15 3 44 9"  # String format, but all numbers, with empty spaces
>>> to_find = to_find.split() # Convert TextTemp into TEXT format
>>> to_find
['15', '3', '44', '9']
>>> placeholders = ','.join('?' * len(to_find))
>>> placeholders
'?,?,?,?'

Then we fill in the query. I will use .format instead of the old-style formatting as it is easier to substitute the same value many times with the new-style formatting:

>>> query = '''CREATE TABLE newTable AS  
               SELECT * FROM oldTable 
               WHERE ColA IN ({0})
               OR ColB IN ({0})'''.format(placeholders)
>>> print(query)
CREATE TABLE newTable AS  
               SELECT * FROM oldTable 
               WHERE ColA IN (?,?,?,?)
               OR ColB IN (?,?,?,?)

As each ? is bound to a distinct parameter from the parameter list and we now have 2 * n placeholders, we need to duplicate the substituted values too, hence tofind * 2:

>>> to_find * 2
['15', '3', '44', '9', '15', '3', '44', '9']
>>> cur.execute(query, to_find * 2)

And here's the code in final compact form

to_find = to_find.split()
placeholders = ','.join('?' * len(to_find))
query = '''CREATE TABLE newTable AS  
           SELECT * FROM oldTable 
           WHERE ColA IN ({0})
           OR ColB IN ({0})'''.format(placeholders)
cur.execute(query, to_find * 2)

Excellent! Thank you so much for your superb answer! It works! I would not even think of these codes. Splendid! Thanks! — doglas, Mar 03 '17 at 21:21

How to convert the datatype from Integer to Text?

1 Answers1