Python: Extract TEXT from sqlite3 as a string

Question

First week programming in any language and need some help: Im trying to extract a TEXT value as a string from sqlite3 in Python. Im getting a value as a list not a str:

system_id = raw_input ("What is your System ID: ")

For example the user enters 'Elmer'

conn = sqlite3.connect("API.db")
c = conn.cursor()
c.execute('''CREATE TABLE IF NOT EXISTS SYSTEMID (systemid TEXT)''')
conn.commit()
conn.close()

conn = sqlite3.connect("API.db")
c = conn.cursor()
system_id = c.execute('select systemid from SYSTEMID').fetchall()
conn.close()

So how to make the variable system_id as a string? If I print from this I get something like [(u, Elmer)], I want system_id = Elmer

its the fetchall() that returns a list of tuples. The problem is not the variable type. Print the current system_id and you will see. Then depending on what you actually need, you can do something like: system_id = system_id[0][0] i guess.. — Ma0
– Ma0, Commented Dec 16, 2016 at 15:40
yeah I half guessed fetchall() was wrong, but how do I express fetchElmer() — Scalextrix
– Scalextrix, Commented Dec 16, 2016 at 15:41
by executing a better query. somehting along the lines of select systemid from SYSTEMID where user = "Elmer" — Ma0
– Ma0, Commented Dec 16, 2016 at 15:42
Ok I dont know hat the value will be in advance, but this has worked: system_id = str(system_id[0][0]) — Scalextrix
– Scalextrix, Commented Dec 16, 2016 at 15:52

Community · Accepted Answer · 2017-05-23 12:13:54Z

1

As pointed out by Ev. Kounis, fetchall() by default will return a list of tuples with length equal to the variables specified.

In your case you are getting a tuple of length 1, because you asked for a single variable.

The u in front of the string 'Elmer' is also returned by default when getting strings (this just means it is a Unicode String).

There are two things you should do:

Get rid of u by changing text_factor of the connection to str like this:
```
con.text_factory = str
```
Accessing first element of the tuple to only get the string 'Elmer':

fetch = c.execute('select systemid from SYSTEMID').fetchall()
system_id = fetch[0][0] # First element in first tuple in list

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Dec 16, 2016 at 15:51

Carles Mitjans

4,8663 gold badges22 silver badges39 bronze badges

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

1 Comment

Scalextrix Over a year ago

Thanks, I will try, my less elegant solution was system_id = str(system_id[0][0])

Collectives™ on Stack Overflow

Python: Extract TEXT from sqlite3 as a string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest