Generic base class for MySQL in python using PyMySQL

Whenever I write some code in python that involves a MySQL database, I use this base class that contains all the generic code for the basic CRUD operations. This allows me to focus on writing the queries instead of writing the same boilerplate code every time. It uses the client library PyMySQL and will take care of opening and disposing the connections to the database as well as preventing SQL injection by using parameterized queries. Just wanted to share it so that others can use it too.

Methods

__execute and __openConnection are private methods used internally in the class.

execute is intended for insert, update and delete statements and returns the number of affected rows in the table.

select_all is for select statements and returns a list of the rows that satisfy the query condition(s).

select_one is also for select statements and returns the first row that satisfies the query condition(s).

import pymysql.cursors

class SqlBase(object):
    def __init__(self, host, user, password, database):
        self.host = host
        self.user = user
        self.password = password
        self.database = database

    def execute(self, query:str, parameters:list=[]):
        return self.__execute(query, parameters).rowcount

    def select_all(self, query:str, parameters:list=[]):
        cursor = self.__execute(query, parameters)
        return cursor.fetchall()

    def select_one(self, query:str, parameters:list=[]):
        cursor = self.__execute(query, parameters)
        return cursor.fetchone()

    def __execute(self, query:str, parameters:list=[]):
        try:
            with self.__openConnection() as connection:
                with connection.cursor() as cursor:
                    cursor.execute(query, parameters)
                    connection.commit()
                    return cursor
        except pymysql.Error as e:
           print(f"Error while executing query [{query}]: {str(e)}")

    def __openConnection(self):
        try:
            return pymysql.connect(host=self.host, user=self.user, password=self.password, database=self.database, cursorclass=pymysql.cursors.DictCursor)
        except pymysql.Error as e:
           print(f"Error while opening sql connection: {str(e)}")

Example

Create a class that inherits the SqlBase class and call the base constructor with the database credentials. In this example I have made a user repository with examples of the CRUD operations by using execute, select_one and select_all. Notice how the values are parsed as a separate list of parameters rather than being inserted directly into the query itself. This is very important in order to avoid SQL injection attacks in case the values comes from user input like query parameters.

from sql_base import SqlBase
  
class UserRepository(SqlBase):
    def __init__(self):
        SqlBase.__init__(self, "host", "user", "password", "database")
  
    def insert(self, username, email):
        query = "INSERT INTO users(username, email) VALUES (%s,%s)"
        return self.execute(query, [username, email])
          
    def get(self, username):
        query = "SELECT id, username, email FROM users WHERE username=%s"
        return self.select_one(query, [username])
      
    def get_all(self):
        query = "SELECT id, username, email FROM users"
        return self.select_all(query)
 
    def update(self, username, email):
        query = "UPDATE users SET email=%s WHERE username=%s"
        return self.execute(query, [email, username])
          
    def delete(self, username):
        query = "DELETE FROM users WHERE username=%s"
        return self.execute(query, [username])

 

Display flag icons in language dropdown in Sitecore 9 Forms Builder

This blog post is an extension of an earlier post I wrote on how to sort the languages in Sitecore 9 Forms Builder. You can read it here https://blog.fyhn.io/post/2019/02/24/sort-languages-in-sitecore-9-forms-builder-language-dropdown. To make it even easier to locate a specific language, this post focuses on displaying the flag icon for each language in the dropdown. The below image is the result we are going to achieve.

We are going to extend the forms.getLanguages pipeline (located in App_Config\Sitecore\ExperienceForms\Sitecore.ExperienceForms.Pipelines.Client.config) with a processor responsible for resolving the path to the language icon for each language and adding it to the data model.

1. Extend the data model

First, we have to extend the LanguageListItem class with a property containing the path to the icon. Create an ExtendedLanguageListItem class that inherits LanguageListItem and add the icon path property.

public class ExtendedLanguageListItem : LanguageListItem
{
    public string IconPath { get; }

    public ExtendedLanguageListItem(LanguageListItem languageListItem, string iconPath)
    {
        Name = languageListItem.Name;
        DisplayName = languageListItem.DisplayName;
        Text = languageListItem.Text;
        Description = languageListItem.Description;
        HasVersions = languageListItem.HasVersions;
        IconPath = iconPath;
    }
}

2. Create a pipeline processor responsible for resolving the icon path for each language

The processor iterates the languages and uses the GetIconPath method to resolve the path. Next, it replaces the LanguageListItem objects in the pipeline arguments with our ExtendedLanguageListItem containing the path.

public class GetLanguageIconsProcessor : MvcPipelineProcessor<GetLanguagesEventArgs>
{
    public override void Process(GetLanguagesEventArgs args)
    {
        var iconPaths = new Dictionary<string, string>();
        foreach (var itemLanguage in args.ItemLanguages)
            iconPaths.Add(itemLanguage.Name, $"/-/icon/{GetIconPath(itemLanguage, args.FormBuilderContext.Database)}");
        
        args.Languages = args.Languages.Select(lang => new ExtendedLanguageListItem(lang, iconPaths[lang.Name]));
    }

