LEFT function is not working with sqlite3 in python

I am trying to run the following SQL query in python, I have set up a mock database below.

import sqlite3
from datetime import datetime
conn = sqlite3.connect('database.db')

conn.execute("""CREATE TABLE IF NOT EXISTS books (
            title TEXT,
            author TEXT,
            pages INTEGER,
            published INTEGER                
            )""")

   values = ('Deep Learning', 
      'Ian Goodfellow et al.', 
      775, 
      datetime(2016, 11, 18).timestamp())

conn.execute("""INSERT INTO books VALUES (?, ?, ?, ?)""", values)
r = conn.execute("SELECT LEFT(title,2) FROM books")
print(r.fetchall())

However I get the error:

  File "C:/Users/Daniel/Pictures/AllanGray/Revolut/stack.py", line 18, in <module>
    r = conn.execute("SELECT LEFT(title,2) FROM books")
  sqlite3.OperationalError: near "(": syntax error

If I just run a normal basic select statement, it works. What is going wrong with the LEFT function?

1 answer

  • answered 2019-12-09 13:30 forpas

    There is no LEFT() function in SQLite.
    Use substr():

    SELECT SUBSTR(title, 1, 2) FROM books