Python: convert buffer type of SQLITE column into string

Question

I am new to Python 2.6. I have been trying to fetch date datetime value which is in yyyy-mm-dd hh:m:ss format back in my Python program. On checking the column type in Python I get the error: 'buffer' object has no attribute 'decode'. I want to use the strptime() function to split the date data and use it but I can't find how to convert a buffer to string. The following is a sample of my code (also available here):

conn = sqlite3.connect("mrp.db.db", detect_types=sqlite3.PARSE_DECLTYPES)
cursor = conn.cursor()
qryT = """
    SELECT dateDefinitionTest FROM t
    WHERE IDproject = 4 AND IDstatus = 5
    ORDER BY priority, setDate DESC
"""
rec = (4,4)
cursor.execute(qryT,rec)
resultsetTasks = cursor.fetchall()
cursor.close()  # closing the resultset
for item in resultsetTasks:
    taskDetails = {}
    _f = item[10].decode("utf-8")

The exception I get is:

'buffer' object has no attribute 'decode'

pastie.org/5360165 dateDefinitionTest is a sqlite3 field of datetime — Volatil3
– Volatil3, Commented Nov 11, 2012 at 12:03
Error i get: ** 'buffer' object has no attribute 'decode' ** — Volatil3
– Volatil3, Commented Nov 11, 2012 at 12:04

Pedro Romano · Accepted Answer · 2012-11-11 16:41:08Z

2

I am not exactly sure what your problem may be. The following is a working example of what you are trying to achieve, which hopefully will help you:

#!/usr/bin/env python

import datetime
import sqlite3

conn = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cursor = conn.cursor()
cursor.execute("CREATE TABLE t (dateDefinitionTest DATETIME)")
cursor.execute("INSERT INTO t VALUES (?)", (datetime.datetime.now(),))
query = "SELECT dateDefinitionTest FROM t"
cursor.execute(query)
for row in cursor.fetchall():
    dt = datetime.datetime.strptime(row[0], "%Y-%m-%d %H:%M:%S.%f")
    print(repr(dt))
    print(dt.strftime("%Y-%m-%d %H:%M:%S.%f"))
cursor.close()

which outputs:

datetime.datetime(2012, 11, 11, 16, 40, 26, 788966)
2012-11-11 16:40:26.788966

edited Nov 11, 2012 at 16:41

answered Nov 11, 2012 at 13:11

Pedro Romano

11.2k4 gold badges48 silver badges50 bronze badges

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

9 Comments

Volatil3 Over a year ago

I did same but getting the error: strptime() argument 1 must be string, not buffer . I m using Python 2.6

Volatil3 Over a year ago

What I did, i assigned row[0] in another variable: f = row[0][:] that converted buffer into str and then strptime accepted the value.

Pedro Romano Over a year ago

My example was tested with Python 2.7. It may be that Python 2.6 returns a buffer instead of a string.

Volatil3 Over a year ago

yes seems version issue. Can you please tell how to format datetime.datetime(2012, 11, 11, 13, 9, 29, 141720)? strftime() does not work as it's being returned as tuple

Volatil3 Over a year ago

I guesss I found the reason. I was importing as from datetime import datetime,date, time and i think last import was causing the overriding of some function within datetime. By removing extra module import it's now seems to work

|

Clock ZHONG · Accepted Answer · 2016-09-13 16:12:46Z

Your problem's root cause is considering the 'buffer' object gotten from Sqlite database as a 'string' object, the string object has the encode() method, but 'buffer' object has no this method. What your need do is simple: just convert the 'buffer' object to string object, and it's not difficult, only add one line in your codes:

tempString=str(item[10])

_f = tempString.decode("utf-8")

I encountered the same problem today, and googled directed me to here, and found no suitable answer yet. So provide it here. The sqlite record data's type is buffer or string is decided by how we construct our db table, and the sqlite version, and the sqlite db plugin's version, so you test result is different from Pedro Romano's. But any way, just add this line: tempString=str(item[10]), it could force the system use it as a string.

Collectives™ on Stack Overflow

Python: convert buffer type of SQLITE column into string

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related