Fetchall() on sqllite db returning unicode strings [Python 2.7]

Question

I am running into some issues running fetchall().

I have a table defined like this:

with bookDB:
    bookCur = bookDB.cursor()
    bookCur.execute("CREATE TABLE Books(bookID INTEGER PRIMARY KEY, Title TEXT, Author TEXT, Genre TEXT)")
    bookCur.close()

Now if I want to search the database for books by genre, I have this code:

with bookDB:
        bookCur = bookDB.cursor()
        bookCur.execute("SELECT * FROM Books WHERE Genre=?",(query,)) 
        booklist=bookCur.fetchall()
        book = ''.join(str(i) for i in booklist)
        bookCur.close()
        return book;

I would like to join all the items in the book list to make one string that is assigned to book but when I do this, all the TEXT fields arein the unicode format (u'TEXTHERE') and the front and end parentheses also stay. I know I can use split multiple times or delmitters, but there must be something I am doing wrong. How do I stop this from happening? Any tips?

Here is what I get back from fetchall:

[(133, u'Jurassic Park', u'Crichton', u'Sci-Fi')]

I would like a string like this

133>>Jurassic Park>>Crichton>>Sci-Fi

To send back through a socket.

Thank you very much in advance for any help.

You will have several books in the list, do you want to concatenate them all or return a list already formatted? — Paulo Bu
– Paulo Bu, Commented Mar 18, 2014 at 1:21

Paulo Bu · Accepted Answer · 2014-03-18 01:43:32Z

1

booklist is a list of tuples representing the query columns in the database. By doing book = ''.join(str(i) for i in booklist) you're just concatenating the string representation of the tuples in booklist.

Try something like this:

book = '|'.join(['>>'.join(map(str, row)) for row in booklist])

This will return something like this:

"133>>Jurassic Park>>Crichton>>Sci-Fi|134>>Star Wars>>Crichton>>Sci-Fi"

where >> is the columns separator and | is the row separators. If you need other representation for the separators just change the string literals.

In that code, the inner join will concatenate the tuples (columns) and the outer will concatenate the rows. map(str, row) is a trick to convert the tuple into a list of string objects applying str method to every element in the tuple. It is needed because simply doing join on an iterable that contains non string elements returns a TypeError exception.

Hope this helps!

edited Mar 18, 2014 at 1:43

answered Mar 18, 2014 at 1:16

Paulo Bu

29.9k6 gold badges77 silver badges74 bronze badges

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

2 Comments

cjuffo Over a year ago

Thanks friend, it did work. May I ask you what the significance of map()? What is it doing?

Paulo Bu Over a year ago

@cjuf I'm glad, I'll update the answer to explain map's usage.

Collectives™ on Stack Overflow

Fetchall() on sqllite db returning unicode strings [Python 2.7]

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related