Re: [sqlite] How to insert the BLOB in database?

[FarSight Data Systems]

Thank you.

Mark


On Monday, June 10, 2019 12:53:29 PM Luuk wrote:
> On 10-6-2019 05:08, Mark Halegua wrote:
> > On Monday, June 10, 2019 03:46:02 AM Simon Slavin wrote:
> >> On 10 Jun 2019, at 3:44am, Mark Halegua  
wrote:
> >>> I probably should figure this out, but in a GUI, how do I recover a
> >>> graphic from the database?
> >> 
> >> Programming.  SQLite can't do it since it doesn't even understand that
> >> that
> >> sequence of octets is a graphics.
> >> 
> >> How you do it in programming depends on your development environment and
> >> libraries. ___
> >> sqlite-users mailing list
> >> sqlite-users@mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > 
> > I'm using Python.  What would the programming sequence be to display the
> > graphic in that language?
> > 
> > Mark
> > 
> > mARK
> 
> google for:  python show picture from database sqlite
> 
> https://stackoverflow.com/questions/30818728/retrieve-image-from-sqlite3-dat
> abase-and-directly-display-on-kivy-window
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 

Mark S. Halegua
718-360-1712
917-686-8794
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] passing error messages to pysqlite

[FarSight Data Systems]

Is anyone able to tell me why I'm having this commit problem in python?

Mark


On Saturday, October 04, 2014 11:43:41 AM Mark Halegua wrote:
> Just tried the same code on a different system, using sqlite3 3.8.2
> 
> Same result.
> 
> Mark
> 
> On Saturday, October 04, 2014 10:44:20 AM Mark Halegua wrote:
> > Kieth,
> > 
> > thanks for the info, however, I tested the following code on a LOCAL copy
> > of the database, one where at the sqlite3 command line I was able to do
> > an insert without problems, and yet, the python code returned the
> > 'database is locked; error message in the code (I used a wxpython
> > messagebox widget to display the error).
> > 
> > Why would it return a locked message in this case?
> > 
> > Here's the code:
> > def OnSave(self, event):
> > self.dbupdated = True
> > self.pub_title = self.pub_text.GetValue()
> > self.pub_remarks = self.remarks_text.GetValue()
> > print self.pub_title
> > print self.pub_remarks
> > # update relevant tables(s)
> > self.cdata.execute('insert into publishers(publisher_name,
> > 
> > remarks)  values("test", "remarks");')
> > 
> > try:
> > self.database.commit()
> > 
> > except sqlite.Error, dberr:
> > wx.MessageBox(dberr.args[0], 'DB Warning',
> > 
> > wx.OK)
> > 
> > self.dbupdated = False
> > 
> > # clear fields
> > 
> > if self.dbupdated:
> > self.pub_text.SetValue(' ')
> > self.remarks_text.SetValue(' ')
> > 
> > print 'dbupdated = ' , self.dbupdated
> > print self.add
> > self.add = False
> > print self.add
> > 
> > sqlite3 version 3.7.4
> > 
> > Mark
> > 
> > On Friday, October 03, 2014 10:37:06 PM Keith Medcalf wrote:
> > > Yes.  pysqlite/sqlite3 in python tries to manage transactions for you by
> > > automatically starting them, and you need to commit them yourself.  This
> > > is
> > > controlled by the isolation_level attribute set on the connection (can
> > > also
> > > be set as a parameter when you open the connection).
> > > 
> > > The default value is '' (an empty string).  Other valid values are
> > > 'IMMEDIATE', 'EXCLUSIVE' and None.  Basically, pysqlite tries to detect
> > > the
> > > type of statement you are running and if it is a DML statement (select,
> > > insert, update, delete) it automatically begins a transaction for you if
> > > one is not in progress by first doing a 'BEGIN '+isolation_level.  If
> > > the
> > > statement is something other than DML, then it will 'COMMIT' before
> > > executing the DDL (CREATE/DROP) then it will 'COMMIT' after executing
> > > it.
> > > It does not know about the "WITH" statement, so treats those as DDL
> > > rather
> > > than DML.
> > > 
> > > Setting isolation_level to None tells pysqlite/sqlite3 to not manage
> > > transactions and let the SQLite3 engine do it as it would from the C
> > > interface or the sqlite3.exe command line tool (what the dbapi calls
> > > autocommit mode), so you have to issue your own BEGIN and
> > > COMMIT/ROLLBACK
> > > commands where you want them and the pysqlite/sqlite3 interface modules
> > > will not attempt to begin or commit transactions for you.
> > > 
> > > On Friday, 3 October, 2014 21:48, you wrote:
> > > >Ah, ok.  after doing a database commit I get the error going to stderr.
> > > >Now it's just a matter
> > > >of capturing/redirecting the stderr output and using an except there.
> > > >
> > > >On Friday, October 03, 2014 11:35:08 PM you wrote:
> > > >> the sqlite3 command line doesn't require a commit, it gave an error
> > > >> after the attempted insert command.
> > > >> 
> > > >> pysqlite requires one?
> > > >> 
> > > >> Mark
> > > >> 
> > > >> On Friday, October 03, 2014 09:06:56 PM Keith Medcalf wrote:
> > > >> > Are you committing the change?
> > > >> > 
> > > >> > >-Original Message-
> > > >> > >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> > > >> > >boun...@sqlite.org] On Behalf Of Mark Halegua
> > > >> > >Sent: Friday, 3 October, 2014 20:58
> > > >> > >To: sqlite-users@sqlite.org
> > > >> > >Subject: [sqlite] passing error messages to pysqlite
> > > >> > >
> > > >> > >I have a sqlite3 database.  In the networkied are I have the db is
> > > >
> > > >locked
> > > >
> > > >> > >(wee've
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] passing error messages to pysqlite

[FarSight Data Systems]

the sqlite3 command line doesn't require a commit, it gave an error after the 
attempted 
insert command.

pysqlite requires one?

Mark


On Friday, October 03, 2014 09:06:56 PM Keith Medcalf wrote:
> Are you committing the change?
> 
> >-Original Message-
> >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> >boun...@sqlite.org] On Behalf Of Mark Halegua
> >Sent: Friday, 3 October, 2014 20:58
> >To: sqlite-users@sqlite.org
> >Subject: [sqlite] passing error messages to pysqlite
> >
> >I have a sqlite3 database.  In the networkied are I have the db is locked
> >(wee've
> >discussed this before, and I'm using it mostly on a local machine, but I
> >need to
> >test certain conditions, networking being one).
> >
> >In the sqlite3 command line, when I try to insert new info I get a dabase
> >locked
> >message.
> >
> >Hosever, when I do the same in python with pysqlite, it doesn't return
> >anything
> >and the code proceeds as if nothing happened, and the program just goes
> >on,
> >even though the data wasn't saved/
> >
> >I'm sure some message is being returned to pysqlite, how do I capture it
> >to set up
> >an error trap/condition?
> >
> >Thanks
> >___
> >sqlite-users mailing list
> >sqlite-users@sqlite.org
> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 

Mark S. Halegua
718-360-1712
917-686-8794
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] passing error messages to pysqlite

[FarSight Data Systems]

Ah, ok.  after doing a database commit I get the error going to stderr.  Now 
it's just a matter 
of capturing/redirecting the stderr output and using an except there.  

Thanks.

Maqrk


On Friday, October 03, 2014 09:06:56 PM Keith Medcalf wrote:
> Are you committing the change?
> 
> >-Original Message-
> >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> >boun...@sqlite.org] On Behalf Of Mark Halegua
> >Sent: Friday, 3 October, 2014 20:58
> >To: sqlite-users@sqlite.org
> >Subject: [sqlite] passing error messages to pysqlite
> >
> >I have a sqlite3 database.  In the networkied are I have the db is locked
> >(wee've
> >discussed this before, and I'm using it mostly on a local machine, but I
> >need to
> >test certain conditions, networking being one).
> >
> >In the sqlite3 command line, when I try to insert new info I get a dabase
> >locked
> >message.
> >
> >Hosever, when I do the same in python with pysqlite, it doesn't return
> >anything
> >and the code proceeds as if nothing happened, and the program just goes
> >on,
> >even though the data wasn't saved/
> >
> >I'm sure some message is being returned to pysqlite, how do I capture it
> >to set up
> >an error trap/condition?
> >
> >Thanks
> >___
> >sqlite-users mailing list
> >sqlite-users@sqlite.org
> >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 

Mark S. Halegua
718-360-1712
917-686-8794
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[FarSight Data Systems]

Thanks, I'll keep it in min.  In this case, howevery, I don't think that will 
be an issue.  All of 
the names are from American published pulp magazines,  writers, artists, and 
editors.

Mark

On Friday, September 19, 2014 02:02:30 AM Simon Slavin wrote:
> On 19 Sep 2014, at 1:15am, Mark Halegua  wrote:
> > that resolved it.  I didn't know you needed to put the desc with both
> > columns.
> > 
> > It means another table I had thought was properly ordered wasn't.
> > 
> > Thank you.
> 
> You're welcome.  Glad you figured it out.
> 
> By the way I wanted to warn you about starting any project with first name,
> middle name and last name fields.  This leads to problems, and I would go
> to some lengths to avoid it if possible.  It would be better to provide two
> columns:
> 
> name(their name, however they want it to be shown)
> nameInSortOrder (their name, in whatever order you feel it should be sorted)
> 
> For the second field your name might appear as "Halegua, Mark" and someone
> with a middle name might appear as "Smith, Mark Edward".  The comma is
> needed because some people have a surname which is two separate words, e.g.
> Patrick Nielsen Hayden.  Given the way SQLite works you would want to
> declare the field nameInSortOrder as having COLLATE NOCASE.
> 
> This is especially important if you are storing names which don't all have
> Western-style 'given-name surname' format.  For instance, you may see these
> words between the first and last parts of people's names: "bin", "ben",
> "ibn", "bas", "bat", "O'", "al-", "de" "van de", "Fitz".  They"re not
> middle names.  They mean "son of" or "daughter of" or "from" or other
> things.  They should definitely not be capitalised, except for "O'".  And
> you don't sort on them at all.
> 
> Similarly, surnames beginning with 'Mac' or 'Mc' should not be sorted
> together, not as if the name begins with the letters 'MAC'.  It's a
> convention that they all be sorted at the beginning of the 'M' listings,
> ignoring the difference between "Mc" and "Mac".
> 
> For a longer list of reasons, see this article:
> 
>  mes/>
> 
> and for those who like that, there's a similar
> 
> 
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 

Mark S. Halegua
718-360-1712
917-686-8794
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I'm trying to figure out how to ...

[FarSight Data Systems]

Kieth,

Thanks for the reply.  I should have asked sooner and would have wasted less 
time trying to 
do something sqlite wasn't meant for.  I will however look up rolling cursors.  
That may help 
a little.  Again, thanks.

Mark


On Wednesday, September 17, 2014 11:14:36 PM Keith Medcalf wrote:
> You cannot do any of these things in any relational database.  You can only
> do this in navigational databases.  There are various kludges which permit
> you to simulate navigational abilities on top of relational databases, but
> they are all kludges which are performed by various forms of fakery to
> impose navigability on top of a model which inherently is not navigable.
> 
> For example, many database drivers can kludge you up what is called a
> "keyset" driven cursor.  It does this by executing the query and storing a
> temporary table containing the primary keys of each table in the query for
> each result row (this is stored either in the driver (for a client-driven
> keyset) or on the server (for a server driven keyset).  When you ask for a
> row from the keyset, the primary keys are used to issue a bunch of queries
> to "reconstruct" the "present view" of the result that would be at that
> navigational location for you.  There are also, usually in these same
> drivers, what are called "scrollable" cursors.  These differ from a
> "keyset" cursor in that the query result set is stored in a temporary table
> (rather than a keyset table).  The driver can then "pretend" there is
> ordering and record numbers on the results and can internally reissue
> queries against the temporary table and its row numbers so that it appears
> you can scroll forwards and backwards and access rand om rows of the result
> set (this type of cursor is almost always implemented on the server as a
> temp table and the only information sent to the client are the extents of
> the rowset).  The key difference is that keyset cursors can be used to
> update the database (since you have the primary keys for the original data
> rows stored away) whereas plain scrollable cursors are read only.
> 
> In some systems these types of capabilities exist solely in the drivers.  In
> others, there is inherent support in the database engine itself.  There are
> even cases where there is a combination of both, or where you can select
> whether the support should be implemented server-side or client-side.  In
> some cases the choice of implementation method is taken away from you in
> order to protect you from doing something "abysmal", such as retrieving the
> primary keys for a billion row keyset into driver storage.)
> 
> This is a kludge to give the appearance of navigational capabilities where
> they inherently do not exist.
> 
> The other way to do it is the way primitive folks do it -- retrieve the
> entire result set into a big list of records in your programs storage, and
> then navigate through your list in memory.  This is popular with Microsoft
> tools, for example, and is why most Microsoft tools take aeons to open (try
> to open the event viewer on a busy Microsoft server, or open DSA against a
> domain with a several hundred thousand objects.  You come in to work and
> sign on, then open the tool and lock the screen, then go for breakfast, and
> meetings, and lunch.  When you return after lunch the tool is ready to use.
>  Or it has crashed because it ran out of memory.)
> 
> It is also quite common for "bitty system" developers to do this. 
> Everything works swimmingly well on their test database with 5 customers
> and 3 products.  However, once the "production" database is loaded that
> contains a few thousand customers and several millions of products and
> components, the system craps out or is abysmally slow.  You then read
> stories in the newspaper about how some company (or government) spent
> hundreds of millions or billions of dollars on a failed computer system.
> 
> There are still other products which do not provide drivers which kludge up
> any of these illusions for you, and the database engine does not have the
> baked in complication to assist with the creation of these illusions.  For
> these systems you have to do all the skull-duggery yourself.  SQLite falls
> into this category.  I don't think anyone was written a "driver" which
> implements this in automated fashion either, so you have to "roll your own"
> as it were.
> >I'm racking my brain trying to figure out how to get directly to the last
> >item in a (potentially) sorted or ordered table.  At least oe of the
> >tables will be ordered by a name and a date, so uising the rtowid won't
> >work.
> Read all the results until you run out of results.  At this point the last
> result you successfully retrieved was the last.  If this takes too long
> then,
> >Also, how to traverse a table or cursor in a reverse direction.
> 
> Issue the same query again, and "reverse" the sort order of each column in
> the group by clause.  You will now retrieve the result set in 

