CODEWITHSUNDEEP
pythonsqlite
doglas
doglas

Reputation: 115

How to convert the datatype from Integer to Text?

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.

Upvotes: 1

Views: 133

Answers (1)

Antti Haapala -- Слава Україні
Antti Haapala -- Слава Україні

Reputation: 133909

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)

Upvotes: 2

Related Questions