Member Avatar for 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!!

  • 4 Contributors
  • 12 Replies
  • 245 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by ov3rcl0ck

Dani AI

Generated

Quick summary and recommendation (short): for a Python app that must support multiple simultaneous readers/writers from different machines, use a client–server database (PostgreSQL, MariaDB/MySQL, etc.) running on a dedicated host rather than placing a file-based DB on a network share. That gives true concurrent transactions, reliable file locking, central backups and simpler security. This follows the practical guidance in the SQLite documentation about when a client/server RDBMS is a better fit.
(Appropriate Uses for SQLite).

Why file-on-a-share is risky: embedded/file databases rely on OS-level file locking. Many network filesystems (SMB, NFS, etc.) do not implement the required advisory-lock semantics reliably, and that can produce “database is locked” errors or corruption. SQLite’s locking notes explain this; Firebird’s FAQ explicitly warns that databases must be on local disks (or served by a Firebird server) and that mapped drives are unsafe.
(SQLite: File Locking & Concurrency) • (Firebird FAQ: mapped drive).

Practical setup notes you can apply now:

  • Install the DB server on a reliable machine (local disk) and let clients connect over TCP/IP. Do not share the raw DB file.
  • Limit access at the network level (firewall), use strong DB accounts/roles, and prefer VPN/SSH tunnels or SSL for remote offices rather than opening the DB to the public Internet. (See standard hardening steps for PostgreSQL.)
    (How to secure PostgreSQL — DigitalOcean).
  • For Python clients use a proper DB adapter and pooling (psycopg2 / psycopg3 for Postgres); keep credentials out of distributed binaries or rotate them often. Example connection/pool (psycopg2):
    from psycopg2 import pool
p = pool.SimpleConnectionPool(1, 10, host='db.host', port=5432,
                           database='mydb', user='app', password='pw')
conn = p.getconn()
# use conn.cursor(), then p.putconn(conn)

    (see psycopg docs for production guidance). (psycopg docs)

Notes tying back to the thread: ’s Postgres suggestion is the usual, safe choice; ’s idea of a socket/API layer is also a good pattern—run a small app server (REST or RPC) that mediates DB access so clients never talk to the DB directly; ’s points about hosting and firewall rules are exactly the network-level controls you should apply. Checklist: server on local disk, firewall/VPN, per-user accounts, connection pooling, regular backups, and test concurrency under expected load.

Recommended Answers

All 12 Replies

Member Avatar for 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.

Member Avatar for Stefano Mtangoo

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!

Member Avatar for 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.

Member Avatar for Stefano Mtangoo

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

Member Avatar for 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 passwords

In 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.

Member Avatar for 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.

Member Avatar for 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.

Member Avatar for 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?

Edited by jcmeyer because: n/a
Member Avatar for 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.

Member Avatar for 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.

Member Avatar for jcmeyer

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

Member Avatar for ov3rcl0ck

Well problem solve then?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.