Re: [sqlite] sqlite db is locked on network drive

[FarSight Data Systems]

when I try an insert I get this:

Error: database is locked

Mark

On Friday, September 12, 2014 06:57:21 PM Simon Slavin wrote:
> On 12 Sep 2014, at 5:28pm, Mark Halegua  wrote:
> > The db file is stored on a seagate goflex device as my kinda file server. 
> > on that device I can open the db to read but not to write to.
> 
> Do you get an error message ?  Or do your changes just disappear ?
> 
> If it's an error message, what step generates it and what does it say ?
> 
> Simon.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 

Mark S. Halegua
718-360-1712
917-686-8794
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GUI INfo

[FarSight Data Systems]

Max,

I'm rurrently creating an application with sqlite using python and wxpython 
for the gui.  It's a great combo and working out nicely.

Mark


On Tuesday, September 09, 2014 05:06:53 PM Teg wrote:
> Hello Maxine,
> 
> It's totally unrelated to Sqlite though.
> 
> Pick a programming language that works with Sqlite and make a GUI with
> this programming language.
> 
> C
> 
> Wednesday, September 10, 2014, 4:56:39 PM, you wrote:
> 
> MN> I am an experienced Access VBA programmer. I know about the SQLite
> commands MN> to create and manipulate data in tables and queries.
> 
> MN>
> 
> MN> What I want to know is where do I find info on creating a graphical user
> MN> interface such as menus, forms and reports. What additional programs
> are MN> required to do this?
> 
> MN>
> 
> MN> Thanks in advance,
> 
> MN> Max
> 
> MN>
> 
> MN> ___
> MN> sqlite-users mailing list
> MN> sqlite-users@sqlite.org
> MN> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 

Mark S. Halegua
718-360-1712
917-686-8794
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Window functions?

[FarSight Data Systems]

On Monday, August 25, 2014 09:25:47 PM Stephan Beal wrote:
> On Mon, Aug 25, 2014 at 9:17 PM, Petite Abeille 
> 
> wrote:
> > True. But what a quantum leap that would be. Like moving from the
> > wheelbarrow to the jet engine.
> 
> For the small percentage of users who need it (or would even know how to
> apply it). i've been following this list since 2006 or 2007 and i recall
> this topic having come up only a small handful of times, which implies that
> only a small minority of users feels the need for it.

I'm coming at this from a different perspective, as I'm using pysqlite and 
wxpython to display, in a windowed fashion, data in a SQLite database.

Would that solve the problem?

Mark


-- 

Mark S. Halegua
718-360-1712
917-686-8794
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users