Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: How to use my own python function in a SQLite WHERE clause?

  1. #1
    Join Date
    Sep 2013
    Beans
    24

    How to use my own python function in a SQLite WHERE clause?

    I've been struggling to get this to work yet not successful, would highly appreciate some advice:
    I have my own python module to represent some Chinese calendar dates. I use sqlite3 to persist data. All the dates are converted to string before being stored by sqlite3. Now, I want to query the database with a WHERE caluse, for example:
    SELECT * FROM table1 WHERE dates > MyOwnModule.ChineseDate('兔年八月十五')
    I got a sqlite operation error.


    How can I ever use my own functions in a SQLite query? If I can't do queries, what's the point of using a database to persist??? Please give me some advice, many many thanks!

  2. #2
    Join Date
    Jun 2009
    Location
    SW Forida
    Beans
    Hidden!
    Distro
    Kubuntu

    Re: How to use my own python function in a SQLite WHERE clause?

    Does SQLite support the Chinese character set, or do you have to turn that on. And it may not be a date function where you can use > ?

    This works in one of my queries.
    where hist_date = '2011-12-31' and (basis_date > '2011-12-31' or not NULL)

    and in another I convert a date
    where ((strftime('%m', ty.hist_date) = '12' and strftime('%Y',ty.hist_date) = '2012' )

    But I think my queries work as that is the supported date functions.
    UEFI boot install & repair info - Regularly Updated :
    https://ubuntuforums.org/showthread.php?t=2147295
    Please use Thread Tools above first post to change to [Solved] when/if answered completely.

  3. #3
    Join Date
    Sep 2013
    Beans
    24

    Re: How to use my own python function in a SQLite WHERE clause?

    Quote Originally Posted by oldfred View Post
    Does SQLite support the Chinese character set, or do you have to turn that on. And it may not be a date function where you can use > ?

    This works in one of my queries.
    where hist_date = '2011-12-31' and (basis_date > '2011-12-31' or not NULL)

    and in another I convert a date
    where ((strftime('%m', ty.hist_date) = '12' and strftime('%Y',ty.hist_date) = '2012' )

    But I think my queries work as that is the supported date functions.

    Thanks for your reply. I believe it is not Chinese character's problem. I've just ran the program with the Chinese characters replaced by ascii date, and the result was the same.

    No my function is not a date function, it is my own python function written by me. Does it mean I cannot directly use it in a WHERE clause?

  4. #4
    Join Date
    Aug 2013
    Beans
    76
    Distro
    Ubuntu 13.04 Raring Ringtail

    Re: How to use my own python function in a SQLite WHERE clause?

    Quote Originally Posted by Bao_Niu View Post
    No my function is not a date function, it is my own python function written by me. Does it mean I cannot directly use it in a WHERE clause?
    Are you running this inside of a python program/script or trying to query directly?

    Running it in python, I'd do something like that:

    Code:
    chinese_date = MyOwnModule.ChineseDate('兔年八月十五')
    your_cursor.execute('SELECT * FROM table1 WHERE dates > ?', chinese_date)
    Last edited by Kevin_Arnold; October 2nd, 2013 at 10:54 PM.

  5. #5
    Join Date
    Jun 2009
    Location
    SW Forida
    Beans
    Hidden!
    Distro
    Kubuntu

    Re: How to use my own python function in a SQLite WHERE clause?

    SQLite is not as full featured as some of the full SQL implementations that make it easy to create functions.

    I have never tried functions:
    http://webpy.org/cookbook/sqlite-udf
    http://php.net/manual/en/function.sq...e-function.php
    UEFI boot install & repair info - Regularly Updated :
    https://ubuntuforums.org/showthread.php?t=2147295
    Please use Thread Tools above first post to change to [Solved] when/if answered completely.

  6. #6
    Join Date
    Sep 2013
    Beans
    24

    Re: How to use my own python function in a SQLite WHERE clause?

    Quote Originally Posted by Kevin_Arnold View Post
    Are you running this inside of a python program/script or trying to query directly?

    Running it in python, I'd do something like that:

    Code:
    chinese_date = MyOwnModule.ChineseDate('兔年八月十五')
    your_cursor.execute('SELECT * FROM table1 WHERE dates > ?', chinese_date)


    Thanks Kevin, this is a good thought, however, my situation is a bit stranger here.
    "兔年八月十五" is actually a recurring date, so I can't just assign its result to a variable like what you did in your example. I have to pass the whole object into the query and let the sql query trigger the comparison between two objects, not two strings or two dates.

    Maybe I simply can't do this in sqlite3?

  7. #7
    Join Date
    Jul 2007
    Location
    Poland
    Beans
    4,499
    Distro
    Ubuntu 14.04 Trusty Tahr

    Re: How to use my own python function in a SQLite WHERE clause?

    SQL is simple and doesn't know the concept of objects.
    Could you elaborate what exactly you want to achieve? What do you mean by recurring and what that has to do with dates in db?
    if your question is answered, mark the thread as [SOLVED]. Thx.
    To post code or command output, use [code] tags.
    Check your bash script here // BashFAQ // BashPitfalls

  8. #8
    Join Date
    Sep 2013
    Beans
    24

    Re: How to use my own python function in a SQLite WHERE clause?

    Quote Originally Posted by Vaphell View Post
    SQL is simple and doesn't know the concept of objects.
    Could you elaborate what exactly you want to achieve? What do you mean by recurring and what that has to do with dates in db?

    Yes, here is what I'm doing and what I wanted to achieve.
    I coded a module that has a ChineseDate class, which automatically parse Chinese dates characters into a python-dateutil object(which actually is a datetime.datetime object as well).
    I used detect_types=sqlite3.PARSE_DECLTYPES to tell the connection object to store this ChineseDate type directly into one column in my schema. When inserted into that column, the python ChineseDate object will be automatically adapted into a complex string, containing the first occuring date and the interval, etc. Now I want to select all the records with their MyDate column later than a certain 兔年八月十五, so I built this query:
    SELECT * FROM myTable WHERE MyDate > MyModule.ChineseDate("兔年八月十五")
    This comparison is legal in the pure Python context because both sides are a ChineseDate instance. But for some reason I got an sqlite3.operationalError from running it.
    Am I in the wrong path to achieve my goal? Thank you very much.

  9. #9
    Join Date
    Jun 2009
    Location
    SW Forida
    Beans
    Hidden!
    Distro
    Kubuntu

    Re: How to use my own python function in a SQLite WHERE clause?

    I am sure it is against Codd's rules, but could you not have another column with SQLite date as well as Chinese date? It seems your function is converting back & forth already?
    UEFI boot install & repair info - Regularly Updated :
    https://ubuntuforums.org/showthread.php?t=2147295
    Please use Thread Tools above first post to change to [Solved] when/if answered completely.

  10. #10
    Join Date
    Sep 2013
    Beans
    24

    Re: How to use my own python function in a SQLite WHERE clause?

    Quote Originally Posted by oldfred View Post
    I am sure it is against Codd's rules, but could you not have another column with SQLite date as well as Chinese date? It seems your function is converting back & forth already?
    Yes I can, it is only the start of my project so I can easily refactor my code base at this phase. But I'm more interested in the question of "is this kind of in-place comparison of two native python objects supported in sqlite3 WHERE clause"?
    Or it is not supported at all. WHERE A >= B, A and B must be BOTH dates or integers or strings ONLY and no other custom types?

    I'm really eager to hear some pro's advice on this. Thanks a lot!

Page 1 of 2 12 LastLast

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •