Change datatype of column SQLite3(Python)

Question

I have a database that I am reading with sqlite3 in Python 2.7, using the following command:

# Change to database directory
os.chdir(data)
# Find database file
cur_db = glob.glob('*.db')
# Connect to database
con = sqlite3.connect('database.db')
c = con.cursor()
# Query database
print(len(available_table))
for row in c.execute('SELECT * FROM col1 '):
        print row

which gives me something like:

(1, u'2.3', u'brown', u'0', u'hairy', u'banana', u'2', u'monkey')

I would like to look at values in the column w/ the value u'2.3' greater than 2. But this is a unicode string instead of a number, making it difficult to compare to a number (eg 2).

Ideally, I would like something like:

# Connect to database
con = sqlite3.connect('database.db')
c = con.cursor()
# Query database
c.execute('SELECT * FROM critter WHERE weight > 2'.

QUESTION: How can I add a conditional statement to extract only data rows where this element is greater than 2? I would like to leave the database unaltered.

mechanical_meat · Accepted Answer · 2017-03-15 21:39:10Z

1

Edit: oh you want a query to do that... You can do:

for row in c.execute('select * from critter where cast(weight as numeric) > 2'):
  # do something

Older answer:
If you want to do this on the Python side you can use a try-except construct like so:

for row in c.execute('SELECT * FROM col1'):
  try:
    val = float(row[1]) # number here is the number of the element in the tuple
    if val > 2:
      print(val)
  except ValueError:
    pass

edited Mar 15, 2017 at 21:39

answered Mar 15, 2017 at 21:29

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

3 Comments

AaronJPung Over a year ago

For the question I'd asked, your solution would probably work (I didn't try it). I edited the question to clarify what I am really asking (eg. not using a for loop to extract data from the table.

mechanical_meat Over a year ago

@AaronJPung: ah, I see. Thanks for clarifying. I edited my answer.

AaronJPung Over a year ago

You are a god amongst men. Thank you, your solution works perfectly. I'd never heard/read of using cast in an execute call!

Collectives™ on Stack Overflow

Change datatype of column SQLite3(Python)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related