How to Connect Different Databases on the Same Server in Odoo 15

There are many cases where we need to connect to different databases on the same server to fetch data, create records, write records, etc. 

By using the contextlib package, we can implement this.

The contextlib module of Python is used for stopping the propagation of leaked abstractions and is usually used when opening a file or making a database connection. The with statement used in Python works with unmanaged resources. It makes sure that a resource is cleared out when the code finishes its running.

In the contextlib using closing(something) function which returns a Context Manager that closes something upon completion of the with block.

Next, we have to import sql_db, SUPERUSER_ID from Odoo

import contextlib
from odoo import sql_db, SUPERUSER_ID

Now we have imported the contextlib,  sql_db and SUPERUSER_ID. Then we have to define a function if we want to connect to another database inside a function. Then use db_connect(db_name) to connect the corresponding database. Then we create a cursor to create a connection to the corresponding database. After that we can fetch data from the database using:

By using Query

from odoo import models, sql_db
class SaleOrder(models.Model):
   _inherit = 'sale.order'
   def action_confirm(self):
       res = super().action_confirm()
       db = sql_db.db_connect('db_name')
       cursor = db.cursor()
       cursor.execute("""select name from sale_order""")
       data = cursor.dictfetchall()

Here, we are going to connect to another database and fetch the sale order name of all sale orders from that database while confirming the sale order. At first, we super the confirm button of the sale order. For that we use super() function to super the function action_confirm(). Then we connect to the database in the server by mentioning the database name in the place of db_name. Then we create a cursor, which Odoo uses as an interface to communicate with the PostgreSQL database. 

In Odoo, we usually do not have to connect with the cursor directly. However, sometimes there will be situations where the Odoo ORM could not provide the expected output. This is where we have the use of the database cursor. Then we use execute() to execute a query in python or in function. Write the query inside the execute() as shown above. If we want to fetch data as the form of the list contains a dictionary having key as a field name in the database and value as data of the corresponding field then we use cursor.dictfetchall(), if we want to fetch a single data can use cursoer.dictfetchone() which works same as cursorr.dictfetchall(), the only difference is that fetchone() return single record, cursorr.fetchall() will give us all the matching records in the form of the list of tuples, cursorr.fetchone() works the same way as cursorr.fetchall(), and the difference is that it only returns a single record.

import contextlib
from odoo import models, api, sql_db, SUPERUSER_ID
class SaleOrder(models.Model):
   _inherit = 'sale.order'
   def action_confirm(self):
       res = super().action_confirm()
       connection = sql_db.db_connect('db_name')
       with contextlib.closing(connection.cursor()) as cr:
           cr.autocommit(True)
           env = api.Environment(cr, SUPERUSER_ID, {})
           sale_order = env['sale.order'].search([])

Here, we are going to connect to another database and search the sale order of all sale orders from that database while confirming the sale order. We import contextlib, sql_db SUPERUSER_ID. Then we super the confirm button of the sale order. After that,  we specify the database name in ‘db_name’ and create the cursor that connects to the database mentioned in the db_name. After that, we commit the cursor, to commit the data in the cursor to the database. Then we create an environment of that database, Environment in API is to provide an encapsulation around the cursor, which stores various contextual data used by the ORM. Using that we can perform ORM methods such as search, browse, create, write, unlink, search_read, etc.