0

im working on python application that requiring database connections..I had developed my application with sqlite3 but it start showing the error(the database is locked).. so I decided to use MySQL database instead.. and it is pretty good with no error.. the only one problem is that I need to ask every user using my application to install MySQL server on his pc (appserv for example) .. so can I make mysql to be like sqlite3 apart of python lib. so I can produce a python script can be converted into exe file by the tool pyInstaller.exe and no need to install mysql server by users???

update: after reviewing the code I found opened connection not closed correctly and work fine with sqllite3 ..thank you every body

user2980054
  • 453
  • 1
  • 7
  • 21
  • 1
    The MySQLdb module comes with [embedded server support](http://mysql-python.sourceforge.net/MySQLdb.html#embedded-server). – Carsten Apr 02 '14 at 14:57

2 Answers2

1

It depends (more "depends" in the answer).

If you need to share the data between the users of your application - you need a mysql database server somewhere setup, your application would need to have an access to it. And, the performance can really depend on the network - depends on how heavily would the application use the database. The application itself would only need to know how to "speak" with the database server - python mysql driver, like MySQLdb or pymysql.

If you don't need to share the data between users - then sqlite may be an option. Or may be not - depends on what do you want to store there, what for and what do you need to do with the data.

So, more questions than answers, probably it was more suitable for a comment. At least, think about what I've said.

Also see:

Hope that helps.

Community
  • 1
  • 1
alecxe
  • 462,703
  • 120
  • 1,088
  • 1,195
  • thank you I want to use sqlite3 but the mentioned error is a nightmare – user2980054 Apr 02 '14 at 15:05
  • @user2980054 ok, then can you be more specific and show how do you open up and close the sqlite3 connection in the code? Thanks. – alecxe Apr 02 '14 at 15:06
  • the app is market manager program that make it easy for user to buy ,sell ,report, get profits calculations and so on.. for example: after filling some form he clicking Button that opening database connection and inserting one or more sql(s) – user2980054 Apr 02 '14 at 15:24
  • @user2980054 i bet you are just not closing the sqlite connection properly. See http://stackoverflow.com/questions/9561832/what-if-i-dont-close-the-database-connection-in-python-sqlite – alecxe Apr 02 '14 at 15:26
  • after reviewing the code I found opened connection not closed correctly and work fine with sqllite3 ..thank you – user2980054 Apr 02 '14 at 20:38
0

If your application is a stand-alone system such that each user maintains their own private database then you have no alternative to install MySQL on each system that is running the application. You cannot bundle MySQL into your application such that it does not require a separate installation.

There is an embedded version of MySQL that you can build into your application (thanks, Carsten, in the comments, for pointing this out). More information is here: http://mysql-python.blogspot.com/. It may take some effort to get this working (on Windows you apparently need to build it from source code) and will take some more work to get it packaged up when you generate your executable, but this might be a MySQL solution for you.

I've just finished updating a web application using SQLite which had begun reporting Database is locked errors as the usage scaled up. By rewriting the database code with care I was able to produce a system that can handle moderate to heavy usage (in the context of a 15 person company) reliably still using SQLite -- you have to be careful to keep your connections around for the minimum time necessary and always call .close() on them. If your application is really single-user you should have no problem supporting it using SQLite -- and that's doubly true if it's single-threaded.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • _"You cannot bundle MySQL into your application such that it does not require a separate installation."_ -- [Yes you can!](https://dev.mysql.com/doc/refman/5.7/en/libmysqld.html) – Carsten Apr 02 '14 at 14:58
  • my application is for single user and every database connection is ended with close() function ..but SQLite3 keeps showing me the locked database error even after make the parameter timeout=20 – user2980054 Apr 02 '14 at 15:04
  • 1
    @Carsten: Thanks, updated to reflect the existence of this previously-unknown-to-me library. – Larry Lustig Apr 02 '14 at 15:08
  • @user2980054: Then you have an error in your code somewhere. Possibly you're swallowing an exception somewhere that's leaving an uncommitted transaction on the database. Single-threaded, single-user applications are completely doable using SQLite, and that's the most appropriate database to use for that kind of application. – Larry Lustig Apr 02 '14 at 15:09
  • do i need to commit after every single sql execution ..? – user2980054 Apr 02 '14 at 15:20
  • You do not. But every time you create a connection you must use `try \ except \ finally` to ensure that after you've done your SQL statements that the connection is `commit()`-ted and `close()`-ed. Isolate your database code in one or two source files and rethink how you have it structured to ensure the reliability of those connection objects. – Larry Lustig Apr 02 '14 at 15:25