Re: 'Lite' Databases (Re: sqlite3 and dates)
On 02/21/2015 01:22 AM, Ned Deily wrote: SQLite is one of the most widely-used, best-documented, best-tested, and well-respected software packages in the world. yes but is still sql. there are a couple of small scale not-sql databases that look interesting. problem with them is that the creator seem to subscribe to the build-and-toss-into-the-wild school of development. http://buzhug.sourceforge.net/ http://www.pydblite.net/en/index.html both are useful, both could use multi-writer support, and both need some love from the python world. --- eric -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
Am 20.02.2015 um 15:16 schrieb Dennis Lee Bieber: The middle-ground is probably something like the embedded version of Firebird (pity there has been no updated book -- "The Firebird Book" came out in 2004, v1.5 while 2.5 is current [Whoops, looks like there /is/ an update, print-on-demand in three overpriced volumes]) The second edition as PDF EBook is part of the IBPhoenix DVD (Developer Edition $100). But I can't find out from the website what that DVD contains in its current edition. It used to have Firebird itself and quite a lot of additional software and documentation. Firebird embedded has no network access and multi-user capabilities at all if I read the documentation correctly (http://www.firebirdsql.org/manual/ufb-cs-embedded.html). But "A Firebird embedded server DLL can also be used as a network client" to a regular Firebird server (from the same page). On the other hand triggers and stored procedures should work exactly like the client/server versions (and the procedure language is pretty similar to PostgreSQL, so migration between these two isn't too difficult). So this is a middle ground with other pros and cons than SQLite versus PostgreSQL. -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
Ned Deily writes: >> Same reason lots of people have forked Postgres. Or you might just want >> to customize it. > Well, for whatever reason one might have, one can: it's public domain > software. Yes, but unlike with most FOSS software, your version has much lower quality assurance than the "official" version because you don't have the big test suite. Even if you don't fork or change the code at all, but you just port it to a new platform or compiler, you really should run the full set of tests, but you can't. > It seems like [the SQLite consortium] was an approach Richard Hipp > and major users of SQLite took to ensure a sustaining funding model > for the project while ensuring its independence. The main benefit of the consortium seems to be very close an intense support from the SQLite core developers, including customization and porting services. Consortium membership apparently starts at $75K a year so I doubt anyone joins just to get the test suite. There is some mention of separate licenses just for the test suite but I didn't see a price tag and I wonder if that generates significant revenue compared to the consortium. > given the immense good that the SQLite project has done for so many > other projects over the years. Maybe I should look into it more. I've never particularly felt the need for it since I've either used client/server databases or else simpler embedded databases like bsddb or even flat files. I actually think Macid/Happstack-state is brilliant but I haven't used it yet. > Many other less important projects have foundered for lack of > sustained funding. Other FOSS databases like Postgres, MySQL, MongoDB, Riak, Cassandra, etc. all seem to be doing fine. GNAT Ada (GCC-based Ada compiler and surrounding ecosystem) is split into free and proprietary components and you have to pay pretty big to get the proprietary parts, but the free parts aren't hobbled in any way (like missing tests) AFAIK. -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
In article <871tljepea@jester.gateway.pace.com>, Paul Rubin wrote: > Ned Deily writes: > > (though I don't know why anyone would want to fork it). > > Same reason lots of people have forked Postgres. Or you might just want > to customize it. Well, for whatever reason one might have, one can: it's public domain software. > > I imagine that is done as an incentive to help > > finance the on-going development and maintenance of SQLite. > It's a pretty unusual and annoying trick that other projects have not > felt they had to resort to. > > > https://www.sqlite.org/testing.html > > Thanks, that's the page I remember. The TH3 test suite is the > interesting one and you can't get it without paying a lot of $. I > guess there is some semantic quibble possible about whether you pay for > the test suite, or (as they put it) pay for Consortium membership and > then (as a member) get the test suite for free. I don't have any special knowledge of the history or current status of the Consortium but it's not difficult to find information and blog posts about it. It seems like this was an approach Richard Hipp and major users of SQLite took to ensure a sustaining funding model for the project while ensuring its independence. It strikes me as a very reasonable and modest constraint given the immense good that the SQLite project has done for so many other projects over the years. Many other less important projects have foundered for lack of sustained funding. https://www.sqlite.org/consortium.html https://blog.lizardwrangler.com/2008/02/27/the-sqlite-consortium/ > Do you know the situation with the SQL Logic Test (SLT) also mentioned > on that page? No -- Ned Deily, n...@acm.org -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
Ned Deily writes: > (though I don't know why anyone would want to fork it). Same reason lots of people have forked Postgres. Or you might just want to customize it. > I imagine that is done as an incentive to help > finance the on-going development and maintenance of SQLite. It's a pretty unusual and annoying trick that other projects have not felt they had to resort to. > https://www.sqlite.org/testing.html Thanks, that's the page I remember. The TH3 test suite is the interesting one and you can't get it without paying a lot of $. I guess there is some semantic quibble possible about whether you pay for the test suite, or (as they put it) pay for Consortium membership and then (as a member) get the test suite for free. Do you know the situation with the SQL Logic Test (SLT) also mentioned on that page? -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
In article <54e7b0da.7060...@stoneleaf.us>, Ethan Furman wrote: > On 02/20/2015 01:17 PM, Paul Rubin wrote: > > > SQLite always seemed bloated (from the embedded NoSQL point of view) and > > fragile to me, and the vendor plays an annoying anti-forking trick, > > which is that the code is released but the developers' test suite is > > secret and proprietary (can be licensed from them for big bucks). > Wow, really? I had just started playing with it, but I don't think I'll > bother now. SQLite is one of the most widely-used, best-documented, best-tested, and well-respected software packages in the world. It is used all over by the place on many different platforms. The code, documentation, and some of the tests are in the public domain, freely usable and forkable by all (though I don't know why anyone would want to fork it). It is true that part of the test suite is only released free to SQLite consortium members. I imagine that is done as an incentive to help finance the on-going development and maintenance of SQLite. http://en.wikipedia.org/wiki/SQLite https://www.sqlite.org/about.html https://www.sqlite.org/testing.html https://www.sqlite.org/copyright.html -- Ned Deily, n...@acm.org -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 2015-02-20 13:17, Paul Rubin wrote: > For stuff like browser bookmarks or other typical embedded database > purposes, I don't see why SQL or relations are needed. Berkeley DB > is a transactional key-value store that's been around for decades > and is way simpler than SQLite, and there's other things like that > too. Well, for Steven's purposes, both fail ungracefully on network shares. From my understanding, BDB fails even harder (whereas the sqlite doesn't so much fail as keep a locked write transaction locked if it fails at the wrong time, so you have to force an unlock) And all said, sqlite doesn't add that much overhead compared to the many other libraries I've used that aren't nearly so well-tested. -tkc -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On Sat, Feb 21, 2015 at 8:17 AM, Paul Rubin wrote: > Ben Finney writes: >> I don't know of a free-software concurrent RDBMS which can be considered >> lighter than that. (No, MySQL doesn't count; its concurrency is >> *unreliable* and it commonly loses data silently. Don't use MySQL.) > > I thought they fixed MySQL transactions years ago, with the InnoDB > engine. For some reason it's not the default, so you have to turn it on > explicitly: is there more to it than that? Even if you use InnoDB for all of _your_ tables, the system catalog tables will all be MyISAM. So it's possible to lose critical metadata. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 02/20/2015 01:17 PM, Paul Rubin wrote: > SQLite always seemed bloated (from the embedded NoSQL point of view) and > fragile to me, and the vendor plays an annoying anti-forking trick, > which is that the code is released but the developers' test suite is > secret and proprietary (can be licensed from them for big bucks). Wow, really? I had just started playing with it, but I don't think I'll bother now. -- ~Ethan~ signature.asc Description: OpenPGP digital signature -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 20/02/2015 21:17, Paul Rubin wrote: Ben Finney writes: I don't know of a free-software concurrent RDBMS which can be considered lighter than that. (No, MySQL doesn't count; its concurrency is *unreliable* and it commonly loses data silently. Don't use MySQL.) I thought they fixed MySQL transactions years ago, with the InnoDB engine. For some reason it's not the default, so you have to turn it on explicitly: is there more to it than that? For stuff like browser bookmarks or other typical embedded database purposes, I don't see why SQL or relations are needed. Berkeley DB is a transactional key-value store that's been around for decades and is way simpler than SQLite, and there's other things like that too. I thought I recognised the name, so a quick search and found it was deprecated in Python 2.6, removed from 3.0. Supported bindings available here https://www.jcea.es/programacion/pybsddb.htm for anybody who's interested. SQLite always seemed bloated (from the embedded NoSQL point of view) and fragile to me, and the vendor plays an annoying anti-forking trick, which is that the code is released but the developers' test suite is secret and proprietary (can be licensed from them for big bucks). So if you want to make your own version of SQLite you have to either pay for the test suite, or have much less reliability assurance for your patched version than the vendor has for their version. Add that Sqlite is written in C (think of naked whirling razor blades) and you've got a pretty serious disincentive against modification. Thanks for the above. I've been meaning to take a look at how SQLite is tested for months if not years. That saves me the trouble :) -- My fellow Pythonistas, ask not what our language can do for you, ask what you can do for our language. Mark Lawrence -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
Ben Finney writes: > I don't know of a free-software concurrent RDBMS which can be considered > lighter than that. (No, MySQL doesn't count; its concurrency is > *unreliable* and it commonly loses data silently. Don't use MySQL.) I thought they fixed MySQL transactions years ago, with the InnoDB engine. For some reason it's not the default, so you have to turn it on explicitly: is there more to it than that? For stuff like browser bookmarks or other typical embedded database purposes, I don't see why SQL or relations are needed. Berkeley DB is a transactional key-value store that's been around for decades and is way simpler than SQLite, and there's other things like that too. SQLite always seemed bloated (from the embedded NoSQL point of view) and fragile to me, and the vendor plays an annoying anti-forking trick, which is that the code is released but the developers' test suite is secret and proprietary (can be licensed from them for big bucks). So if you want to make your own version of SQLite you have to either pay for the test suite, or have much less reliability assurance for your patched version than the vendor has for their version. Add that Sqlite is written in C (think of naked whirling razor blades) and you've got a pretty serious disincentive against modification. -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 02/19/2015 02:23 PM, Mario Figueiredo wrote: > On Thu, 19 Feb 2015 12:26:04 -0800 (PST), ru...@yahoo.com wrote: >> >> I'll point out that five people in this thread (by my >> count) have said that Postgresql requires a significant >> amount of work to setup and use. Only you and Steven claim >> the opposite. > > Well, I claim the opposite too. > >> Or I could have tried as you >> and Steven suggest to "somehow" package Postgresql in my >> app installer. That would have been an even bigger cost >> in my time with an uncertain outcome. > > I don't see how. You said it was just a simple application this > postgres database was serving. So I must assume you aren't talking of > a complex setup that database synchronization or other features that > force a manual setup. Correct. > For your purposes, you could just basically backup your data folder > and package postgres with the same configuration files you have in > your current computer. What do you mean by "data folder"? The directory in which Postgresql keeps its database data? If so you're wrong. That data is at a minimum architecture dependent. It is also private to Postgresql and one would be ill advised to use that as a distribution format. There is also no need to since installing Postgresql and loading the initial data from some standard format is supported and will produce the same results. Or are you talking about some sort of data (csv files, sql files of insert statements, etc) that you use to initially load tables in your database. That will be the same whether you are using Postgresql or Sqlite so I don't see your point. >> None of those >> costs would have been necessary at all had I developed >> a self-contained Sqlite app. > > The cost would have probably been much higher, depending on your > project. SQLite would have forced you to move all your business logic > into your code, greatly increasing code maintenance, your application > extensibility and its ability to more easily adapt to new business > requirements. How so? nobody's claimed that Sqlite is a replacement for Postgresql in large scale, high concurrency "heavy-duty" applications. Ethan Furman posted a list of things that Sqlite does well from the Sqlite website. Here is a link if you missed it: http://www.sqlite.org/whentouse.html We are talking about using Postgresql as a backend for applications that fit on that list, ie applications like the applications already using Sqlite :-) >From previous posts I am guessing that what you are saying is that business logic should be implemented in stored procedures and because Sqlite does not offer stored procedures you can't implement business logic in Sqlite. First, recall that we are not talking about a multi-tiered set of applications with a middleware layer and mutiuser backend. We've established already that is the domain of servers like Postgresql. While I tend to agree with the idea that business logic should be in the database, I'm sure you're aware that that is not a universally held opinion and it is certainly not a universally implemented one in the domain of applications we're talking about. It is easy to find plenty of applications that implement all or part of the business logic in the app. Given Sqlite's architecture I'm not even sure you can say it doesn't have stored procedures. Since programmatic access is through the (python-)API you can write a module with all the business logic and decree that that all higher level functions access the database through that module. How is that effectively different than a set of stored procedures in a client-server database? And I can see a justification for not even going that far in some cases. We are talking about small scale applications where the database can be considered an "implementation detail" of the application but it is easier to implement the application by taking advantage of the capabilities of a SQL relational database than trying to implement those storage details in some ad-hoc way. > Conversely, if none of this is true concerning your particular > project, then you just chose the wrong tool. Postgres was overkill for > your particular needs and it was a mistake to think you need it to > function just as a shelve on steroids. Bingo! You're catching on. :-) Again I remind you that no one has said that Sqlite is a universal replacement for Postgresql >> Finally keep in mind that if you develop your app using >> Sqlite, it is likely to be far easier to migrate to >> a heavy-duty backend like Postgresql later should you >> need to than to go in the other direction when you find >> out you didn't really need Postgresql after all and the >> cost turned out to be higher than you expected. > > Completely not true! For the reasons mentioned above. You are > concentrating too much on the RDBMS aspects and completely forgetting > about the implications in your codebase. > > Whether you move from a non d
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 02/19/2015 01:47 PM, Chris Angelico wrote: > On Fri, Feb 20, 2015 at 7:26 AM, wrote: >> I'll point out that five people in this thread (by my >> count) have said that Postgresql requires a significant >> amount of work to setup and use. Only you and Steven claim >> the opposite. (And it sounds to me like Steven does not >> have a lot of experience installing, configuring and >> maintaining Postgresql -- please correct me if I am wrong >> Steven.) > > I don't know about Steven, but I do have a certain amount of > experience installing, configuring, and maintaining; Right. I am aware of that as you might have guessed by the fact that I directed my question to Steven and not to you. :-) > [...] the most common changes I've needed to make to Postgres configs are: >[...] > 2) Increasing logging, eg turning on the logging of all queries. Not > particularly common, but can be handy; and it's a trivial edit anyway. It may be trivial to you but it is not going to be trivial to many of end users the kind of apps we're talking about. One of the people interested in my little app was at the limits of her ability when it came to finding a PowerPoint file she'd just been editing so she could attach it to an email. So if the logging level needs changing, the app better be prepared to do it itself. >[...] > So while it makes sense to talk about *experience* "installing, > configuring, and maintaining", it's really just the installing part > that actually demands effort. No, see below. > And that can be mitigated in a few ways, > but ultimately, there's no single simple solution for everything. That's right but misses the obvious: some solutions are simpler than others. Installing a Postgresql client-server database and a Python application is inherently more complex than installing a Python application (Sqlite being included in Python). (Doesn't that seem rather obvious, even to you?) And, if using Postgresql is as easy as you claim, surely you don't expect to reserve its use only to your applications? So we could expect to see many applications, potentially all those currently using Sqlite, to start using Postgresql. Do they all install and run their own independent server? Each with its own set of more than a half dozen processes running all the time? Each that has to be started up at system boot? Each with their own directory of Postgresql software and backend data directory? Where do these files all go and how do app developers agree on some standard to avoid chaos? How does each app find a network port or socket that doesn't conflict with the others or with things the machine owner may run? For a Sqlite app you are installing files for which the rules are all pretty well established. And if the Postresql apps share a server who arbitrates when app X decides it needs to set some parameter that breaks app Y? Or change anything else on the server; things shared in common by all the apps like users or (as you yourself mentioned) log file settings? The complexities you deny exist do exist and have to be dealt with somewhere. Either you, the developer have to handle them in the installation and anticipate problems like "Install error: /var/lib/pgsql/data: already exists" or "data directory contain wrong version of Postresql" or you shove the task off onto your users in which case they will have to have sufficient expertise to deal with them. Install problems are not of course limited to server software, they occur with simple Python apps too. But installing a single Python app that depends on the version of Sqlite in the minimally supported version of Python from a self-contained installer will have far lower probability of problems than an automated install of server software in a very uncontrolled environment. And whatever that app install failure probability is, requiring server software in addition can only increase it. > Even > SQLite3 has issues with that - as soon as you have two programs that > try to read the same file (eg your application, using Python's module, > and the sqlite3 command line tool), you have version differences, and > quite a number of my students have come to me asking why on earth they > can't upgrade their SQLite3 properly. (Though usually both versions > are "new enough", so it doesn't matter. It's just a point of > confusion.) Irrelevant. As the application designer you are responsible for what you provide (the application and its backend database). If I want to obtain a command line tool somewhere, it's my problem to worry about versions -- it has nothing to do with you. If you mean that you as a developer have to worry about not shipping two incompatible versions of app code, well... yeah. I think that's pretty much a standard part of putting together a working application. Or is there some other point I missed? As for no maintenance, I mentioned two common maintenance activities in my reply to Steven: backups and server u
Re: 'Lite' Databases (Re: sqlite3 and dates)
On Thu, 19 Feb 2015 12:26:04 -0800 (PST), ru...@yahoo.com wrote: > >I'll point out that five people in this thread (by my >count) have said that Postgresql requires a significant >amount of work to setup and use. Only you and Steven claim >the opposite. Well, I claim the opposite too. >Or I could have tried as you >and Steven suggest to "somehow" package Postgresql in my >app installer. That would have been an even bigger cost >in my time with an uncertain outcome. I don't see how. You said it was just a simple application this postgres database was serving. So I must assume you aren't talking of a complex setup that database synchronization or other features that force a manual setup. For your purposes, you could just basically backup your data folder and package postgres with the same configuration files you have in your current computer. >None of those >costs would have been necessary at all had I developed >a self-contained Sqlite app. The cost would have probably been much higher, depending on your project. SQLite would have forced you to move all your business logic into your code, greatly increasing code maintenance, your application extensibility and its ability to more easily adapt to new business requirements. Conversely, if none of this is true concerning your particular project, then you just chose the wrong tool. Postgres was overkill for your particular needs and it was a mistake to think you need it to function just as a shelve on steroids. > >Finally keep in mind that if you develop your app using >Sqlite, it is likely to be far easier to migrate to >a heavy-duty backend like Postgresql later should you >need to than to go in the other direction when you find >out you didn't really need Postgresql after all and the >cost turned out to be higher than you expected. Completely not true! For the reasons mentioned above. You are concentrating too much on the RDBMS aspects and completely forgetting about the implications in your codebase. Whether you move from a non distributed model to a client-server model, or the other way around, your code will suffer major changes. And even if you decide to keep the business logic in the client layer (which is a mistake) when moving from SQLite to a client-server RDBMS, you will still have to deal with a whole new set of issues regarding the very nature of concurrent access that will essentially force you to scrap much of your previous code. >It is bad advise to recommend using Postgresql without >regard to the developer's actual needs. Naturally. But I must say postgres isn't the bad cat you painted in your post. It's much, much easier to distribute, deploy and manage than you are suggesting. For most systems where performance and database synchronization aren't a requirement, it can be entirely automated, I know, because that's how we had it set up on three schools where we sold our integrated management system. I haven't had a maintenance request call in 8 months. It's only under critical requirements that postgres necessitates a baby sitter. And those projects don't suffer from lack of competent administrators. -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On Wednesday, February 18, 2015 at 10:39:04 PM UTC-7, Ethan Furman wrote: > On 02/18/2015 09:26 PM, memilanuk wrote: > > On 02/18/2015 09:16 PM, Ben Finney wrote: > >> memilanuk writes: > >> > >>> In the past I've been waffling back and forth between a desktop > >>> client/server setup, or a web-based interface with everything on one > >>> computer. At this point I'm leaning toward the latter. > >> > >> So, it's been many exchanges back and forth, and you still aren't > >> telling us what specific needs you have that SQLite can't provide. At > >> this point I'm just going to have to wait until you can lay out the > >> specifics. > >> > > > > Okay, let me put it like this: if I set up a web interface using Flask for > > the front-end, and SQLite as the backend DB, > > running from a PC/laptop, with anywhere from 1 to 10 people doing data > > entry from other devices (laptops, desktops, > > tablets, etc.) at roughly the same time, is SQLite going to be 'concurrent' > > enough? > > Well, having zero experience with SQLite, but having read the docs just today > [snip snide remark] -- I think you'll be > fine with SQLite under those conditions. :) I too agree with this (with a similar caveat -- I've only used Sqlite for playing around and have no real experience using it in real-world applications). Another thing to keep in mind is if you do need find you need a more heavy duty backend, it will be easier to migrate from Sqlite to Postgresql (or whatever) [*] than to go the other way should you later find Postgresl too heavy-weight and costly. [*] Unless you are very careful to use only features in Postgresql that you've determined can be easily migrated back to Sqlite. In practice very few people have the strength of character required to do this. :-) -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On Fri, Feb 20, 2015 at 7:26 AM, wrote: > I'll point out that five people in this thread (by my > count) have said that Postgresql requires a significant > amount of work to setup and use. Only you and Steven claim > the opposite. (And it sounds to me like Steven does not > have a lot of experience installing, configuring and > maintaining Postgresql -- please correct me if I am wrong > Steven.) I don't know about Steven, but I do have a certain amount of experience installing, configuring, and maintaining; and the biggest thing I've noted about the "configuring" part is that you can ignore it most of the time. The docs caution you that the defaults are deliberately NOT set for maximum performance... but you won't normally need maximum performance initially anyway. (In any case, "maximum performance" requires a lot of knowledge of workloads, resource availability, concurrency model, etc.) The defaults are usually good enough; the most common changes I've needed to make to Postgres configs are: 1) Weakening the default security model of listening only on localhost, to allow other computers on the network to connect to the database. Obviously it's correct for the default to be secure, here; anyone who wants to allow remote access will need to be aware of what s/he is doing anyway, so requiring that a couple of files be edited isn't a big deal. 2) Increasing logging, eg turning on the logging of all queries. Not particularly common, but can be handy; and it's a trivial edit anyway. 3) Permitting certain special-purpose users to bypass the normal login mechanisms, eg for scripted backups. Similarly to #1, the defaults are absolutely correct, and if you want to weaken your security, it's only fitting that you have to go in and edit some configs. Given that I frequently don't need to do _any_ of these, it's safe to say that configuring PostgreSQL doesn't take a lot of effort at all. Similarly, "maintaining" isn't really a thing; apart from maintaining my own data tables (cleaning out junk tables that were for one quick test, which I often forget to drop when I'm done with them), I don't have to spend any effort making sure the database keeps running. (In contrast, a busy MySQL server probably needs to have myisamchk run on it periodically, which *does* count as maintenance.) So while it makes sense to talk about *experience* "installing, configuring, and maintaining", it's really just the installing part that actually demands effort. And that can be mitigated in a few ways, but ultimately, there's no single simple solution for everything. Even SQLite3 has issues with that - as soon as you have two programs that try to read the same file (eg your application, using Python's module, and the sqlite3 command line tool), you have version differences, and quite a number of my students have come to me asking why on earth they can't upgrade their SQLite3 properly. (Though usually both versions are "new enough", so it doesn't matter. It's just a point of confusion.) ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 02/19/2015 12:23 AM, Chris Angelico wrote:
> On Thu, Feb 19, 2015 at 6:07 PM, Steven D'Aprano
> wrote:
>> Very possibly. With modern dependency management, it isn't hard to install
>> Postgresql:
>>
>> sudo aptitude postgresql
>>
>> or equivalent should work. For primitive operating systems with no
>> dependency management available, Firefox could come with a simple script
>> which downloads, installs, configures and runs Postgresql. (Everything is
>> simple for the guy who doesn't have to do it.)
>
> Definitely a possibility. I'm pretty sure I've seen that exact thing
> done by one application (on Windows; on a typical Linux system,
> that'll be done by simple dependency management - your package
> metadata says "depends on postgresql" and apt-get or yum or whatever
> will do the work), and it's quite common for a Windows installer
> wizard to go through a series of subcomponents (grab the .NET
> framework, grab these hotfixes that the program depends on, grab some
> adware toolbar that you forgot to untick, and *then* install the
> program you asked for).
>
>> Possible snags:
>>
>> - Possibly postgresql is simply *too big*. Your 50MB(?) Firefox
>>turns into a 2GB install. I doubt it -- on Debian, postgresql
>>is 17MB installed. But I don't know what dependencies I'm not
>>counting.
>
> Yeah. And frankly, I would be surprised if Firefox is only 50MB these
> days. The extra dent of PostgreSQL won't be all that significant - and
> don't forget that the SQLite3 dent can be removed, so you're talking
> about the difference between them, plus you can omit a whole bunch of
> PG's ancillaries.
>
>> - Or it is impossible to configure without excessive amounts of
>>tech-savvy human intervention. Again, I doubt it. I seem to
>>recall needing to create a Postgresql user and password. But
>>maybe even that is too technical for the average Firefox user.
>
> You don't even need to do that. An absolutely default Postgres on
> Debian or Ubuntu is ready to use, with peer authentication. If you can
> become root, you can then drop privs to the 'postgres' user and
> connect to the database that way.
>
> I don't know if it's possible to do a non-root installation of
> PostgreSQL, but if it isn't today, it could easily be tomorrow, if
> someone puts in a little effort. You'd miss out on boot-time startup,
> and it'd probably have to do some password-based authentication (with
> autogenerated passwords), but it certainly could work. The Windows
> equivalent ("install for just me") is, I think, already possible.
>
>> - Maybe there are nasty interactions between Postgresql listening
>>on some port and Windows firewall wanting to block that same port.
>
> That's definitely an issue, given that Windows doesn't have Unix
> domain sockets. But I'm sure it's a solvable problem. How does IDLE
> cope with firewall issues?
There's an awful lot of "could"s, "possible"s etc in your
and Steven's responses. And most of those words apply to
issues that aren't problems at all when using Sqlite.
I'll point out that five people in this thread (by my
count) have said that Postgresql requires a significant
amount of work to setup and use. Only you and Steven claim
the opposite. (And it sounds to me like Steven does not
have a lot of experience installing, configuring and
maintaining Postgresql -- please correct me if I am wrong
Steven.)
I've used Postgresql for a number of small to medium size
projects for work and personal use. I too think it is an
amazing piece of work for free software. Consequently I
used it for a small educational app I developed for my
personal use. A number of friends saw it, liked it and
asked about using it themselves. Unfortunately none of
these friends were at all technical and the barrier of
installing and configuring Postresql would have involved
me in trying to talk them through it on the phone (risky
as there were OSs I was unfamiliar with involved) or do
it myself in person (one of them was 2000mi away) or do
it remotely (again a cost in coming up with and installing
remote desktop software on different OSs and non-technical
doubts about its use ("I don't mind you doing this while
I'm siting beside you but remote access is a different
story"). Then there are the ongoing maintenance issues
I mentioned in my last reply to Steven.
None of these would have been impossible to overcome with
more effort and expense. Or I could have tried as you
and Steven suggest to "somehow" package Postgresql in my
app installer. That would have been an even bigger cost
in my time with an uncertain outcome. None of those
costs would have been necessary at all had I developed
a self-contained Sqlite app.
Finally keep in mind that if you develop your app using
Sqlite, it is likely to be far easier to migrate to
a heavy-duty backend like Postgresql later should you
need to than to go in the other direction when you find
out you didn't really need Postgr
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 02/19/2015 12:07 AM, Steven D'Aprano wrote: > ru...@yahoo.com wrote: >> On 02/18/2015 07:13 PM, Steven D'Aprano wrote: >>> Chris Angelico wrote: > SQLite misses some important features that makes it better suited as a > simple datastore, not much unlike shelve. And network use is not one > of them, since you can actually implement concurrent sqlite access by > coding an intermediate layer. Assuming of course we are talking about > a small number of concurrent users. This is what I was saying: it's fine for purposes like Firefox's bookmarks and settings and such (which I think was what it was originally developed for?). Not so fine over a network. >>> >>> The sheer number of Firefox bugs related to its use of SQLite says >>> different. >>> >>> Once upon a time, Firefox's config, bookmarks, etc. were stored in plain >>> text files. At worst they were HTML. You could trivially read them, copy >>> them, restore them and even (if you were careful) edit them using the >>> text editor of your choice. Many a time I was on one machine, wanted to >>> know a bookmark from another machine, so I would ssh across to the other >>> machine and run grep over the bookmark file. >> >> I agree, I prefer plain text files whenever practical. But since >> the original discussion was about Sqlite vs Postgresql, not Sqlite >> vs text files, shouldn't the question be: would Firefox be better >> if it required you to install and configure Postgreql instead of >> using Sqlite? > > Very possibly. With modern dependency management, it isn't hard to install > Postgresql: > > sudo aptitude postgresql > > or equivalent should work. And the equivalent for Android would be? Even with Windows, Mac and Linux I don't imagine they want to be in the business of tracking what package managers are used by what OSes and OS version and dealing with the inevitable problems that will arise, particularly with a userbase that is 99.9% non- technical. Compare with Sqlite which require zero end-user involvement and puts the management in one place, in-house, and under control of the developers. > For primitive operating systems with no > dependency management available, Firefox could come with a simple script > which downloads, installs, configures and runs Postgresql. (Everything is > simple for the guy who doesn't have to do it.) Right. The key is in the last sentence above. > Possible snags: > [...] > > - Or it is impossible to configure without excessive amounts of >tech-savvy human intervention. Again, I doubt it. I seem to >recall needing to create a Postgresql user and password. But >maybe even that is too technical for the average Firefox user. You want hands-off for non-technical users and hands-on for technical ones. The last thing I want is yet another database server running -- if I want FF to use Postgresql, I want it to use a database in my existing server for which I already have management and backup/recovery procedures established. So now you have to ask me if I want to use an existing server or not and if so what its connection details are. And this is not per-install but per profile creation. My point is not that this is an insoluble problem -- just that it is one of many such problems that take some non-trivial amount of time recognize and to address -- time which is not necessary when using Sqlite. > - Maybe there are nasty interactions between Postgresql listening >on some port and Windows firewall wanting to block that same port. The attack surface for any application that has a network port is vastly greater that one that uses only filesystem apis like sqlite. So you've now just undertaken to be responsible to a much greater degree for the security of my machine. FF does already have a bucketful of security issues but as a client, not as a server. And what about backing up all that data in the wonderful, featureful database you just installed for me? As you know, you can't just include the postresql data files in a file system backup. And what about upgrades to the server? Are you going to develop your own upgrade infrastructure, or are you going to tell me to use the standard Postgresql upgrade methodology (which involves, in simple cases, dumping the data with pg_dumpall, doing the upgrade, and restoring with pg_restore_all). If the latter, I'm sure that will be really popular with non-technical users. Of course with sqlite, you just ship a new shared library file with your new version of Firefox. > Or... and here is a radical thought... maybe Firefox could give you the > choice of which database? By default, it might use Sqlite, to satisfy the > desktop users who don't want to think about it. And for those who are > disturbed by the fragility of Sqlite on a network home directory, you just > set a config setting in about:config to point at your existing Postgresql > instance, and never need worry about it again. Yes, I
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 02/19/2015 09:03 AM, Tim Chase wrote: > On 2015-02-19 15:04, Mark Lawrence wrote: >> On 19/02/2015 14:17, Tim Chase wrote: > Parameterized queries is just a pet peeve of mine that I wish to > include here. SQLite misses it and I miss the fact SQLite misses > it. The less SQL one needs to write in their code, the happier > one should be. Instead, use the DB-API's parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor's execute() method. (Other database modules may use a different placeholder, such as %s or :1.) For example:..." >>> >>> I think Mario was referring to what other back ends call prepared >>> statements. >> >> Is this >> https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executemany >> an equivalent? > > Depends on whether sqlite3.Cursor.executemany() uses > > https://www.sqlite.org/c3ref/stmt.html > > under the hood. So it seems that Sqlite does have prepared statements -- they are just accessible from the api and not from SQL. Regarding Mark's question I would say that a more significant difference is that executemany() requires you to know all the bind parameter values at the time the statement is executed. A prepared statement does not require you to know any of the bind parameter values when you prepare the statement -- they are supplied to the prepared statement at any later time or times when you execute the prepared statement. -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 2015-02-18 20:05, ru...@yahoo.com.dmarc.invalid wrote: > Sqlite offers concurrent access already. > What Sqlite doesn't offer is high performance concurrent write > access. That is, it locks the entire database for the duration > of a write operation. Given that most such operations are pretty > short, for a small number of concurrent writers this is not a > big problem. Though the entire-database-lock, as Steven mentions in another section of this thread, causes issues on network-shared file-systems. -tkc -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 2015-02-19 15:04, Mark Lawrence wrote: > On 19/02/2015 14:17, Tim Chase wrote: Parameterized queries is just a pet peeve of mine that I wish to include here. SQLite misses it and I miss the fact SQLite misses it. The less SQL one needs to write in their code, the happier one should be. >>> >>> Instead, use the DB-API’s parameter substitution. Put ? as a >>> placeholder wherever you want to use a value, and then provide a >>> tuple of values as the second argument to the cursor’s execute() >>> method. (Other database modules may use a different placeholder, >>> such as %s or :1.) For example:..." >> >> I think Mario was referring to what other back ends call prepared >> statements. > > Is this > https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executemany > an equivalent? Depends on whether sqlite3.Cursor.executemany() uses https://www.sqlite.org/c3ref/stmt.html under the hood. -tkc -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On Fri, Feb 20, 2015 at 2:04 AM, Mark Lawrence wrote: >> This saves the SQL processor from recompiling the SQL into internal >> byte-code every time. It's handy if you know a given query will run >> multiple times with the same "shape" parameters. It's not essential, >> and some optimize away the need, but many back-end interfaces support >> it. >> >> -tkc >> > > Is this > https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executemany an > equivalent? That's a different feature, and also useful. Personally, I've never used executemany() for anything other than INSERT statements, though I can imagine using it equally for UPDATE. It's useful only when you have a bulk lot to do all at once; you can't take advantage of it to repeat a common and complex query. Imagine you run a web server that shows some statistical information about your session, on every page; this may require a complex query, which you could retain from one page request to another. But that only matters to performance. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 19/02/2015 14:17, Tim Chase wrote: On 2015-02-19 05:32, Mark Lawrence wrote: On 19/02/2015 00:08, Mario Figueiredo wrote: Parameterized queries is just a pet peeve of mine that I wish to include here. SQLite misses it and I miss the fact SQLite misses it. The less SQL one needs to write in their code, the happier one should be. Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.) For example:..." I think Mario was referring to what other back ends call prepared statements. So you do something like sql = "..." # parameters are referenced here conn = sqlite3.connect(...) stmt = conn.prepare(sql) for parameters in list_of_parameters: stmt.execute(*parameters) This saves the SQL processor from recompiling the SQL into internal byte-code every time. It's handy if you know a given query will run multiple times with the same "shape" parameters. It's not essential, and some optimize away the need, but many back-end interfaces support it. -tkc Is this https://docs.python.org/3/library/sqlite3.html#sqlite3.Cursor.executemany an equivalent? -- My fellow Pythonistas, ask not what our language can do for you, ask what you can do for our language. Mark Lawrence -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 2015-02-19 05:32, Mark Lawrence wrote: > On 19/02/2015 00:08, Mario Figueiredo wrote: > > Parameterized queries is just a pet peeve of mine that I wish to > > include here. SQLite misses it and I miss the fact SQLite misses > > it. The less SQL one needs to write in their code, the happier > > one should be. > > Instead, use the DB-API’s parameter substitution. Put ? as a > placeholder wherever you want to use a value, and then provide a > tuple of values as the second argument to the cursor’s execute() > method. (Other database modules may use a different placeholder, > such as %s or :1.) For example:..." I think Mario was referring to what other back ends call prepared statements. So you do something like sql = "..." # parameters are referenced here conn = sqlite3.connect(...) stmt = conn.prepare(sql) for parameters in list_of_parameters: stmt.execute(*parameters) This saves the SQL processor from recompiling the SQL into internal byte-code every time. It's handy if you know a given query will run multiple times with the same "shape" parameters. It's not essential, and some optimize away the need, but many back-end interfaces support it. -tkc -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On 2015-02-18, Chris Angelico wrote: > On Thu, Feb 19, 2015 at 9:17 AM, wrote: >>> SQLite3 is fine for something that's basically just a more structured >>> version of a flat file. You assume that nobody but you has the file >>> open, and you manipulate it just the same as if it were a big fat blob >>> of JSON, but thanks to SQLite, you don't have to rewrite the whole >>> file every time you make a small change. That's fine. >> >> That's bullshit. Sqlite offers a lot more than that including >> a SQL interface, transactions, referential integrity, constraints >> indexes, triggers and other general relational database features. >> >> That you would equate that to a JSON blob would indicate either >> a profound ignorance about Sqlite or (more likely) a need to >> defend your preference with complete disregard of fact. > > I didn't equate them. I said that SQLite3 is great if you look on it > as an upgrade over a JSON blob. Of course it offers more features than > that, and you don't need to swear at me to make your point. > > But SQLite3 is *not* great if you look on it as a database engine > comparable with DB2, PostgreSQL, and even MySQL. I certainly agree with that bit, but in my own code I can almost never justify the hassle (set-up, security considerations, &c.) of using a database server. TBH, one reason I like SQLite3 is that I can easily move the data file around in the filesystem or between machies. -- "It is the role of librarians to keep government running in difficult times," replied Dramoren. "Librarians are the last line of defence against chaos." (McMullen 2001) -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On 2015-02-18, Johannes Bauer wrote: > On 18.02.2015 12:21, Chris Angelico wrote: > >> SQLite3 is fine for something that's basically just a more structured >> version of a flat file. You assume that nobody but you has the file >> open, and you manipulate it just the same as if it were a big fat blob >> of JSON, but thanks to SQLite, you don't have to rewrite the whole >> file every time you make a small change. That's fine. But it's the >> wrong tool for any job involving multiple users over a network, and >> quite probably the wrong tool for a lot of other jobs too. > > Your assessment that some tools fit certain problems and don't fit > different problems is entirely correct. SQLite does the job that it is > supposed to do and it fills that nieche well. > >> It's the >> smallest-end piece of software that can truly be called a database. I >> would consider it to be the wrong database for serious accounting >> work, and that's based on the ranting of a majorly-annoyed accountant >> who had to deal with issues in professional systems that had made >> similar choices in back-end selection. > > It probably is the wrong database for serious accounting work, and it's > probably also the wrong database for doing multivariate statistical > analysis on sparse matrices that you store in tables. > > You could similarly argue that a hammer is the wrong tool to drive in a > screw and you'd be correct in that assessment. But it's completely > besides the point. "If your only tool is a hammer, every problem looks like a nail." ;-) -- In the 1970s, people began receiving utility bills for -£999,999,996.32 and it became harder to sustain the myth of the infallible electronic brain. (Verity Stob) -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On Thu, 19 Feb 2015 03:43:36 +, Mark Lawrence wrote: >After a wonderful relationship lasting many happy years I dumped Firefox >a few weeks ago for Chrome. A few anxious moments gave me pause for >thought, but overall I'm happy to have changed. However is anybody >aware of a "new kid on the block" that could take over as I'd happily >switch again? Nothing has sprung out at me, hence the choice I made. Despite being built on Chrome framework, Vivaldi seems like an interesting option. Made by the original makers of Opera who have grown annoyed at the direction their browser took. When they opened Vivaldi beta to the public a few weeks ago, it gained good reviews a little all over the web. https://vivaldi.com/ -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On Thu, 19 Feb 2015 18:22:57 +1300, Gregory Ewing wrote: > >How does sqlite3 miss parameterized queries? It supports >DB-API parameter subsitution with '?' according to the >docs. It's actually parameterized views that I meant. Not queries. SQLite misses the ability to write parameterized views. -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
Ben Finney wrote: > What's the difference betwen a “requirement” and a “major requirement”? "I require a gold-plated Mercedes, if I can have it for less than $30,000. My major requirement is for some sort of personal transport." *wink* -- Steve -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On Thu, Feb 19, 2015 at 6:07 PM, Steven D'Aprano
wrote:
> Very possibly. With modern dependency management, it isn't hard to install
> Postgresql:
>
> sudo aptitude postgresql
>
> or equivalent should work. For primitive operating systems with no
> dependency management available, Firefox could come with a simple script
> which downloads, installs, configures and runs Postgresql. (Everything is
> simple for the guy who doesn't have to do it.)
Definitely a possibility. I'm pretty sure I've seen that exact thing
done by one application (on Windows; on a typical Linux system,
that'll be done by simple dependency management - your package
metadata says "depends on postgresql" and apt-get or yum or whatever
will do the work), and it's quite common for a Windows installer
wizard to go through a series of subcomponents (grab the .NET
framework, grab these hotfixes that the program depends on, grab some
adware toolbar that you forgot to untick, and *then* install the
program you asked for).
> Possible snags:
>
> - Possibly postgresql is simply *too big*. Your 50MB(?) Firefox
> turns into a 2GB install. I doubt it -- on Debian, postgresql
> is 17MB installed. But I don't know what dependencies I'm not
> counting.
Yeah. And frankly, I would be surprised if Firefox is only 50MB these
days. The extra dent of PostgreSQL won't be all that significant - and
don't forget that the SQLite3 dent can be removed, so you're talking
about the difference between them, plus you can omit a whole bunch of
PG's ancillaries.
> - Or it is impossible to configure without excessive amounts of
> tech-savvy human intervention. Again, I doubt it. I seem to
> recall needing to create a Postgresql user and password. But
> maybe even that is too technical for the average Firefox user.
You don't even need to do that. An absolutely default Postgres on
Debian or Ubuntu is ready to use, with peer authentication. If you can
become root, you can then drop privs to the 'postgres' user and
connect to the database that way.
I don't know if it's possible to do a non-root installation of
PostgreSQL, but if it isn't today, it could easily be tomorrow, if
someone puts in a little effort. You'd miss out on boot-time startup,
and it'd probably have to do some password-based authentication (with
autogenerated passwords), but it certainly could work. The Windows
equivalent ("install for just me") is, I think, already possible.
> - Maybe there are nasty interactions between Postgresql listening
> on some port and Windows firewall wanting to block that same port.
That's definitely an issue, given that Windows doesn't have Unix
domain sockets. But I'm sure it's a solvable problem. How does IDLE
cope with firewall issues?
ChrisA
--
https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
ru...@yahoo.com wrote: > On 02/18/2015 07:13 PM, Steven D'Aprano wrote:> Chris Angelico wrote: SQLite misses some important features that makes it better suited as a simple datastore, not much unlike shelve. And network use is not one of them, since you can actually implement concurrent sqlite access by coding an intermediate layer. Assuming of course we are talking about a small number of concurrent users. >>> >>> This is what I was saying: it's fine for purposes like Firefox's >>> bookmarks and settings and such (which I think was what it was >>> originally developed for?). Not so fine over a network. >> >> The sheer number of Firefox bugs related to its use of SQLite says >> different. >> >> Once upon a time, Firefox's config, bookmarks, etc. were stored in plain >> text files. At worst they were HTML. You could trivially read them, copy >> them, restore them and even (if you were careful) edit them using the >> text editor of your choice. Many a time I was on one machine, wanted to >> know a bookmark from another machine, so I would ssh across to the other >> machine and run grep over the bookmark file. > > I agree, I prefer plain text files whenever practical. But since > the original discussion was about Sqlite vs Postgresql, not Sqlite > vs text files, shouldn't the question be: would Firefox be better > if it required you to install and configure Postgreql instead of > using Sqlite? Very possibly. With modern dependency management, it isn't hard to install Postgresql: sudo aptitude postgresql or equivalent should work. For primitive operating systems with no dependency management available, Firefox could come with a simple script which downloads, installs, configures and runs Postgresql. (Everything is simple for the guy who doesn't have to do it.) Possible snags: - Possibly postgresql is simply *too big*. Your 50MB(?) Firefox turns into a 2GB install. I doubt it -- on Debian, postgresql is 17MB installed. But I don't know what dependencies I'm not counting. - Or it is impossible to configure without excessive amounts of tech-savvy human intervention. Again, I doubt it. I seem to recall needing to create a Postgresql user and password. But maybe even that is too technical for the average Firefox user. - Maybe there are nasty interactions between Postgresql listening on some port and Windows firewall wanting to block that same port. Or... and here is a radical thought... maybe Firefox could give you the choice of which database? By default, it might use Sqlite, to satisfy the desktop users who don't want to think about it. And for those who are disturbed by the fragility of Sqlite on a network home directory, you just set a config setting in about:config to point at your existing Postgresql instance, and never need worry about it again. The Firefox devs surprise and confuse me. On the one hand, they have designed a powerful plug-in architecture, and encourage their user-base to use it for all sorts of amazing functionality that they don't want to build into the core browser. Yay for this. And on the other hand, they are *actively hostile* to any suggestion that using SQlite is not the best and *only* appropriate solution to the problem of storing config, bookmarks and history. A plug-in database architecture would probably work really well. > I don't see any evidence that it is Sqlite that is the problem > as opposed to FF's use (or misuse) of it, or other problems that > are in FF and have nothing to do with Sqlite. No no, even Sqlite devs recommend against using it on network drives. The Firefox problem is that when FF crashes, as it can do, or if you yank the power to the computer and everything dies, if your home directory is on a network drive, the database may be left in a locked state, or even corrupted. Nothing that the FF developers can do, given the choice of Sqlite. > If Sqlite reliably > implements ACID semantics as they claim, Ah, well "reliably" is a tricky word... http://stackoverflow.com/questions/788517/sqlite-over-a-network-share -- Steve -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 02/18/2015 09:26 PM, memilanuk wrote: > On 02/18/2015 09:16 PM, Ben Finney wrote: >> memilanuk writes: >> >>> In the past I've been waffling back and forth between a desktop >>> client/server setup, or a web-based interface with everything on one >>> computer. At this point I'm leaning toward the latter. >> >> So, it's been many exchanges back and forth, and you still aren't >> telling us what specific needs you have that SQLite can't provide. At >> this point I'm just going to have to wait until you can lay out the >> specifics. >> > > Okay, let me put it like this: if I set up a web interface using Flask for > the front-end, and SQLite as the backend DB, > running from a PC/laptop, with anywhere from 1 to 10 people doing data entry > from other devices (laptops, desktops, > tablets, etc.) at roughly the same time, is SQLite going to be 'concurrent' > enough? Well, having zero experience with SQLite, but having read the docs just today [snip snide remark] -- I think you'll be fine with SQLite under those conditions. :) -- ~Ethan~ signature.asc Description: OpenPGP digital signature -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 19/02/2015 00:08, Mario Figueiredo wrote: Parameterized queries is just a pet peeve of mine that I wish to include here. SQLite misses it and I miss the fact SQLite misses it. The less SQL one needs to write in their code, the happier one should be. https://docs.python.org/3/library/sqlite3.html#module-sqlite3 paragraphs seven and eight. "Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see http://xkcd.com/327/ for humorous example of what can go wrong). Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.) For example:..." -- My fellow Pythonistas, ask not what our language can do for you, ask what you can do for our language. Mark Lawrence -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 02/18/2015 09:16 PM, Ben Finney wrote: memilanuk writes: In the past I've been waffling back and forth between a desktop client/server setup, or a web-based interface with everything on one computer. At this point I'm leaning toward the latter. So, it's been many exchanges back and forth, and you still aren't telling us what specific needs you have that SQLite can't provide. At this point I'm just going to have to wait until you can lay out the specifics. Okay, let me put it like this: if I set up a web interface using Flask for the front-end, and SQLite as the backend DB, running from a PC/laptop, with anywhere from 1 to 10 people doing data entry from other devices (laptops, desktops, tablets, etc.) at roughly the same time, is SQLite going to be 'concurrent' enough? -- Shiny! Let's be bad guys. Reach me @ memilanuk (at) gmail dot com -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
Mario Figueiredo wrote: Parameterized queries is just a pet peeve of mine that I wish to include here. SQLite misses it How does sqlite3 miss parameterized queries? It supports DB-API parameter subsitution with '?' according to the docs. -- Greg -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
memilanuk writes: > In the past I've been waffling back and forth between a desktop > client/server setup, or a web-based interface with everything on one > computer. At this point I'm leaning toward the latter. So, it's been many exchanges back and forth, and you still aren't telling us what specific needs you have that SQLite can't provide. At this point I'm just going to have to wait until you can lay out the specifics. -- \ “In economics, hope and faith coexist with great scientific | `\ pretension and also a deep desire for respectability.” —John | _o__)Kenneth Galbraith, 1970-06-07 | Ben Finney -- https://mail.python.org/mailman/listinfo/python-list
Re: When to use SQLite3 [was Re: 'Lite' Databases (Re: sqlite3 and dates)]
On 02/18/2015 08:59 PM, Steve Hayes wrote: > I would summarise it by saying [...] if you want a standalone database on > a single machine, use SQLite. It sounds like SQLite would also work fine if that single-machine scenario was a web-app with not-too-many users trying to write at once. -- ~Ethan~ signature.asc Description: OpenPGP digital signature -- https://mail.python.org/mailman/listinfo/python-list
Re: When to use SQLite3 [was Re: 'Lite' Databases (Re: sqlite3 and dates)]
On Wed, 18 Feb 2015 20:15:30 -0800, Ethan Furman wrote: >At the risk of using actual data, I looked this up at >http://www.sqlite.org/whentouse.html: > > >Checklist For Choosing The Right Database Engine Interesting. A couple of months ago I asked in comp.databases what the differences were between SQLite and MySQL, and I got a lot of uninformative gobbledegook. This was more informative. I would summarise it by saying if you want a multiuser database running on a network, use MySQL. If you want a standalone database on a single machine, use SQLite. -- Steve Hayes from Tshwane, South Africa Web: http://www.khanya.org.za/stevesig.htm Blog: http://khanya.wordpress.com E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 02/18/2015 08:36 PM, Ben Finney wrote: memilanuk writes: They would need to be able to set up the application (and whatever database) on their laptop or PC, wherever that may be, and spend their time administering the event, not the database engine. So, the database will only be accessed by exactly one application, on exactly the same machine and storage as the application? If so, you don't need concurrency. Otherwise, your database needs concurrency; and the person installing the database will need to make a lot of decisions about the specific network environment and devices to be allowed to access the database. But is this what you mean by your requirements not being met by SQLite? In the past I've been waffling back and forth between a desktop client/server setup, or a web-based interface with everything on one computer. At this point I'm leaning toward the latter. -- Shiny! Let's be bad guys. Reach me @ memilanuk (at) gmail dot com -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
memilanuk writes: > They would need to be able to set up the application (and whatever > database) on their laptop or PC, wherever that may be, and spend their > time administering the event, not the database engine. So, the database will only be accessed by exactly one application, on exactly the same machine and storage as the application? If so, you don't need concurrency. Otherwise, your database needs concurrency; and the person installing the database will need to make a lot of decisions about the specific network environment and devices to be allowed to access the database. But is this what you mean by your requirements not being met by SQLite? -- \ “Natural catastrophes are rare, but they come often enough. We | `\ need not force the hand of nature.” —Carl Sagan, _Cosmos_, 1980 | _o__) | Ben Finney -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 02/18/2015 09:09 PM, Ben Finney wrote > memilanuk writes: >[...] > If you want networked access, you need concurrent access and access > permissions, etc. Sqlite has concurrent access. It doesn't have concurrent access that will support a large number of writers or high volume of writes. As for access permissions, it is common, even with Postgresql to do all database access through a single Postgresql user and to implement authorization and access permission in the application. > SQLite does not have concurrent access. Once you require concurrent > access, you need something more complex, like PostgreSQL. Please read https://www.sqlite.org/faq.html#q5 >[...] -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 02/18/2015 08:09 PM, Ben Finney wrote: I have a hard time picturing that few people stressing a modern computer system enough to where SQLite couldn't keep up (thinking web-based interface using Flask or something similar). In the latter case, one of the over-arching priorities is that it be easily distributable, as in that people with relatively little knowledge of a database be able to set it up and run it. Set it up where? Are you hoping that a network-accessible service can be set up without knowledge of the specific concurrent authenticated networked access is needed in each installation? They would need to be able to set up the application (and whatever database) on their laptop or PC, wherever that may be, and spend their time administering the event, not the database engine. Once its set, it shouldn't need any tending, or they are going to be SOL as I wouldn't be able to help them. It may be that Flask + SQLite will be enough; otherwise I foresee a disproportional amount of *my* time will be spent documenting and explaining how to set up and maintain a RDBMS on Windows, on a Mac, etc. Starting to wonder if a pre-configured VM appliance running in Virtualbox might be simpler for the end user to set up and run. -- Shiny! Let's be bad guys. Reach me @ memilanuk (at) gmail dot com -- https://mail.python.org/mailman/listinfo/python-list
When to use SQLite3 [was Re: 'Lite' Databases (Re: sqlite3 and dates)]
At the risk of using actual data, I looked this up at http://www.sqlite.org/whentouse.html: Checklist For Choosing The Right Database Engine * Is the data separated from the application by a network? → choose client/server Relational database engines act as a bandwidth-reducing data filter. So it is best to keep the database engine and the data on the same physical device so that the high-bandwidth engine-to-disk link does not have to traverse the network, only the lower-bandwidth application-to-engine link. But SQLite is built into the application. So if the data is on a separate device from the application, it is required that the higher bandwidth engine-to-disk link be across the network. This works, but it is suboptimal. Hence, it is usually better to select a client/server database engine when the data is on a separate device from the application. * Many concurrent writers? → choose client/server If many threads and/or processes need to write the database at the same instant (and they cannot queue up and take turns) then it is best to select a database engine that supports that capability, which always means a client/server database engine. SQLite only supports one writer at a time per database file. But in most cases, a write transaction only takes milliseconds and so multiple writers can simply take turns. SQLite will handle more write concurrency that many people suspect. Nevertheless, client/server database systems, because they have a long-running server process at hand to coordinate access, can usually handle far more write concurrency than SQLite ever will. * Big data? → choose client/server If your data will grow to a size that you are uncomfortable or unable to fit into a single disk file, then you should select a solution other than SQLite. SQLite supports databases up to 140 terabytes in size, assuming you can find a disk drive and filesystem that will support 140-terabyte files. Even so, when the size of the content looks like it might creep into the terabyte range, it would be good to consider a centralized client/server database. * Otherwise → choose SQLite! For device-local storage with low writer concurrency and less than a terabyte of content, SQLite is almost always a better solution. SQLite is fast and reliable and it requires no configuration or maintenance. It keeps thing simple. SQLite "just works". signature.asc Description: OpenPGP digital signature -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 02/18/2015 07:13 PM, Steven D'Aprano wrote:> Chris Angelico wrote: >>> SQLite misses some important features that makes it better suited as a >>> simple datastore, not much unlike shelve. And network use is not one >>> of them, since you can actually implement concurrent sqlite access by >>> coding an intermediate layer. Assuming of course we are talking about >>> a small number of concurrent users. >> >> This is what I was saying: it's fine for purposes like Firefox's >> bookmarks and settings and such (which I think was what it was >> originally developed for?). Not so fine over a network. > > The sheer number of Firefox bugs related to its use of SQLite says > different. > > Once upon a time, Firefox's config, bookmarks, etc. were stored in plain > text files. At worst they were HTML. You could trivially read them, copy > them, restore them and even (if you were careful) edit them using the text > editor of your choice. Many a time I was on one machine, wanted to know a > bookmark from another machine, so I would ssh across to the other machine > and run grep over the bookmark file. I agree, I prefer plain text files whenever practical. But since the original discussion was about Sqlite vs Postgresql, not Sqlite vs text files, shouldn't the question be: would Firefox be better if it required you to install and configure Postgreql instead of using Sqlite? > No more. Firefox still keeps a bookmark HTML file, but it never seems to be > synced with the actual bookmarks. Settings are stored in an opaque blob, > rather than human-readable text, limiting what you can do with it. It's very > nice that Firefox offers about:config but not so nice that you can't do the > same thing without the GUI running. > > If Firefox crashes, there are failure modes where it can no longer read your > bookmarks, or keep history. I don't mean that history won't persist across > restarts, I mean that *within a single session* it cannot remember what page > you came from so you can hit the Back button and return to it. WTF? > > I swear, if not for the fact that every single other browser is worse, I > would dump Firefox in a second. > > I don't believe for a second that moving to SQlite has anything to do with > performance, because reading and writing preference settings should be rare > and far from a bottleneck. SQlite is simply fragile and unreliable over a > network, and people using their home directory on a network drive are not > that rare. I don't see any evidence that it is Sqlite that is the problem as opposed to FF's use (or misuse) of it, or other problems that are in FF and have nothing to do with Sqlite. If Sqlite reliably implements ACID semantics as they claim, is certainly should be possible to make use of it without the problems you (and I too) see. And there is no reason to believe the situation would be any better with Postgresql. -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On 02/18/2015 04:07 PM, Steven D'Aprano wrote: > ru...@yahoo.com wrote: >> On 02/18/2015 01:14 PM, Ben Finney wrote: >>> Johannes Bauer writes: On 18.02.2015 08:05, Chris Angelico wrote: > But if you need more facilities than SQLite3 can offer, maybe it's > time to move up to a full database server, instead of local files. > Switching to PostgreSQL will give you all those kinds of features, > plus a lot of other things that I would have thought pretty basic - > like ALTER TABLE. It was quite a surprise to learn that SQLite3 didn't > support that. I see you're running a lawnmower. Maybe you should switch to a combine harvester. That'll get you extra features like a reciprocating knife cutter bar. I was quite surprised that regular lawnmowers don't support those. >>> >>> Chris has pointed out one flaw in this analogy; I'll address another. >>> >>> A feature like 'ALTER TABLE' is not equivalent to a "reciprocating knife >>> cutter bar". I'm in agreement that it is a pretty basic SQL feature, and >>> it doesn't appear to conflict with the narrow focus that we all agree is >>> appropriate for SQLite. >> >> No, you and Chris are way off base and Johannes is correct. >> He was pointing out that there are many applications that can >> benefit from a database and a full-blown, bells and whistles >> solution like Postgresql is often overkill in that (very common) >> case. His analogy is quite apt and I wish I'd thought of it. > > > I'm not seeing that at all. Chris explicitly proceeded his comments with the > condition "if you need more facilities than SQLite3 can offer". Right. And did so in a context where there the facility presumed not to be offered was getting a date back from Sqlite. Common sense should tell anyone that it is very improbable that there is no way to get a date out of a sqlite database. Chris' "solution" to that problem? Switch to Postgresql. That was the context for Johannes' analogy. > Johannes' > analogy ignores that and consequently mocks the very idea that anyone might > need more than a regular lawmower -- even a farmer with a thousand acres of > wheat to be harvested. The analogy works precisely because farmers with a thousand acres of wheat to be harvested need reciprocating knife cutter bars. In the same way people dealing with terabytes of data, concurrent updates, enterprise scale data and applications need a Postregsql. People mowing their lawns do not. People without needs for the things that client server database do well, whose problem is not immediately being able to figure out how to get a date, do not. (Did you really need that explained to you?) If you didn't interpret it the way I did (and the way I presume Johannes meant it) then, well, you didn't interpret it the same. Your interpretation (especially given your penchant for sophistry) are not my problem. I am quite happy to let anyone reading make their own evaluation. > Johannes' subsequent posts are more nuanced about Sqlite filling a niche and > not being suitable for everything, but the analogy you're supporting > doesn't. It's an amusing quip, quite funny, but like most quips, lacks > nuance and misrepresents Chris' original position. All analogies are imperfect and thus make easy targets. Shrug. >[...] -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
memilanuk writes: > At this point... I don't think concurrency is going to be a major > requirement for what I have in mind. What's the difference betwen a “requirement” and a “major requirement”? If you want networked access, you need concurrent access and access permissions, etc. SQLite does not have concurrent access. Once you require concurrent access, you need something more complex, like PostgreSQL. > I have a hard time picturing that few people stressing a modern > computer system enough to where SQLite couldn't keep up (thinking > web-based interface using Flask or something similar). In the latter > case, one of the over-arching priorities is that it be easily > distributable, as in that people with relatively little knowledge of a > database be able to set it up and run it. Set it up where? Are you hoping that a network-accessible service can be set up without knowledge of the specific concurrent authenticated networked access is needed in each installation? -- \ “Broken promises don't upset me. I just think, why did they | `\ believe me?” —Jack Handey | _o__) | Ben Finney -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 02/18/2015 05:08 PM, Mario Figueiredo wrote: >[...] > SQLite misses some important features that makes it better suited as a > simple datastore, not much unlike shelve. And network use is not one > of them, since you can actually implement concurrent sqlite access by > coding an intermediate layer. Assuming of course we are talking about > a small number of concurrent users. I think there are some persistent misunderstandings about Sqlite in this thread, Sqlite offers concurrent access already. What Sqlite doesn't offer is high performance concurrent write access. That is, it locks the entire database for the duration of a write operation. Given that most such operations are pretty short, for a small number of concurrent writers this is not a big problem. -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On Thu, Feb 19, 2015 at 2:33 PM, memilanuk wrote: > At this point... I don't think concurrency is going to be a major > requirement for what I have in mind. For one project, only a few people > will be writing to the DB, and only by a stroke of luck would it be at the > same time, and it would be very unlikely that they would be modifying the > same record at the same time due to physical constraints. > > For the other... there may be anywhere from 1-10 (maybe more, but doubtful) > entering data (creating new records for competitors, or entering existing > competitors in a tournament). I have a hard time picturing that few people > stressing a modern computer system enough to where SQLite couldn't keep up > (thinking web-based interface using Flask or something similar). In the > latter case, one of the over-arching priorities is that it be easily > distributable, as in that people with relatively little knowledge of a > database be able to set it up and run it. Both of these need concurrency. You may not need _heavy_ concurrency, but you certainly do need to cope adequately with multiple simultaneous users. Your first case is a perfect example of why you need a database rather than flat files; in fact, you want the granularity of record-level locking rather than table-level. Alas, SQLite3 does not actually offer this (in fact, I'm not sure it even offers table-level locking); once any process begins writing to the database, all others are locked out (even for reading) until it finishes. That's fine if you (a) don't write very often, and (b) don't write very much, but the fact that you're trying to modify different records doesn't help you here. It does with full-scale database systems, where you actually do have record-level locking, but not with SQLite3. Your second case definitely demands concurrency. I've seen tournaments for various games where database-level write locking would be a critical problem, and that with only a couple hundred players and a handful of people keying in data. Of course, it depends how much effort it takes to key that in. If the humans have to enter extensive reports on the tournament results, they'll spend most of their time doing that; but if their job is to quickly say "X beat Y 2-1" and then get back results saying "X plays Z next, Y gets a bye", then you need your database to react quickly, even if three other people are entering results. So it's a huge question of human versus computer workload... but once again, chances are you need record-level locking. It may very well turn out that SQLite3 is entirely capable of the job. But it's certainly not proven by your above statements, and I would start by assuming PostgreSQL by default. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 19/02/2015 02:13, Steven D'Aprano wrote: Chris Angelico wrote: SQLite misses some important features that makes it better suited as a simple datastore, not much unlike shelve. And network use is not one of them, since you can actually implement concurrent sqlite access by coding an intermediate layer. Assuming of course we are talking about a small number of concurrent users. This is what I was saying: it's fine for purposes like Firefox's bookmarks and settings and such (which I think was what it was originally developed for?). Not so fine over a network. The sheer number of Firefox bugs related to its use of SQLite says different. Once upon a time, Firefox's config, bookmarks, etc. were stored in plain text files. At worst they were HTML. You could trivially read them, copy them, restore them and even (if you were careful) edit them using the text editor of your choice. Many a time I was on one machine, wanted to know a bookmark from another machine, so I would ssh across to the other machine and run grep over the bookmark file. No more. Firefox still keeps a bookmark HTML file, but it never seems to be synced with the actual bookmarks. Settings are stored in an opaque blob, rather than human-readable text, limiting what you can do with it. It's very nice that Firefox offers about:config but not so nice that you can't do the same thing without the GUI running. If Firefox crashes, there are failure modes where it can no longer read your bookmarks, or keep history. I don't mean that history won't persist across restarts, I mean that *within a single session* it cannot remember what page you came from so you can hit the Back button and return to it. WTF? I swear, if not for the fact that every single other browser is worse, I would dump Firefox in a second. After a wonderful relationship lasting many happy years I dumped Firefox a few weeks ago for Chrome. A few anxious moments gave me pause for thought, but overall I'm happy to have changed. However is anybody aware of a "new kid on the block" that could take over as I'd happily switch again? Nothing has sprung out at me, hence the choice I made. -- My fellow Pythonistas, ask not what our language can do for you, ask what you can do for our language. Mark Lawrence -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On 19/02/2015 02:48, Steve Hayes wrote: All of which has nothing, absolutely nothing, to do with the OP's question, which said nothing about number of users, but how the software handles dates. Very true, but charging off like this at massive tangents is one of the reasons I love being here. -- My fellow Pythonistas, ask not what our language can do for you, ask what you can do for our language. Mark Lawrence -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On 02/18/2015 04:03 PM, Ben Finney wrote: Is there anything *good* that sits in between the two extremes of SQLite and PostgreSQL? What do you need a RDBMS to do, and what do you not need? The answers to those questions vary hugely between different people (and most people probably don't think too deeply about them). They will determine what “good” means for your case. Is there nothing that amounts to a 'PostgreSQLite'? PostgreSQL itself fits that mould quite well; it is quite capable of serving a small footprint while still offering full concurrency. I don't know of a free-software concurrent RDBMS which can be considered lighter than that. (No, MySQL doesn't count; its concurrency is *unreliable* and it commonly loses data silently. Don't use MySQL.) But perhaps you don't need concurrency? Only you can tell us. At this point... I don't think concurrency is going to be a major requirement for what I have in mind. For one project, only a few people will be writing to the DB, and only by a stroke of luck would it be at the same time, and it would be very unlikely that they would be modifying the same record at the same time due to physical constraints. For the other... there may be anywhere from 1-10 (maybe more, but doubtful) entering data (creating new records for competitors, or entering existing competitors in a tournament). I have a hard time picturing that few people stressing a modern computer system enough to where SQLite couldn't keep up (thinking web-based interface using Flask or something similar). In the latter case, one of the over-arching priorities is that it be easily distributable, as in that people with relatively little knowledge of a database be able to set it up and run it. -- Shiny! Let's be bad guys. Reach me @ memilanuk (at) gmail dot com -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On Wed, 18 Feb 2015 22:21:35 +1100, Chris Angelico wrote: >On Wed, Feb 18, 2015 at 10:11 PM, Johannes Bauer wrote: >> On 18.02.2015 08:05, Chris Angelico wrote: >> >>> But if you need more facilities than SQLite3 can offer, maybe it's >>> time to move up to a full database server, instead of local files. >>> Switching to PostgreSQL will give you all those kinds of features, >>> plus a lot of other things that I would have thought pretty basic - >>> like ALTER TABLE. It was quite a surprise to learn that SQLite3 didn't >>> support that. >> >> I see you're running a lawnmower. Maybe you should switch to a combine >> harvester. That'll get you extra features like a reciprocating knife >> cutter bar. I was quite surprised that regular lawnmowers don't support >> those. > >SQLite3 is fine for something that's basically just a more structured >version of a flat file. You assume that nobody but you has the file >open, and you manipulate it just the same as if it were a big fat blob >of JSON, but thanks to SQLite, you don't have to rewrite the whole >file every time you make a small change. That's fine. But it's the >wrong tool for any job involving multiple users over a network, and >quite probably the wrong tool for a lot of other jobs too. It's the >smallest-end piece of software that can truly be called a database. I >would consider it to be the wrong database for serious accounting >work, and that's based on the ranting of a majorly-annoyed accountant >who had to deal with issues in professional systems that had made >similar choices in back-end selection. > >You're welcome to disagree, but since PostgreSQL doesn't cost any >money and (on Linux at least; can't speak for other platforms) doesn't >take significant effort to set up, I will continue to recommend it. All of which has nothing, absolutely nothing, to do with the OP's question, which said nothing about number of users, but how the software handles dates. -- Steve Hayes from Tshwane, South Africa Web: http://www.khanya.org.za/stevesig.htm Blog: http://khanya.wordpress.com E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On Wed, 18 Feb 2015 08:19:25 +0200, "Frank Millman" wrote: >Hi all > >sqlite3 does not have a DATE type, but the python module does a pretty good >job of providing one - The Rootsmagic genealogy program uses SQLite for its database, I don't know whether or to what extent it uses Python to interac t with the database, but it seems to do a pretty good job of handling dates, calculating ages etc. http://www.rootsmagic.com/ -- Steve Hayes from Tshwane, South Africa Web: http://www.khanya.org.za/stevesig.htm Blog: http://khanya.wordpress.com E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
Chris Angelico wrote: >> SQLite misses some important features that makes it better suited as a >> simple datastore, not much unlike shelve. And network use is not one >> of them, since you can actually implement concurrent sqlite access by >> coding an intermediate layer. Assuming of course we are talking about >> a small number of concurrent users. > > This is what I was saying: it's fine for purposes like Firefox's > bookmarks and settings and such (which I think was what it was > originally developed for?). Not so fine over a network. The sheer number of Firefox bugs related to its use of SQLite says different. Once upon a time, Firefox's config, bookmarks, etc. were stored in plain text files. At worst they were HTML. You could trivially read them, copy them, restore them and even (if you were careful) edit them using the text editor of your choice. Many a time I was on one machine, wanted to know a bookmark from another machine, so I would ssh across to the other machine and run grep over the bookmark file. No more. Firefox still keeps a bookmark HTML file, but it never seems to be synced with the actual bookmarks. Settings are stored in an opaque blob, rather than human-readable text, limiting what you can do with it. It's very nice that Firefox offers about:config but not so nice that you can't do the same thing without the GUI running. If Firefox crashes, there are failure modes where it can no longer read your bookmarks, or keep history. I don't mean that history won't persist across restarts, I mean that *within a single session* it cannot remember what page you came from so you can hit the Back button and return to it. WTF? I swear, if not for the fact that every single other browser is worse, I would dump Firefox in a second. I don't believe for a second that moving to SQlite has anything to do with performance, because reading and writing preference settings should be rare and far from a bottleneck. SQlite is simply fragile and unreliable over a network, and people using their home directory on a network drive are not that rare. -- Steve -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On Thu, Feb 19, 2015 at 11:08 AM, Mario Figueiredo wrote: > I usually think of my relationship with postgre as similar to what I > experienced with Git. At first I was just dumbstruck by the whole > thing and my first reaction was to ignore it and just do version > control as I knew with the tools I knew. But once my brain clicked > into 'Git mode' and I realized its philosophy and its processes, I > immediately recognized the benefits and understood why everyone was > telling me Git was easy to use and highly useful. (Side point: If you're going to treat PostgreSQL the way you'd treat a girlfriend/boyfriend, you should probably be careful of how you address him. "Postgres" or "PostgreSQL", but not usually "Postgre".) This is a quite apt analogy. You have to get your head around some fundamentals, but once you do, life becomes amazing. >>then there is SQLite, which does 99% of what I want it to do other than >>network use. > > SQLite misses some important features that makes it better suited as a > simple datastore, not much unlike shelve. And network use is not one > of them, since you can actually implement concurrent sqlite access by > coding an intermediate layer. Assuming of course we are talking about > a small number of concurrent users. This is what I was saying: it's fine for purposes like Firefox's bookmarks and settings and such (which I think was what it was originally developed for?). Not so fine over a network. Adding an intermediate layer is a lot more effort than you might think. By the time you've gone there, you should be looking at PostgreSQL anyway. I tried to bolt networking support onto a couple of different databasing systems, back in the 90s, and it was faintly ridiculous... I mean, it worked, but if I'd had today's Postgres, I would never have done anything of the sort. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
On Wed, 18 Feb 2015 15:32:36 -0800, memilanuk wrote: > >Is there anything *good* that sits in between the two extremes of SQLite >and PostgreSQL? > >I've tinkered with MySQL years ago (in conjunction with PHP) and was a >little unhappy with some of the things MariaDB is backwards compatible with MySQL and may answer some of the shortcomings. It's a much stronger RDBM in my opinion than MySQL and offers enterprise level features at cost 0. > PostgreSQL, to me, is orders of magnitude harder to set up and > maintain, though. PostgreSQL grows on you. It takes time to mature into a love relationship, like a complicated girlfriend (or boyfriend, whatever floats your boat). But once that relationship grows, you will want to marry with it, have little postgre kids and grow old with it. No other database stands a chance from that moment on. It's just too powerful and too feature rich, to ignore. Only Oracle stands a chance against it, in my humble opinion. And postgre isn't really that hard to setup and maintain. In fact, maintenance can be largely scriptable and 'croned' because the postgre server is so damn stable. Once you familiarize yourself with the process, you just realize it was easy all the time after all. I usually think of my relationship with postgre as similar to what I experienced with Git. At first I was just dumbstruck by the whole thing and my first reaction was to ignore it and just do version control as I knew with the tools I knew. But once my brain clicked into 'Git mode' and I realized its philosophy and its processes, I immediately recognized the benefits and understood why everyone was telling me Git was easy to use and highly useful. >then there is SQLite, which does 99% of what I want it to do other than >network use. SQLite misses some important features that makes it better suited as a simple datastore, not much unlike shelve. And network use is not one of them, since you can actually implement concurrent sqlite access by coding an intermediate layer. Assuming of course we are talking about a small number of concurrent users. Stored procedures is perhaps the most obvious missing feature. Contrary to an opinion I read on the thread that spawned this one, you really should thrive to put the business logic into the database as this permits great simplification of your code and much better adaptability to new requirements. SQLite IS a database. And wants to be used as a database. So despite agreeing SPs would increase SQLite footprint, it's undeniable they could be put to good use. Admittedly these too can be implemented through an intermediate layer. But are much more complex to code. Parameterized queries is just a pet peeve of mine that I wish to include here. SQLite misses it and I miss the fact SQLite misses it. The less SQL one needs to write in their code, the happier one should be. -- https://mail.python.org/mailman/listinfo/python-list
Re: 'Lite' Databases (Re: sqlite3 and dates)
memilanuk writes: > Okay... this might be a question with a blindingly obvious answer, but > I haven't seen any recommendations otherwise so I'll ask anyway ;) > > Is there anything *good* that sits in between the two extremes of > SQLite and PostgreSQL? What do you need a RDBMS to do, and what do you not need? The answers to those questions vary hugely between different people (and most people probably don't think too deeply about them). They will determine what “good” means for your case. > Is there nothing that amounts to a 'PostgreSQLite'? PostgreSQL itself fits that mould quite well; it is quite capable of serving a small footprint while still offering full concurrency. I don't know of a free-software concurrent RDBMS which can be considered lighter than that. (No, MySQL doesn't count; its concurrency is *unreliable* and it commonly loses data silently. Don't use MySQL.) But perhaps you don't need concurrency? Only you can tell us. -- \ “It's up to the masses to distribute [music] however they want | `\… The laws don't matter at that point. People sharing music in | _o__)their bedrooms is the new radio.” —Neil Young, 2008-05-06 | Ben Finney -- https://mail.python.org/mailman/listinfo/python-list
'Lite' Databases (Re: sqlite3 and dates)
On 02/18/2015 02:52 PM, Ethan Furman wrote: Chris also wrote: But SQLite3 is *not* great if you look on it as a database engine comparable with DB2, PostgreSQL, and even MySQL. Sure, the LITE in SQLite means you don't get some things. There is still a huge amount of software that doesn't need concurrency and can benefit from it. Having installed Postgres I can say there is definitely a cost to install it, use it, maintain it, etc... especially if you aren't steeped in it and have to look things up every time you have to make a change (how do I add a user again?). I think the general advice should be: if you are writing a single-user application that happens to need SQL services, check out SQLite; if you are writing a multi-user or concurrent SQL application, check out Postgres. Okay... this might be a question with a blindingly obvious answer, but I haven't seen any recommendations otherwise so I'll ask anyway ;) Is there anything *good* that sits in between the two extremes of SQLite and PostgreSQL? I've tinkered with MySQL years ago (in conjunction with PHP) and was a little unhappy with some of the things it either didn't implement fully (foreign keys) or silently ignored (check constraints). PostgreSQL, to me, is orders of magnitude harder to set up and maintain, though. And then there is SQLite, which does 99% of what I want it to do other than network use. I see other DB names such as DB2, Oracle, MS SQL Server, etc. out there but the only other 'free' one seems to be Firebird? Is that really the only other contender? Is there nothing that amounts to a 'PostgreSQLite'? -- Shiny! Let's be bad guys. Reach me @ memilanuk (at) gmail dot com -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
ru...@yahoo.com wrote: > On 02/18/2015 01:14 PM, Ben Finney wrote: >> Johannes Bauer writes: >>> On 18.02.2015 08:05, Chris Angelico wrote: >>> But if you need more facilities than SQLite3 can offer, maybe it's time to move up to a full database server, instead of local files. Switching to PostgreSQL will give you all those kinds of features, plus a lot of other things that I would have thought pretty basic - like ALTER TABLE. It was quite a surprise to learn that SQLite3 didn't support that. >>> >>> I see you're running a lawnmower. Maybe you should switch to a combine >>> harvester. That'll get you extra features like a reciprocating knife >>> cutter bar. I was quite surprised that regular lawnmowers don't support >>> those. >> >> Chris has pointed out one flaw in this analogy; I'll address another. >> >> A feature like 'ALTER TABLE' is not equivalent to a "reciprocating knife >> cutter bar". I'm in agreement that it is a pretty basic SQL feature, and >> it doesn't appear to conflict with the narrow focus that we all agree is >> appropriate for SQLite. > > No, you and Chris are way off base and Johannes is correct. > He was pointing out that there are many applications that can > benefit from a database and a full-blown, bells and whistles > solution like Postgresql is often overkill in that (very common) > case. His analogy is quite apt and I wish I'd thought of it. I'm not seeing that at all. Chris explicitly proceeded his comments with the condition "if you need more facilities than SQLite3 can offer". Johannes' analogy ignores that and consequently mocks the very idea that anyone might need more than a regular lawmower -- even a farmer with a thousand acres of wheat to be harvested. Johannes' subsequent posts are more nuanced about Sqlite filling a niche and not being suitable for everything, but the analogy you're supporting doesn't. It's an amusing quip, quite funny, but like most quips, lacks nuance and misrepresents Chris' original position. Had Chris said, "SQlite? Pah, don't use that, Postgresql is a much better solution!", the combine harvester analogy would have been much more fair. But he didn't, so it isn't. But fair or not, it has inspired good discussion, so there is that in it's favour. -- Steven -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On Thu, Feb 19, 2015 at 9:17 AM, rurpy wrote: > That you would equate that to a JSON blob [...] Chris wrote: > I didn't equate them. >> Chris wrote earlier: >>> and you manipulate it just the same as if it were a big fat blob >>> of JSON That sure sounds like equating. Chris also wrote: > But SQLite3 is *not* great if you look on it as a database engine > comparable with DB2, PostgreSQL, and even MySQL. Sure, the LITE in SQLite means you don't get some things. There is still a huge amount of software that doesn't need concurrency and can benefit from it. Having installed Postgres I can say there is definitely a cost to install it, use it, maintain it, etc... especially if you aren't steeped in it and have to look things up every time you have to make a change (how do I add a user again?). I think the general advice should be: if you are writing a single-user application that happens to need SQL services, check out SQLite; if you are writing a multi-user or concurrent SQL application, check out Postgres. -- ~Ethan~ signature.asc Description: OpenPGP digital signature -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On Thu, Feb 19, 2015 at 9:17 AM, wrote: >> SQLite3 is fine for something that's basically just a more structured >> version of a flat file. You assume that nobody but you has the file >> open, and you manipulate it just the same as if it were a big fat blob >> of JSON, but thanks to SQLite, you don't have to rewrite the whole >> file every time you make a small change. That's fine. > > That's bullshit. Sqlite offers a lot more than that including > a SQL interface, transactions, referential integrity, constraints > indexes, triggers and other general relational database features. > > That you would equate that to a JSON blob would indicate either > a profound ignorance about Sqlite or (more likely) a need to > defend your preference with complete disregard of fact. I didn't equate them. I said that SQLite3 is great if you look on it as an upgrade over a JSON blob. Of course it offers more features than that, and you don't need to swear at me to make your point. But SQLite3 is *not* great if you look on it as a database engine comparable with DB2, PostgreSQL, and even MySQL. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On 02/18/2015 04:21 AM, Chris Angelico wrote: > On Wed, Feb 18, 2015 at 10:11 PM, Johannes Bauer wrote: >> On 18.02.2015 08:05, Chris Angelico wrote: >> >>> But if you need more facilities than SQLite3 can offer, maybe it's >>> time to move up to a full database server, instead of local files. >>> Switching to PostgreSQL will give you all those kinds of features, >>> plus a lot of other things that I would have thought pretty basic - >>> like ALTER TABLE. It was quite a surprise to learn that SQLite3 didn't >>> support that. >> >> I see you're running a lawnmower. Maybe you should switch to a combine >> harvester. That'll get you extra features like a reciprocating knife >> cutter bar. I was quite surprised that regular lawnmowers don't support >> those. > > SQLite3 is fine for something that's basically just a more structured > version of a flat file. You assume that nobody but you has the file > open, and you manipulate it just the same as if it were a big fat blob > of JSON, but thanks to SQLite, you don't have to rewrite the whole > file every time you make a small change. That's fine. That's bullshit. Sqlite offers a lot more than that including a SQL interface, transactions, referential integrity, constraints indexes, triggers and other general relational database features. That you would equate that to a JSON blob would indicate either a profound ignorance about Sqlite or (more likely) a need to defend your preference with complete disregard of fact. > But it's the > wrong tool for any job involving multiple users over a network, and > quite probably the wrong tool for a lot of other jobs too. Nobody disputes that nor was that the point. The point was that there are many applications that can benefit from use of a database that are NOT distributed multi-user, muilti-tier applications. For many of that very large class of applications Sqlite is a good (and preferable to Postgresql) solution. > It's the > smallest-end piece of software that can truly be called a database. I > would consider it to be the wrong database for serious accounting > work, and that's based on the ranting of a majorly-annoyed accountant > who had to deal with issues in professional systems that had made > similar choices in back-end selection. I consider the program I use for my personal accounting program to be for very serious use since errors could have very grave consequences for me. But a multi-user client-server database is emphatically not needed by it. And I'm sure you're aware that "not for serious use" is a common way that C and Java programmers dismiss Python? So maybe you should try a little harder to come up with real arguments and not rely on cheap and meaningless labels. > You're welcome to disagree, but since PostgreSQL doesn't cost any > money and (on Linux at least; can't speak for other platforms) doesn't > take significant effort to set up, I will continue to recommend it. Postgresql costs a *lot* more -- in setup and on-going maintenance. Not all (or even most) costs are the initial monetary purchase expense. Its an unmoderated newsgroup so feel free to recommend what you want. But counter opinions should be aired so that others a can judge for themselves whether your recommendations are based on facts or on your personal preferences. -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On 02/18/2015 01:14 PM, Ben Finney wrote: > Johannes Bauer writes: >> On 18.02.2015 08:05, Chris Angelico wrote: >> >>> But if you need more facilities than SQLite3 can offer, maybe it's >>> time to move up to a full database server, instead of local files. >>> Switching to PostgreSQL will give you all those kinds of features, >>> plus a lot of other things that I would have thought pretty basic - >>> like ALTER TABLE. It was quite a surprise to learn that SQLite3 didn't >>> support that. >> >> I see you're running a lawnmower. Maybe you should switch to a combine >> harvester. That'll get you extra features like a reciprocating knife >> cutter bar. I was quite surprised that regular lawnmowers don't support >> those. > > Chris has pointed out one flaw in this analogy; I'll address another. > > A feature like 'ALTER TABLE' is not equivalent to a "reciprocating knife > cutter bar". I'm in agreement that it is a pretty basic SQL feature, and > it doesn't appear to conflict with the narrow focus that we all agree is > appropriate for SQLite. No, you and Chris are way off base and Johannes is correct. He was pointing out that there are many applications that can benefit from a database and a full-blown, bells and whistles solution like Postgresql is often overkill in that (very common) case. His analogy is quite apt and I wish I'd thought of it. > So you're mocking such an expectation as though it's expecting something > wildly niche. I think you're propping up a straw man there; the > expectation is quite simple and its absence from SQLite is astonishing. > Your attempted mockery does not, IMO, hit home. It has already been pointed out that Sqlite *does* have ALTER TABLE so you are either deliberately propagating false information or not paying attention. The only thing basic that a (claimed) relational database needs to support is the basic relational operations of which ALTER TABLE is not one, And if one insists on ALTER TALE the minimal requirement there is that it support RENAME. You and Chris are confusing convenience with necessity. The success and wide adoption of Sqlite is pretty clear evidence that there are many applications that can benefit from its use. Sqllite gets used because of a very important feature that Postgresql is missing -- the ability to embed a database in an application without requiring the installation and maintenance of an entire separate client-server infrastructure. If you are tempted to trivialize that feature you might want to scan the Postgresql mail list where that feature is regularly requested for Postgresql. -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
Johannes Bauer writes: > On 18.02.2015 08:05, Chris Angelico wrote: > > > But if you need more facilities than SQLite3 can offer, maybe it's > > time to move up to a full database server, instead of local files. > > Switching to PostgreSQL will give you all those kinds of features, > > plus a lot of other things that I would have thought pretty basic - > > like ALTER TABLE. It was quite a surprise to learn that SQLite3 didn't > > support that. > > I see you're running a lawnmower. Maybe you should switch to a combine > harvester. That'll get you extra features like a reciprocating knife > cutter bar. I was quite surprised that regular lawnmowers don't support > those. Chris has pointed out one flaw in this analogy; I'll address another. A feature like ‘ALTER TABLE’ is not equivalent to a “reciprocating knife cutter bar”. I'm in agreement that it is a pretty basic SQL feature, and it doesn't appear to conflict with the narrow focus that we all agree is appropriate for SQLite. So you're mocking such an expectation as though it's expecting something wildly niche. I think you're propping up a straw man there; the expectation is quite simple and its absence from SQLite is astonishing. Your attempted mockery does not, IMO, hit home. -- \ “When we call others dogmatic, what we really object to is | `\ their holding dogmas that are different from our own.” —Charles | _o__) Issawi | Ben Finney -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On 18.02.2015 13:14, Chris Angelico wrote: > On Wed, Feb 18, 2015 at 10:57 PM, Johannes Bauer wrote: >> SQLite and Postgres are so vastly different in their setup, >> configuration, capabilities and requirements that the original developer >> has to have done a MAJOR error in judgement so that a change from one to >> the other would not be ill-advised. > > On Wed, Feb 18, 2015 at 6:49 PM, Frank Millman wrote: >> My accounting software supports three databases - MS Sql Server, PostgreSQL, >> and sqlite3. > > Johannes, are you saying that Frank made three major errors of judgement? :) I'm totally pulling my fifth! :-P Cheers, Johannes -- >> Wo hattest Du das Beben nochmal GENAU vorhergesagt? > Zumindest nicht öffentlich! Ah, der neueste und bis heute genialste Streich unsere großen Kosmologen: Die Geheim-Vorhersage. - Karl Kaos über Rüdiger Thomas in dsa -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On Wed, Feb 18, 2015 at 10:57 PM, Johannes Bauer wrote: > SQLite and Postgres are so vastly different in their setup, > configuration, capabilities and requirements that the original developer > has to have done a MAJOR error in judgement so that a change from one to > the other would not be ill-advised. On Wed, Feb 18, 2015 at 6:49 PM, Frank Millman wrote: > My accounting software supports three databases - MS Sql Server, PostgreSQL, > and sqlite3. Johannes, are you saying that Frank made three major errors of judgement? :) ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On 18.02.2015 12:21, Chris Angelico wrote: > SQLite3 is fine for something that's basically just a more structured > version of a flat file. You assume that nobody but you has the file > open, and you manipulate it just the same as if it were a big fat blob > of JSON, but thanks to SQLite, you don't have to rewrite the whole > file every time you make a small change. That's fine. But it's the > wrong tool for any job involving multiple users over a network, and > quite probably the wrong tool for a lot of other jobs too. Your assessment that some tools fit certain problems and don't fit different problems is entirely correct. SQLite does the job that it is supposed to do and it fills that nieche well. > It's the > smallest-end piece of software that can truly be called a database. I > would consider it to be the wrong database for serious accounting > work, and that's based on the ranting of a majorly-annoyed accountant > who had to deal with issues in professional systems that had made > similar choices in back-end selection. It probably is the wrong database for serious accounting work, and it's probably also the wrong database for doing multivariate statistical analysis on sparse matrices that you store in tables. You could similarly argue that a hammer is the wrong tool to drive in a screw and you'd be correct in that assessment. But it's completely besides the point. SQLite and Postgres are so vastly different in their setup, configuration, capabilities and requirements that the original developer has to have done a MAJOR error in judgement so that a change from one to the other would not be ill-advised. > You're welcome to disagree, but since PostgreSQL doesn't cost any > money and (on Linux at least; can't speak for other platforms) doesn't > take significant effort to set up, I will continue to recommend it. I work with Postgres on a professional, day-to-day basis. And while it's free, it *does* take a significant effort to setup and it *does* take a significant effort to maintain. Especially in comparison with something like SQLite that literally has no setup at all. PostgreSQL is great. It's an incredible database and that it's free is amazing. But in very few settings will it be a replacement for SQLite. Cheers, Johannes -- >> Wo hattest Du das Beben nochmal GENAU vorhergesagt? > Zumindest nicht öffentlich! Ah, der neueste und bis heute genialste Streich unsere großen Kosmologen: Die Geheim-Vorhersage. - Karl Kaos über Rüdiger Thomas in dsa -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On Wed, Feb 18, 2015 at 10:11 PM, Johannes Bauer wrote: > On 18.02.2015 08:05, Chris Angelico wrote: > >> But if you need more facilities than SQLite3 can offer, maybe it's >> time to move up to a full database server, instead of local files. >> Switching to PostgreSQL will give you all those kinds of features, >> plus a lot of other things that I would have thought pretty basic - >> like ALTER TABLE. It was quite a surprise to learn that SQLite3 didn't >> support that. > > I see you're running a lawnmower. Maybe you should switch to a combine > harvester. That'll get you extra features like a reciprocating knife > cutter bar. I was quite surprised that regular lawnmowers don't support > those. SQLite3 is fine for something that's basically just a more structured version of a flat file. You assume that nobody but you has the file open, and you manipulate it just the same as if it were a big fat blob of JSON, but thanks to SQLite, you don't have to rewrite the whole file every time you make a small change. That's fine. But it's the wrong tool for any job involving multiple users over a network, and quite probably the wrong tool for a lot of other jobs too. It's the smallest-end piece of software that can truly be called a database. I would consider it to be the wrong database for serious accounting work, and that's based on the ranting of a majorly-annoyed accountant who had to deal with issues in professional systems that had made similar choices in back-end selection. You're welcome to disagree, but since PostgreSQL doesn't cost any money and (on Linux at least; can't speak for other platforms) doesn't take significant effort to set up, I will continue to recommend it. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On 18.02.2015 08:05, Chris Angelico wrote: > But if you need more facilities than SQLite3 can offer, maybe it's > time to move up to a full database server, instead of local files. > Switching to PostgreSQL will give you all those kinds of features, > plus a lot of other things that I would have thought pretty basic - > like ALTER TABLE. It was quite a surprise to learn that SQLite3 didn't > support that. I see you're running a lawnmower. Maybe you should switch to a combine harvester. That'll get you extra features like a reciprocating knife cutter bar. I was quite surprised that regular lawnmowers don't support those. Cheers, Johannes -- >> Wo hattest Du das Beben nochmal GENAU vorhergesagt? > Zumindest nicht öffentlich! Ah, der neueste und bis heute genialste Streich unsere großen Kosmologen: Die Geheim-Vorhersage. - Karl Kaos über Rüdiger Thomas in dsa -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On Wed, Feb 18, 2015 at 6:49 PM, Frank Millman wrote: > My accounting software supports three databases - MS Sql Server, PostgreSQL, > and sqlite3. > > sqlite3 is not suitable for 'heavy-duty' applications, but it is ideal for > demos and one-man businesses. Anyone can try out my software so long as they > have python installed. They do not have to set up a database. I wouldn't trust sqlite3 with my accounting... but then, I'm becoming more and more disillusioned with most full-scale accounting packages, too. I'm seriously looking toward a git-managed directory of text files as being the accounting package of the future; a few pre-commit hooks (and/or pre-receive if you want to have a central repo that people push to) to check file formats and stuff; and then some scripts that do maintenance and reporting (eg updating inventory quantities when you mark an invoice as completed). I want to see a big company work this way... it's clearly not a problem for a one-man operation, as I've been doing exactly that for some time (with very few scripts), and we do know that git scales to global operations just fine, but will it actually work? Inquiring minds must know! But anyway, I wouldn't push people onto sqlite3 for anything serious, mainly because it sucks at concurrency, secondarily because it lacks a number of common features. I'd push people to PostgreSQL. > sqlite3 does support ALTER TABLE, but with limited functionality. I think > all you can do is add a column. Ah, true. Minor support for altering tables, not quite none. But still, there's a lot of limitations that will bite you badly any time you try to migrate a schema using anything other than the brute-force "create entire new database and import the content". ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
"Mark Lawrence" wrote in message
news:mc1g3n$q8j$1...@ger.gmane.org...
> On 18/02/2015 06:19, Frank Millman wrote:
>> Hi all
>>
>> sqlite3 does not have a DATE type, but the python module does a pretty
>> good
>> job of providing one -
>>
>> However, the following does not return a date object -
>>
> cur.execute('SELECT CAST(? AS DATE)', ('2015-03-31',))
>>
> cur.fetchone()
>> (2015,)
>
>>
>
> Will this do?
>
> cur.execute('select current_date as "d [date]", current_timestamp as "ts
> [timestamp]"')
> row = cur.fetchone()
> print("current_date", row[0], type(row[0]))
> print("current_timestamp", row[1], type(row[1]))
>
I will have to experiment a bit, It looks as if it will do just fine.
The magic incantation is
'detect_types=sqlite3.PARSE_DECLTYPES|PARSE_COLNAMES'
I had not looked at PARSE_COLNAMES before. Very useful.
Thanks, Mark
Frank
--
https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
"Chris Angelico" wrote in message
news:CAPTjJmrB+55CVgN6zTUawRf=rzn9ltavi5tzhjsyvhhywk1...@mail.gmail.com...
> On Wed, Feb 18, 2015 at 5:19 PM, Frank Millman wrote:
>> However, the following does not return a date object -
>>
> cur.execute('SELECT CAST(? AS DATE)', ('2015-03-31',))
>>
> cur.fetchone()
>> (2015,)
>
>>
>> I don't know how easy this would be to implement, but it would be nice if
>> it
>> could be made to work.
>
> Heh! Looks like the date is implemented as a slightly magical integer,
> so "cast to date" becomes "cast to integer" and you just get back
> 2015. Could be really easy to fix, could be nigh impossible... but
> sure, that seems a reasonable thing to ask for. Worst case, you get
> told it's not practical.
>
> But if you need more facilities than SQLite3 can offer, maybe it's
> time to move up to a full database server, instead of local files.
> Switching to PostgreSQL will give you all those kinds of features,
> plus a lot of other things that I would have thought pretty basic -
> like ALTER TABLE. It was quite a surprise to learn that SQLite3 didn't
> support that.
>
My accounting software supports three databases - MS Sql Server, PostgreSQL,
and sqlite3.
sqlite3 is not suitable for 'heavy-duty' applications, but it is ideal for
demos and one-man businesses. Anyone can try out my software so long as they
have python installed. They do not have to set up a database.
sqlite3 does support ALTER TABLE, but with limited functionality. I think
all you can do is add a column.
Frank
--
https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On 18/02/2015 06:19, Frank Millman wrote:
Hi all
sqlite3 does not have a DATE type, but the python module does a pretty good
job of providing one -
import sqlite3
conn = sqlite3.connect(':memory:', detect_types=sqlite3.PARSE_DECLTYPES)
cur = conn.cursor()
cur.execute('CREATE TABLE test (dob DATE)')
cur.execute('INSERT INTO TEST (dob) VALUES (?)', ('2015-03-31',))
cur.execute('SELECT * FROM test')
cur.fetchone()
(datetime.date(2015, 3, 31),)
However, the following does not return a date object -
cur.execute('SELECT CAST(? AS DATE)', ('2015-03-31',))
cur.fetchone()
(2015,)
I don't know how easy this would be to implement, but it would be nice if it
could be made to work.
Is it worth filing a feature request?
Frank Millman
Will this do?
cur.execute('select current_date as "d [date]", current_timestamp as "ts
[timestamp]"')
row = cur.fetchone()
print("current_date", row[0], type(row[0]))
print("current_timestamp", row[1], type(row[1]))
https://docs.python.org/3/library/sqlite3.html#default-adapters-and-converters
--
My fellow Pythonistas, ask not what our language can do for you, ask
what you can do for our language.
Mark Lawrence
--
https://mail.python.org/mailman/listinfo/python-list
Re: sqlite3 and dates
On Wed, Feb 18, 2015 at 5:19 PM, Frank Millman wrote:
> However, the following does not return a date object -
>
cur.execute('SELECT CAST(? AS DATE)', ('2015-03-31',))
>
cur.fetchone()
> (2015,)
>
> I don't know how easy this would be to implement, but it would be nice if it
> could be made to work.
Heh! Looks like the date is implemented as a slightly magical integer,
so "cast to date" becomes "cast to integer" and you just get back
2015. Could be really easy to fix, could be nigh impossible... but
sure, that seems a reasonable thing to ask for. Worst case, you get
told it's not practical.
But if you need more facilities than SQLite3 can offer, maybe it's
time to move up to a full database server, instead of local files.
Switching to PostgreSQL will give you all those kinds of features,
plus a lot of other things that I would have thought pretty basic -
like ALTER TABLE. It was quite a surprise to learn that SQLite3 didn't
support that.
ChrisA
--
https://mail.python.org/mailman/listinfo/python-list
