I need help choosing a back-end for handling databases

[jcmeyer]

I need a back-end that can:

-be used in python

-handle multiple-users reading and writing to a database at the same time

-can be accessed on a mapped share drive

I have used sqlite but I have read that it is not recommended for multi-user use. I have looked into Firebird but it should not be used on a mapped share drive.

Any suggestions would be greatly appreciated!!

[jlm699]

This is certainly not the only solution but postgresql can easily handle multiple users (like I assume most other RDMs can). The psycopg2 module is what I've used in the past for postgres but I know there are a few others out there.

[evstevemd]

As jlm699 said, Postgresql is good but isn't the only one.
You can go with mysql plus mysql-python.
I have read yahoo! and many other big companies uses mysql(not sure if it is with Python or PHP) so I know it must be multiuser!

[jcmeyer]

Thank you for your suggestions. Perhaps you can help answer a couple other questions concerning DBMS's. My networking knowledge is minimal at best.

So let's say I use one that uses a database server. Firebird does this for example. Now, let's say I am on a LAN such as in an office building. Each person in the office wants access to the databases on this database server. They want to be able to read and write to this database. The network is secure.

How do I create this server? Can the server and the databases be on any machine's hard disk on the network? Also, once the server and databases are created, how is access granted to the server? In my application I use wxpython as a front-end, but can anyone with a front-end get onto this database server? Or can only people on the office LAN gain access to this server?

Thank you very much. I appreciate all replies.

[evstevemd]

That's where sockets comes in MHO.
You will use to send data to the server and then there will be a thread opened to server to save each query. So theoretically this is what I would do :
1. Connect to the server via sockets and server's mysql-python module
2. Use that connection to send queries
3. Close database connection and end socket session

Just my 2cents

[jlm699]

How do I create this server? Can the server and the databases be on any machine's hard disk on the network?

This process is pretty easy with Postgresql; just download and install from here . There's documentation and help resources there for you if you're a command line junkie. There's a GUI front-end for pg that you can use to setup your database quickly and with very minimal effort. And yes, you can install the server/database onto any machine's hard disk but keep in mind that if you need a large database that is always available you'll want to install it on a server (or at least a reliable computer that is always available), preferably with more than plenty of storage.
Also, once the server and databases are created, how is access granted to the server?You set up user accounts with passwordsIn my application I use wxpython as a front-end, but can anyone with a front-end get onto this database server?
wxPython isn't your front-end it's just your GUI toolkit. The front-end would be whatever you use to access the database. Anyone that has a compatible front-end would have access as long as they know a user name/password (can also be open access ie, no pw). When you install pg it comes with a very nice front-end that will allow you quick and easy access to any pg databases. Any body that wants to access your db could install this application.

You could alternately create and distribute a Python program to access the database, which would act as the user's front-end (even though the pg module that you make use of is the means to this end). Or you could simply create a web front-end (I've used PHP to create a db front-end for my workplace which I highly recommend) Or can only people on the office LAN gain access to this server?Depends whether your "office LAN" is internal only or if it can be accessed from the outside world.

[jcmeyer]

That was extrememely helpful jlm699! You sold me on postgresql.

A couple more things:

I suppose actually it would not be on a LAN but rather an intranet. Like at a business with multiple spread out office locations. Could this be a problem if the database server is on this intranet in one city and then an office somewhere wants access to it?

I have already made a Python program using the wxpython GUI toolkit to create and distribute to the users s a front-end.

So, simply, and if I understand you correctly, I use postgresql as a back-end in my Python program, set up the database server on this office intranet with multiple accounts for security, and then distribute the Python program as a way for the users to access the databases. And postgresql handles any issues with multiple-users reading and writing to the database so I don't necessarily have to worry about that anymore.

Thank you again, jlm699.

[ov3rcl0ck]

I prefer Postgre because I don't like many aspects of MySQL and its preformance. If needed you can use serialization+threading to acheive this, or make your own database using one of pythons many databasing libraries.

[jcmeyer]

I have looked more into pstgresql since my last post and want to refine my questions. From what I understand limiting who can connect to the database server is very easy with postgresql. You can use usernames and passwords along with allowing only certain ip addresses.

So as long as you are connected to the office intranet, know the name of the host of the database server, know a username and password and you have an allowed ip address, you should be able to get access to the server...no matter where you are? If the database server is hosted on either a computer or server on the private office intranet, do you need to be on the private office intranet to access it, or just simply connected to the internet somewhere?

[ov3rcl0ck]

I have looked more into pstgresql since my last post and want to refine my questions. From what I understand limiting who can connect to the database server is very easy with postgresql. You can use usernames and passwords along with allowing only certain ip addresses.

So as long as you are connected to the office intranet, know the name of the host of the database server, know a username and password and you have an allowed ip address, you should be able to get access to the server...no matter where you are? If the database server is hosted on either a computer or server on the private office intranet, do you need to be on the private office intranet to access it, or just simply connected to the internet somewhere?

could basically set it up either way, you can host it on your LAN or you can host it on the internet, if you host it on your LAN its only going to be available to your local network in your office, if you host it online then it can be accessed anywhere, but of course you can restrict access based on IP you could probably just use IPtables to filter IP's.

[jlm699]

I have looked more into pstgresql since my last post and want to refine my questions. From what I understand limiting who can connect to the database server is very easy with postgresql. You can use usernames and passwords along with allowing only certain ip addresses.

So as long as you are connected to the office intranet, know the name of the host of the database server, know a username and password and you have an allowed ip address, you should be able to get access to the server...no matter where you are? If the database server is hosted on either a computer or server on the private office intranet, do you need to be on the private office intranet to access it, or just simply connected to the internet somewhere?

In addition to ov3rcl0ck's comments above, also keep in mind that many "intranets" allow you to tunnel or VPN inside of the intranet from the outside world. If you have a way of accessing your work LAN/intranet from home then potentially you could access the private network-hosted database from anywhere. Otherwise, the only way to access the intranet-hosted database would be to have open access to said intranet.

You could also choose to host the database on your company's DMZ server; this would be the server that provides the outside internet into your internal network. You'd have to talk to your IT department about it, as I'm sure they'd have a number of security measures that they would want to put into place during the postgres setup.

[jcmeyer]

Thanks guys, I am marking this as solved. Very very helpful.

[ov3rcl0ck]

Well problem solve then?