#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    12
    Rep Power
    0

    Question Python sqlite query


    Hi folks,

    Im new to Python (2.7) and to sqlite. I've got a small database that was imported from an Excel csv file. The resultant database is showing all strings as unicode strings, and I can't seem to do a query on them! (I expected to just see ASCII strings, not unicode - does sqlite do that?).

    If I do a query like this:

    cursor.execute('SELECT * FROM database'), I get something like this:

    (u'lname', u'fname', u'eid', u'dept', u'badge', u'acu0', u'acu1')

    I have a badge number in a string variable, and need to query the database to return the contents of 'acu1'.

    I hope I explained that clearly enough, but I just don't know how to do a 'string query' when it's a unicode value. Can someone try to explain what I'm doing wrong? I'd be happy to forget unicode altogether and just use ASCII in the database, but don't know how.

    Any help gratefully appreciated.

    Len
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    Querying unicode values is the same as querying ASCII values. They are unicode strings by default because there is no other way to be certain that the import process won't munge the data in the case that there are non-ASCII characters in the text file. ASCII is a subset of unicode anyway, so even bit-for-bit there is no difference here.

    A basic query just needs to be valid SQL syntax:
    Code:
    SELECT [column_name(s)] FROM [table/expression] WHERE [condition];
    Let's say you want all values from the acu1 column, with no conditions/filtering:
    python Code:
    cursor.execute('SELECT acu1 FROM database')
    acu1_list = cursor.fetchall()

    If you want to get the acu1 value for a single badge number, then you would need to filter the results with a WHERE condition:
    python Code:
    badge_number = 'a1234'
    cursor.execute('SELECT acu1 FROM database WHERE badge = %s', [badge_number])
    those_acu1s = cursor.fetchall()

    Remember that the structure of the tables you're using is dictated by your CREATE TABLE command, so if you declared all the columns to be "text" you won't have any integer values, just strings which may or may not contain numbers.

    Here's an example I ran to make sure I wasn't completely talking out my patootie (sorry for the lack of isolation -- I don't have time to clean it up, so its just a paste from my terminal):
    Code:
    [ceverett@taco sqlite]$ cat << EOF > foo.csv
    > lname,fname,eid,dept,badge,acu0,acu1
    > Everett,Craig,a1234,Homicide,1234,fargle,singer
    > Kahn,Noi,b5678,Narcotics,5678,moogle,graph
    > EOF
    [ceverett@taco sqlite]$ python
    Python 2.6.6 (eviladds, Jun 19 2013, 13:26:42) 
    [GCC 4.4.6]
    Type "help", "copyright", "credits" or "license" for more information.
    >>> import csv, sqlite3
    >>> conn = sqlite3.connect('foo.db')
    >>> c = conn.cursor()
    >>> c.execute('CREATE TABLE foo (lname text, fname text, eid text, dept text, badge integer, acu0 text, acu1 text)')
    <sqlite3.Cursor object at 0x7fd785007240>
    >>> conn.commit()
    >>> f = open('foo.csv')
    >>> dr = csv.DictReader(f)
    >>> f.close()
    >>> dr = csv.DictReader(f)
    >>> to_db = [(x['lname'], x['fname'], x['eid'], x['dept'], x['badge'], x['acu0'], x['acu1']) for x in dr]
    >>> to_db
    [('Everett', 'Craig', 'a1234', 'Homicide', '1234', 'fargle', 'singer'), ('Kahn', 'Noi', 'b5678', 'Narcotics', '5678', 'moogle', 'graph')]
    >>> c.executemany('INSERT INTO foo (lname, fname, eid, dept, badge, acu0, acu1) VALUES (?, ?, ?, ?, ?, ?, ?);', to_db)
    <sqlite3.Cursor object at 0x7f6e28cab3a0>
    >>> conn.commit()
    >>> c.execute('SELECT * FROM foo')
    >>> z = c.fetchall()
    >>> z
    [(u'Everett', u'Craig', u'a1234', u'Homicide', 1234, u'fargle', u'singer'), (u'Kahn', u'Noi', u'b5678', u'Narcotics', 5678, u'moogle', u'graph')]
    >>> c.execute('SELECT acu1 FROM foo WHERE badge = 1234')
    <sqlite3.Cursor object at 0x7f6e28cab3a0>
    >>> z = c.fetchall()
    >>> z
    [(u'singer',)]
    >>> badge_number = 1234
    >>> c.execute('SELECT acu1 FROM foo WHERE badge = ?', [badge_number])
    <sqlite3.Cursor object at 0x7f6e28cab3a0>
    >>> z = c.fetchall()
    >>> z
    [(u'singer',)]
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    12
    Rep Power
    0

    Wink thanks!


    Thanks so much for your help! I'll do some serious pounding on the keyboard over the weekend and figure this out. I really appreciate the explaination and your time.

    Len
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    12
    Rep Power
    0

    easy with some help


    Originally Posted by lgabrielson
    Thanks so much for your help! I'll do some serious pounding on the keyboard over the weekend and figure this out. I really appreciate the explaination and your time.

    Len
    That wasn't hard at all. Just needed a hand. Lots of new syntax! Thanks again.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Location
    Usually Japan when not on contract
    Posts
    240
    Rep Power
    12
    Originally Posted by lgabrielson
    That wasn't hard at all.... Lots of new syntax!
    Larval stage, in a nutshell. (^.^)
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    12
    Rep Power
    0
    Originally Posted by zxq9
    Larval stage, in a nutshell. (^.^)
    nice

IMN logo majestic logo threadwatch logo seochat tools logo