    private string GetIconPath(Language language, Database database)
    {
        string iconPath = language.GetIcon(database);

        if (string.IsNullOrEmpty(iconPath))
        {
            LanguageDefinition languageDefinition = LanguageDefinitions.GetLanguageDefinition(language);
            if (languageDefinition != null)
                iconPath = languageDefinition.Icon;
        }

        if (string.IsNullOrEmpty(iconPath))
            iconPath = "Office/32x32/flag_generic.png";

        return iconPath;
    }
}

3. Add img tag to the .cshtml file containing the markup

Finally, we have to add a img tag in the markup file in order to display the icon. The file is called FormsListControl.cshtml and located in the sitecore\shell\client\Applications\FormsBuilder\Layouts\Renderings\FormsListControl folder. Navigate to line 194 and add the img tag as shown below.

...
<div class="sc-listcontrol-tile-content">
    <!-- Add the below line -->
    <img style="margin-right:10px;float:left" src="{{ iconPath }}" border="0" alt="" width="32px" height="32px">
    {{{ListControl:GetTileTemplate @("@root.Settings.Id") this}}}
</div>
...

Done! Don't forget to add your pipeline processor to the config file.

If you want to add the language icons in the content editor, use this post: https://xtremdev.com/2017/10/20/sort-language-list-and-show-language-flags/

And for the experience editor, use this post: http://www.xcentium.com/blog/2016/08/05/language-flags-not-displayed

Sort languages in Sitecore 9 Forms Builder language dropdown

The sort order in the language dropdown in Sitecore 9 Forms Builder seems pretty random, as seen in the below screenshot. This is rather confusing when having a lot of languages (in this case the customer has 95 languages). Therefore, I will show you how to make a custom sort order of the list.

The data in the list is fetched from a pipeline called forms.getLanguages (located in App_Config\Sitecore\ExperienceForms\Sitecore.ExperienceForms.Pipelines.Client.config). We can sort the list by extending the pipeline with our own processor handling the sorting. Below is an example that sorts the list using Sitecores own LanguageComparer class. This class sorts the languages so that it matches the sort order of the language definition items located under /sitecore/system/Languages, but you can make your own order, e.g. by display name or item name.

public class SortLanguagesProcessor : MvcPipelineProcessor<GetLanguagesEventArgs>
{
    public override void Process(GetLanguagesEventArgs args)
    {
        var sortedLanguageNames = args.ItemLanguages
            .OrderBy(item => item, new LanguageComparer(args.FormBuilderContext.Database))
            .Select(item => item.Name)
            .ToList();
        args.Languages = args.Languages.OrderBy(lang => sortedLanguageNames.IndexOf(lang.Name));
    }
}

Note that the pipeline arguments contains two lists of languages: a list of Language objects and another list of LanguageListItem objects. The latter is the one you want to sort, as this is the data sent to the view. Last but not least, don't forget to add your processor to the config file.

This is the result of my sorting implementation, where the order follows the same order I have sorted the language definition items.

If you want to make a custom sorting of the list in the content editor, use this post: https://xtremdev.com/2017/10/20/sort-language-list-and-show-language-flags/

And for the experience editor, use this post and implement the sorting in the ExtendedLanguageGallery.cs class: http://www.xcentium.com/blog/2016/08/05/language-flags-not-displayed

Add rendering to an item in Sitecore programmatically

If you need to add a rendering to an item in Sitecore programmatically, you can use the following method which takes five parameters:

1. The item on which the rendering should be added
2. The ID of the rendering
3. The position in the presentation details, where 0 is the first (top one)
4. The placeholder in which the rendering is inserted
5. If the rendering requires a datasource this goes here (absolute path or ID)

public void AddRendering(Item item, ID renderingId, int renderingPosition, string placeholder, string datasource = "")
{
    string renderingsXml = LayoutField.GetFieldValue(item.Fields[FieldIDs.LayoutField]);
    var layoutDefinition = LayoutDefinition.Parse(renderingsXml);

    if (layoutDefinition.Devices == null || layoutDefinition.Devices.Count == 0)
        throw new Exception("No available devices");

    var renderingDefinition = new RenderingDefinition
    {
        ItemID = renderingId.ToString(),
        Placeholder = placeholder,
        Datasource = datasource
    };

    foreach (DeviceDefinition device in layoutDefinition.Devices)
    {
        device.Insert(renderingPosition, renderingDefinition);
    }

    LayoutField.SetFieldValue(item.Fields[FieldIDs.LayoutField], layoutDefinition.ToXml());
}

Note that when dealing with layouts it is important to get and set the field value with LayoutField.Get/SetFieldValue(). If you fail to do this you will not process layout deltas correctly and may instead override all fields (breaking full inheritance), or attempt to get the layout definition for a delta value, which will result in you wiping the layout details when they get saved.