I call data from a DB, by a call like

c.execute(""" SELECT smth FROM smth """) c.fetchone()

I get a list of one item, and when

 c.execute(""" SELECT smth FROM smth """) c.fetchall()

I get a tuple from lists one by one. Can I just get a tuple of strings, not lists, within which a string? Can I immediately read the text from the database as strings, and numbers as numbers?

  • Did not understand the problem, fetchone returns the same as fetchall()[0] - andreymal
  • We are not talking about problems with fetches, a problem in typing, is it possible not to read data as lists from one element, but right away the right types, numbers, lines - Pudberg
  • 2
    What for? If you really want, you can always do [x[0] for x in c.fetchall()] and, accordingly, c.fetchone()[0] , and shoving it inside sqlite3 makes little sense - andreymal

2 answers 2

An example using SQL Alchemy:

First create a SQLite DB:

 C:\Temp>sqlite3 c:\temp\a.db SQLite version 3.13.0 2016-05-18 10:57:30 Enter ".help" for usage hints. sqlite> .headers on sqlite> .mode column sqlite> create table tab(name varchar(50), val int); sqlite> insert into tab values ('A', 1), ('A', 2), ('A', 3), ('B', 2), ('B', 4), ('C', 6); sqlite> select * from tab; name val ---------- ---------- A 1 A 2 A 3 B 2 B 4 C 6 sqlite> .exit

Now use SQLAchemy:

 In [101]: from sqlalchemy import create_engine In [102]: engine = create_engine('sqlite:///c:/temp/a.db') In [103]: conn = engine.connect() In [104]: conn.execute('select * from tab').fetchall() Out[104]: [('A', 1), ('A', 2), ('A', 3), ('B', 2), ('B', 4), ('C', 6)]

Strings and integers ( int ) are defined correctly ...

To get a list of strings, instead of a list of tuples:

 In [106]: rows = conn.execute('select name from tab').fetchall() In [107]: rows Out[107]: [('A',), ('A',), ('A',), ('B',), ('B',), ('C',)] In [108]: names = [x[0] for x in rows] In [109]: names Out[109]: ['A', 'A', 'A', 'B', 'B', 'C']

or:

 In [113]: from operator import itemgetter In [114]: list(map(itemgetter(0), conn.execute('select name from tab').fetchall())) Out[114]: ['A', 'A', 'A', 'B', 'B', 'C']

    If the task is to retrieve exactly one field through fetchall() , the result is a list of values ​​for this field from the table.

    You can offer a solution through row_factory :

     import sqlite3 def my_row_factory(cursor, row): ''' Функция будет иметь доступ к каждой строке выборки. cursor содержит метаданные запроса, row содержит данные выборки ''' if len(cursor.description) == 1: return row[0] else: return row con = sqlite3.connect(":memory:") con.row_factory = my_row_factory cur = con.cursor() cur.execute('CREATE table USER(id integer primary key, name text)') cur.execute('INSERT INTO USER(name) values (?)', ('petrushka',)) cur.execute('INSERT INTO USER(name) values (?)', ('barmalei',)) cur.execute('INSERT INTO USER(name) values (?)', ('alien',)) cur.execute('INSERT INTO USER(name) values (?)', ('scally',)) cur.execute('INSERT INTO USER(name) values (?)', ('molder',)) con.commit() cur.execute("select * from USER as a") print(cur.fetchall())

    Description of row_factory in the documentation