Import Postgre SQL / CSV


#1

We have an in-house data set of previously analyzed samples and their metadata (3000 rows, 30 columns). Each row is a sample and each column is a description for sample. Some columns are the metadata (date of acquisition, client name etc.), other columns are the sample-analysis-results. Is it possible to import this data set into senaite.lims ?


#2

Hi @n3m, generally you can import any data into SENAITE.
For example you can connect to a PostgreSQL DB with the psycopg2 library:

pip install psycopg2

conn = psycopg2.connect(
    user='postgres',
    password='1234',
    host='127.0.0.1',
    port='5432',
    database='DB'
)
cursor = conn.cursor()
cursor.execute('SELECT * FROM data LIMIT 10')
for row in cursor: print(row)
conn.close()

The question is: What do you want to achieve?

If it is just for viewing the data, you can store the values in any object to a dedicated field.
If they contain working data, e.g. Specifications or Client Data etc, then you need to map the data to existing Contents from SENAITE or create new contents during the import.

However, such data migrations are usual projects of its own and require to write import/migration scripts,
because it is not possible out of the box through the web.

Does this answer your question?

Regards, Ramon


#3

Hi Ramon,
Many thanks for the answer.
The postgres information is client data, so yes, I would like to create new content in SENAITE and map the data. As I wrote earlier, the data is not big, (3000 x 30 table). Although the data is a very simple table, earlier data manager stored it in postgres. So, my task is to simplify the data structure, that means - to migrate existing data from postgres to ZODB. Can you point me to the relevant documentation ?
Cheers
Nara


#4

Hi Nara,

there is no documentation available how to achieve that, so the best option is to inspect the schema of clients in senaite.core.contents.client to see how you need to map your data to the client objects.

Then I would recommend you to write a script that you can execute via bin/instance run my_migration_script.py and includes some code like this:

import logging

import transaction
from AccessControl.SecurityManagement import newSecurityManager
from bika.lims import api
from zope.component.hooks import setSite

SID = "senaitelims"
logger = logging.getLogger(__name__)

user_name_or_id = "admin"


def import_from_postgresql(portal):
    """Import Data from PostgreSQL
    """
    clients = portal.clients

    for row in cursor:
        data = map_data(row)  # dictionary of mapped data
        api.create(clients, "Client", **data)
        logger.info("Commiting Changes")
        transaction.commit()


if "app" in locals():
    logger.setLevel(logging.INFO)
    logger.addHandler(logging.StreamHandler())

    app = locals()["app"]
    portal = app[SID]
    setSite(portal)

    user = app.acl_users.getUser(user_name_or_id)
    newSecurityManager(None, user.__of__(app.acl_users))

    import_from_postgresql(portal)

The rest you need to figure out by reading/inspecting the codebase and using the internal API:

Best you also read the doctests to get a better understanding how to use it:

Regards, Ramon


#5

Hi Nara,
you might this also helpful to import data into Senaite: