Use Dolt With MySQL Connector in Python

November 1, 2024

6 min read

Dolt is a version controlled MySQL compatible database. It means you can commit, diff and merge along with other Git-like version control actions. You can use Dolt with most workbenches, ORMs and SQL connectors that are available on the Internet. We have been writing blogs demonstrating how to connect those tools to Dolt server. Today, we go over how to use MySQL Connector/Python to connect to Dolt database.

Before we start, let's make sure to install tools we need:

Dolt, install guide here
MySQL Connector/Python, install guide here

If you want to jump ahead to the complete code of the demo, here is a link to the GitHub repository.

Getting started

If you're not familiar with Dolt, this documentation has basics of setting up Dolt.

Let's create a directory where we want our database to be in and run dolt init command. This command creates a Dolt database that we can start a SQL server on. The server can be custom-configured. Here is the command documentation to configure a custom SQL server. Or, we can simply start a local server with the default configuration using dolt sql-server. The default port used is 3306.

mkdir doltdb && cd doltdb
dolt init
dolt sql-server

The above code will result an empty Dolt database, so we need to add some data.

Create sample data

Once the server is running, we can connect to it with any SQL client that is used to connect to MySQL server because Dolt is MySQL compatible. Let's add a table with some rows to our database.

-- Switch to `doltdb` database
mysql> use doltdb;
Database changed
-- Create a table with some rows
mysql> create table playlist (id int primary key auto_increment, artist_name varchar(100), song_name varchar(100));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into playlist (artist_name, song_name) values ('Porter Robinson', 'Shelter'), ('Benson Boone', 'Beautiful Things');
Query OK, 2 rows affected (0.02 sec)

mysql> select * from playlist;
+----+-----------------+------------------+
| id | artist_name     | song_name        |
+----+-----------------+------------------+
|  1 | Porter Robinson | Shelter          |
|  2 | Benson Boone    | Beautiful Things |
+----+-----------------+------------------+
2 rows in set (0.01 sec)

-- Commit the changes
mysql> CALL DOLT_COMMIT('-Am', 'Create a sample table with rows');
+----------------------------------+
| hash                             |
+----------------------------------+
| tii5lgjci672vg3tkl6hc1j8f1kjhkt9 |
+----------------------------------+
1 row in set (0.02 sec)

-- Create a new branch
mysql> CALL DOLT_BRANCH('mybranch');
+--------+
| status |
+--------+
|      0 |
+--------+
1 row in set (0.07 sec)

Dolt is the only SQL database with true branches. Let's create a new branch for us to develop on the data.

Let's set up the database connection using `mysql.connector` in Python

Now that we have some data in our database, let's create our demo. The following code as a full can be found in dolthub/dolt-mysql-connector-python GitHub repository.

Let's start with connecting to the Dolt SQL server we just started. The following code is one of many examples how we can use mysql.connector library. More examples can be found on MySQL Connector/Python docs.

The default configuration of doltdb connects to the main branch. We can specify which branch to connect to using database/branch name pattern. For example, if we have an existing branch called mybranch. We can connect to it using doltdb/mybranch for database field of the configuration.

import mysql.connector

# configuration to connect to Dolt server
config = {
  'user': 'root',
  'password': '',
  'host': '127.0.0.1',
  'database': 'doltdb/mybranch',
}

try:
  cnx = mysql.connector.connect(**config)
  print("Successfully connected to Dolt server!")
except mysql.connector.Error as err:
    print(err)
else:
  print("Disconnecting from Dolt server!")
  cnx.close()

Let's look at each step we will take in separate functions.

First, let's check the current branch we are on. It should be mybranch as we defined in the configuration.

'''
Check current branch we are on.
'''
def check_current_branch(cnx: mysql.connector.connection):
  print("\n--checking current branch\n")
  cursor = cnx.cursor()
  cursor.execute("SELECT active_branch()")
  for (active_branch) in cursor:
    print('Current branch: {}'.format(active_branch[0]))
  cursor.close()

We query the table to see the data in it before we make any changes.

'''
Queries the 'playlist' table in 'doltdb' database.
'''
def select_from_table(cnx: mysql.connector.connection):
  print("\n--querying 'playlist' table\n")
  cursor = cnx.cursor()
  cursor.execute("SELECT id, artist_name, song_name FROM playlist")
  for (id, artist_name, song_name) in cursor:
    print('{}. "{}" by {}'.format(id, song_name, artist_name))
  cursor.close()

Then, we make some changes to the table. This includes inserting, deleting an updating a row.

'''
Insert, delete and update rows on 'playlist' table.
'''
def make_changes_to_table(cnx: mysql.connector.connection):
  print("\n--making changes to 'playlist' table\n")
  cursor = cnx.cursor()
  cursor.execute("insert into playlist (artist_name, song_name) values ('Taylor Swift', 'Paper Rings');")
  cursor.execute("delete from playlist where id = 2;")
  cursor.execute("update playlist set song_name = 'Everything Goes On' where artist_name = 'Porter Robinson';")
  cursor.close()

Let's create a Dolt commit.

