Will & Skill Developers

Will & Skill Developers

Thoughts, snippets and ideas from the team at Will & Skill AB, Stockholm.

Faisal Mahmud

“The mind is not a vessel to be filled, but a fire to be kindled.” ― Plutarch


Tips when migrating from SQLite to MySQL

Faisal MahmudFaisal Mahmud

A lot of small projects are nice to start with SQLite to get going. When the project grows it is nicer to have a proper database engine like MySQL. Here are few tips that might be handy when you want to migrate from SQLite to MySQL.

1. Read the tutorial listed below

Migrating from sqlite to mysql

2. When creating the database in MySQL shell set the character set and collation

CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

3. Set table engine to InnoDB in MySQL dump file you get it from the Python script in tutorial above

CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;

4. The python script does not set the AUTO INCREMENT flag on the ID columns

Use a text editor of your preference and add AUTO INCREMENT to every ID field and you can also add IF NOT EXISTS if your adding data to already an existing table.

CREATE TABLE IF NOT EXISTS `account_state` (  
  `code` varchar(2) NOT NULL,
  `tax_rate` decimal(10,0) NOT NULL,
  `title` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  UNIQUE KEY `title` (`title`)
Faisal Mahmud

Faisal Mahmud

“The mind is not a vessel to be filled, but a fire to be kindled.” ― Plutarch