CODEWITHSUNDEEP
pythonsqlite
damdafayton
damdafayton

Reputation: 2429

python sqlite3 hypen character read problem

I have script that inserts such values to an sqlite database in python:

items = [
['Lawnmower','Tool', 0, 150,'Excellent', '2012‐01‐05'],
['Lawnmower','Tool', 0, 370,'Fair', '2012‐04‐01'],
['Bike', 'Vehicle', 0, 200,'Good', '2013‐03‐22'],
['Drill', 'Tool', 0, 100,'Good', '2013‐10‐28'],
['Scarifier','Tool', 0, 200,'Average', '2013‐09‐14'],
['Sprinkler','Tool', 0, 80,'Good', '2014‐01‐06']
]

this is the method that inserts values into database:

    cur.executemany(item_sql, items)
    db.commit()

and i have a function like this which reads those values but it doesnt show up hypen symbol correctly as it can be seen in the image. Tough the last row which was entered manually shows up correct:

    def get_items():
        query = '''
        select * from item'''
        return cursor.execute(query).fetchall()

python interpreter

but in sqlite software hypen symbol shows up as its supposed to be:

nothing wrong when viewed through sqlite software

Anyone know why and how to fix?

Upvotes: 0

Views: 166

Answers (1)

Shawn
Shawn

Reputation: 52354

@snakecharmerb is on the right track. Those hyphen characters that aren't rendering are U+2010 Hyphen ones, not the similar-looking ASCII range U+002D Hyphen-Minus that should be used if you want to do anything with those dates in Sqlite.

You can fix it with a search and replace in your editor (Which, actually, might be to blame for swapping out normal hyphens with those characters, though if it did that I'd expect the quotes to be changed too), or with something like perl -C24 -pi -e 's/\x{2010}/-/g' yourfile.py. There's probably some python equivalent, but I don't know it well enough to say.

Or you can fix up the existing database with something like

UPDATE item SET DateRegistered = replace(DateRegistered, char(0x2010), '-');

Upvotes: 1

Related Questions