'''
Commits any current changes.
'''
def commit(cnx: mysql.connector.connection, msg: str):
  print("\n--committing changes\n")
  cursor = cnx.cursor()
  # equivalent to "CALL DOLT_COMMIT('-Am', 'commit message')"
  cursor.callproc("DOLT_COMMIT", ("-Am", msg))
  cursor.close()

Now, we can diff data in playlist table between HEAD(the current commit) and HEAD~ (the commit just before HEAD).

We can also use commit hashes and branch names to show the diff between them. More information on this function can be found on our docs.

'''
Show diff between current HEAD and the last commit before it.
'''
def diff_on_playlist(cnx: mysql.connector.connection):
  print("\n--showing the diff on 'playlist' table\n")
  cursor = cnx.cursor()
  cursor.execute("select * from dolt_diff('HEAD~', 'HEAD', 'playlist');")
  for (to_id, to_artist_name, to_song_name, to_commit, to_commit_date, 
       from_id, from_artist_name, from_song_name, from_commit, from_commit_date, diff_type) in cursor:
    if diff_type == "added":
      print('row {}: \nTO:   {}, {}, {}\n'.format(diff_type, to_id, to_artist_name, to_song_name))
    elif diff_type == "removed":
      print('row {}: \nFROM: {}, {}, {}\n'.format(diff_type, from_id, from_artist_name, from_song_name))
    else:
      print('row {}: \nFROM: {}, {}, {} \nTO:   {}, {}, {}\n'.format(diff_type, 
       from_id, from_artist_name, from_song_name, to_id, to_artist_name, to_song_name))
  cursor.close()

Here, we check out to the main branch.

'''
Switch to an existing branch.
'''
def checkout_branch(cnx: mysql.connector.connection, branch: str):
  print("\n--switching to a different branch\n")
  cursor = cnx.cursor()
  cursor.callproc("DOLT_CHECKOUT", (branch,)) 
  cursor.close()

This is the fun part where we merge mybranch containing the recent changes to main branch.

'''
Merge a branch.
'''
def merge_branch(cnx: mysql.connector.connection, branch: str):
print("\n--merging a branch\n")
cursor = cnx.cursor()
cursor.callproc("DOLT_MERGE", (branch,))
cursor.close()

Then, we can check the merged commit from the commit log of main branch.

'''
Print the commit log.
'''
def dolt_log(cnx: mysql.connector.connection):
  print("\n--showing the commit log\n")
  cursor = cnx.cursor()
  cursor.execute("SELECT commit_hash, committer, message FROM dolt_log")
  print('commit_hash, committer, message')
  for (commit_hash, committer, message) in cursor:
    print('{}, {}, {}'.format(commit_hash, committer, message))
  cursor.close()

Let's run it

When we run this code, we should see the following result. Each section shows the code broken down to each step we took. This includes querying the playlist table before changes, making some changes to the table, committing those changes and showing diff on the changes we made. The diff shows whether the row was deleted or added with associated row values, and it shows the updated row with before and after the change values. Then, we switch to main branch to merge mybranch changes to it. We can see playlist table data before and after merge on main branch. We query the commit log and see that the commit we made in mybranch is now merged in main.

Successfully connected to Dolt server!

--checking current branch

Current branch: mybranch

--querying 'playlist' table

1. "Shelter" by Porter Robinson
2. "Beautiful Things" by Benson Boone

--making changes to 'playlist' table


--committing changes


--showing the diff on 'playlist' table

row modified: 
FROM: 1, Porter Robinson, Shelter 
TO:   1, Porter Robinson, Everything Goes On

row removed: 
FROM: 2, Benson Boone, Beautiful Things

row added: 
TO:   3, Taylor Swift, Paper Rings


--switching to a different branch


--checking current branch

Current branch: main

--querying 'playlist' table

1. "Shelter" by Porter Robinson
2. "Beautiful Things" by Benson Boone

--merging a branch


--querying 'playlist' table

1. "Everything Goes On" by Porter Robinson
3. "Paper Rings" by Taylor Swift

--showing the commit log

commit_hash, committer, message
4nslg3b0f3p9nignmflapnol09sr0gl3, root, commit insert, delete and update changes
tii5lgjci672vg3tkl6hc1j8f1kjhkt9, root, Create a sample table with rows
c4c8tdeh6ego1nks66g47896824hc4tp, jennifersp, Initialize data repository
Disconnecting from Dolt server!

Developing data on a different branch different from main is a great way to keep data safe and avoid conflicts with other teammates' work.

Conclusion

Dolt can be used with most SQL tools of any type that are used to connect to MySQL database as Dolt is MySQL-compatible. We encourage you to try out Dolt using your favorite tool. We have made demonstration blogs for many of the tools out there. If you do not see a tool you use, try connecting it to Dolt server or feel free to reach out to us. We will be happy to help you or to do a demo blog for you. We can be found on our Discord channel.

Blog

Getting started

Create sample data

Let's set up the database connection using mysql.connector in Python

Let's run it

Conclusion

Get started with Dolt

Let's set up the database connection using `mysql.connector` in Python