Python package that gives you the power of a PostgreSQL server, with the convenience of the sqlite3 package.
Python module that gives you the power of a PostgreSQL server, with the convenience of the sqlite3 module.
postgresqlite.connect() call, the library will automatically...
~/.cache directory. (Linux and macOS EM64T only, for now.)initdb) within the project directory with a random password.pg8000 driver).sqlite3 module:
execute on the connection will create a new cursor.fetchall and fetchone return objects that can address fields both by number (as is standard for DB-API) as well as by name (as sqlite3 offers when you configure connection.row_factory = sqlite3.Row).? as a placeholder. (paramstyle = 'qmark')psql and other PostgreSQL clients passing in connection details, while making sure the database is running.pip install postgresqlite
import postgresqlite
db = postgresqlite.connect(mode='dbapi')
cursor = db.cursor()
cursor.execute('create table if not exists tests(id serial, info text not null, created_at timestamp not null default current_timestamp)')
cursor.execute("insert into tests(info) values('Hi mom!'),('This is great!')")
db.commit()
cursor.execute("select id, info, created_at from tests where id=%s", [1])
for row in cursor:
print("row:", row, "id:", row[0], "created_at:", row[2])
cursor.execute("select count(*) from tests")
print("count:", cursor.fetchone()[0])
cursor.execute("select * from tests order by id desc limit 1")
print("row:", cursor.fetchone())
FriendlyConnection APIpip install postgresqlite
import postgresqlite
db = postgresqlite.connect()
db.query('create table if not exists tests(id serial, info text not null, created_at timestamp not null default current_timestamp)')
db.query("insert into tests(info) values('Hi mom!'),('This is great!')")
rows = db.query("select * from tests where id=:id", id=1)
for row in rows:
print("row:", row, "id:", row.id, "created_at:", row.created_at)
print("count:", db.query_value("select count(*) from tests"))
print("last row:", db.query_row("select * from tests order by id desc limit 1"))
pip install postgresqlite flask_sqlalchemy
import postgresqlite, flask, flask_sqlalchemy, datetime
app = flask.Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = postgresqlite.get_uri()
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = flask_sqlalchemy.SQLAlchemy(app)
class Car(db.Model):
id = db.Column(db.Integer, primary_key=True)
brand = db.Column(db.String)
model = db.Column(db.String, nullable=False)
day_rate = db.Column(db.Integer)
rentals = db.relationship('Rental', backref='car')
def to_dict(self):
return {'id': self.id, 'brand': self.brand, 'model': self.model, 'day_rate': self.day_rate, 'rentals': [rental.to_dict() for rental in self.rentals]}
class Rental(db.Model):
id = db.Column(db.Integer, primary_key=True)
car_id = db.Column(db.Integer, db.ForeignKey('car.id'), nullable=False)
customer_name = db.Column(db.String, nullable=False)
start_time = db.Column(db.DateTime, default=datetime.datetime.now)
end_time = db.Column(db.DateTime)
def to_dict(self):
return {'id': self.id, 'car_id': self.car_id, 'customer_name': self.customer_name, 'start_time': self.start_time, 'end_time': self.end_time}
@app.route('/cars')
def show_all():
return flask.jsonify([car.to_dict() for car in Car.query.all()])
@app.route('/cars', methods = ['POST'])
def new_car():
car = Car(**flask.request.get_json())
db.session.add(car)
db.session.commit()
return flask.jsonify(car.to_dict())
@app.route('/rentals', methods = ['POST'])
def new_rental():
rental = Rental(**flask.request.get_json())
db.session.add(rental)
db.session.commit()
return flask.jsonify(rental.to_dict())
# Create tables based on the Model classes above.
with app.app_context():
db.create_all()
if __name__ == '__main__':
app.run(debug = True)
Running this should expose a REST API. If you have httpie installed, it can be tested from the command like this:
http http://127.0.0.1:5000/cars brand=Volvo model=S60
http http://127.0.0.1:5000/rentals car_id=1 customer_name=Frank
http http://127.0.0.1:5000/cars
postgresqlite.connect(dirname='data/postgresqlite', mode='friendly', config=None)Start a server (if needed), wait for it, and return a DB-API compatible object.
Arguments:
dirname (str): The dir where the configuration file (postgresqlite.json)
will be read or created, and where database files will be stored. If the
path does not exist, it will be created.mode ('easy', 'sqlite', 'dbapi'):
friendly (the default), a FriendlyConnection object is returned. It's a DB-API compatible Connection, but with a few additions to make it more programmer-friendly, as documented in the The FriendlyConnection object-section.sqlite3 API more closely:
Connection objects have an execute method that creates a new cursor and
runs the given query on it.connection.row_factory = sqlite3.Row for sqlite3).? as a placeholder. (paramstyle = 'qmark')config (Config | None): An object obtained through get_config() can be given to configure the connection. This causes dirname to be ignored.Arguments:
data/postgresqlite): The directory where the configuration file (postgresqlite.json) will be read or created, and where database files will be stored. If the path does not exist, it will be created.True): When set, a few (superficial) changes are made to the exposed DB-API to make it resemble the Python sqlite3 API more closely, as described in the Features section.Config object, defaults to None): This can be an object returned by get_config. When None, the connect method will create a default configuration.Returns a DB-API compatible connection object.
postgresqlite.get_config(dirname='data/postgresqlite')Start a server (if needed), wait for it, and return the config object. If the PostgreSQL server is in autostart mode (which is the default), it will be kept running until some time after the calling process (and any other processes that depend on this server) have terminated.
Arguments:
postgresqlite.json) will be read or created, and where database files will be stored. If the path does not exist, it will be created.Returns a Config object, that includes (among others) the following attributes:
user (string): Database user name.password (string): Database password. (Can be None.)port (int): Database TCP port. (Can be None.)host (string): Database host name. (Can be None.)database (string): Database name.socket (string): Database UNIX domain socket. (Can be None.)env (dict): A dictionary containing PGHOST, PGPORT, PGDATABASE, PGSOCKET, PGUSER, PGPASSWORD and PGURI keys with their appropriate values.postgresqlite.get_uri(dirname='data/postgresqlite', driver='pg8000')Start a server (if needed), wait for it, and return a connection URL.
Arguments:
postgresqlite.json) will be read or created, and where database files will be stored. If the path does not exist, it will be created.postgresql+DRIVER://user:pwd@host/db), which we'll set to pg8000 by default. This parameter allows you to specify a different direct, or leave it out (by providing None).FriendlyConnection objectBy default the postgresqlite.connect method will return a FriendlyConnection, which is a DB-API compatible Connection object, but with a few programmer-friendly differences:
row['name'] instead of row[0]. In addition, attribute syntax can be used, like row.name.:named style.SQLError and contain easy to read error messages, highlighting the offending part of the query.Besides, those changes, the FriendlyConnection object offers a couple of additional methods:
Create a new Cursor, execute the given sql with the given params dictionary on that cursor, and return the Cursor. Example:
row = db.execute('select * from test where id=:id', {'id': 123})).fetchone()`
Execute the sql with the given parameters, returning a list of FriendlyRow objects. Example:
rows = db.query('select id, name from test where name=:name', name='Ivo')
print(len(rows), rows[0].id) # 3 123
Execute the sql with the given parameters, returning a single FriendlyRow object or None (if no rows were returned). If the query results in more than one row, an exception is thrown. Example:
row = db.query_row('select id, name from test where id=:id', id=123)
print(row.id, row.name) # 123 Ivo
Execute the sql with the given parameters, returning a list of values. If the query results in more than one column, an exception is thrown. Example:
test_ids = db.query_column('select id from test where name=:name', name='Ivo')
print(test_ids) # [123, 456, 789]
Execute the sql with the given parameters, returning a single value. If the query results in more than one column or more than one row, an exception is thrown. Example:
test_id = db.query_value('select id from test where name=:name limit 1', name='Ivo')
print(test_id) # 123
In order to easily access the database your application is using, PostgreSQLite can open a client application (such as psql or Beekeeper Studio) for you, making sure the database is started (and doesn't shutdown) while the application is open, and passing in connection details.
The connection details are provided in environment variables called PGHOST, PGPORT, PGDATABASE, PGSOCKET, PGUSER, PGPASSWORD and PGURI.
An application can be started by running the postgresqlite package with -m, providing the executable and its arguments as arguments.
psql, the default PostgreSQL client, is run. So opening your database in psql can be done like this:
python -mpostgresqlite
psql client uses the environment variables to set up a connection.$PG, it will be executed as a shell command. So in order to pass the URI as the argument to an application, one could use:
python -mpostgresqlite 'xdg-open $PGURI'
postgresql:// protocol, the above command can be used to open your database with it.python -mpostgresqlite my-client connect --extra-fancy
Optionally, the directory where the configuration file (postgresqlite.json) will be read or created, and where database files will be stored, can be specified by prefixing the command with -c <dirname>. For example:
python -mpostgresqlite -d mydbdir 'xdg-open $PGURI'
When PostgreSQLite is first started (for a certain directory), a configuration file called postgresqlite.json is created. After initial creation, the file is all yours to modify. It contains the following values:
autostart (bool): When true, PostgreSQLite will automatically start/stop a PostgreSQL server. Otherwise, it will just connect to an existing database given the credentials in this file.host (str): Connection host name.port (int): Connection TCP port.socket (str): Connection UNIX domain socket (optional).database (int): Database name.user (str): Connection user name.password (str): Connection password name (optional).socket_id (str): A (random) string that distinguishes this instance of PostgreSQLite, for creating the PostgreSQL unix socket file in /tmp.These fields are only relevant when autostart is true:
expire_seconds (int): The time in seconds after which a server is shutdown when there are no active clients anymore.pg_cache_dir (str): The directory into which PostgreSQL will be installed and from which it will be ran.postgresql_version (str): The version of PostgreSQL to use. Currently only "14.3" is supported.PostgreSQLite uses a series of lock files (stored in locks/ in the configuration directory) to coordinate different application instances that may want to use the database. When such a process starts and no database is running, it forks a daemon that will start and eventually stop the PostgreSQL server. Stopping happens when there have been no more database applications running for expire_seconds, based on the lock files created by each application.
In case you want to force the server to stop immediately, you can do so by deleting the locks/daemon.lock file within the configuration directory. Starting the application again will cause the server to start anew.
The autostart feature installs PostgreSQL from a tarball stored as a GitHub release. This tarball is created using the Dockerfile and run.sh script provided in the create-standalone-postgresql/ directory.
It works by copying the PostgreSQL binaries and depend files (including any .so files being used) from an Arch linux installation to a .tar.gz file.
FAQs
Python package that gives you the power of a PostgreSQL server, with the convenience of the sqlite3 package.
We found that postgresqlite demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Research
An advanced npm supply chain attack is leveraging Ethereum smart contracts for decentralized, persistent malware control, evading traditional defenses.
Security News
Research
Attackers are impersonating Sindre Sorhus on npm with a fake 'chalk-node' package containing a malicious backdoor to compromise developers' projects.
Security News
The npm package for the LottieFiles Player web component was hit with a supply chain attack after a software engineer's npmjs credentials were compromised.