Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Create .db file from .sql file

My database is MySQL. I want to create .db sqlite file from my .sql file. when i am trying to achieve that using .read command i am getting many errors because the format which the sqlite desire does not matches with my format. My database is so big that i can not manually change something.

Below are the error messages which I got while performing .read operation.

(env) PS C:UsersadminDownloadsAdvanced-QA-and-RAG-Series-mainQNA> .sqlite3 111.db
SQLite version 3.45.3 2024-04-15 13:34:05 (UTF-16 console I/O)
Enter ".help" for usage hints.
sqlite> . readProgram interrupted.
(env) PS C:UsersadminDownloadsAdvanced-QA-and-RAG-Series-mainQNA> .sqlite3 111.db
SQLite version 3.45.3 2024-04-15 13:34:05 (UTF-16 console I/O)
Enter ".help" for usage hints.
Error: cannot open "R2..efew"
sqlite> .read adgf.sql
Error: cannot open "adgf.sql"
sqlite> read R2.sql
   ...>
   ...>
   ...> .read R2.sql
   ...> ^X^X
   ...> exit()
(env) PS C:UsersadminDownloadsAdvanced-QA-and-RAG-Series-mainQNA> .sqlite3 111.db
SQLite version 3.45.3 2024-04-15 13:34:05 (UTF-16 console I/O)
Enter ".help" for usage hints.
sqlite> .read R2.sql
Parse error near line 17: near "SET": syntax error
  SET NAMES utf8mb4;
  ^--- error here
Parse error near line 18: near "SET": syntax error
  SET FOREIGN_KEY_CHECKS = 0;
  ^--- error here
Parse error near line 24: near "AUTO_INCREMENT": syntax error
  ABLE `app_version`  (   `id` smallint(4) NOT NULL AUTO_INCREMENT,   `askupdate
                                      error here ---^
Parse error near line 40: near "CHARACTER": syntax error
    `id` smallint(9) NOT NULL,   `name` varchar(40) CHARACTER SET utf8 COLLATE u
                                      error here ---^
Parse error near line 52: near "'WORKING'": syntax error
     `date` date NOT NULL,   `attendance_type` enum('WORKING','ABSENT','HOLIDAY'
                                      error here ---^
Parse error near line 95: near "AUTO_INCREMENT": syntax error
  E `attendance_request`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `user_id`
                                      error here ---^
Parse error near line 116: near "AUTO_INCREMENT": syntax error
  E `attendance_setting`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `company_i
                                      error here ---^
Parse error near line 136: near "AUTO_INCREMENT": syntax error
  REATE TABLE `city`  (   `id` smallint(3) NOT NULL AUTO_INCREMENT,   `name` var
                                      error here ---^
Parse error near line 149: near "AUTO_INCREMENT": syntax error
  TE TABLE `company`  (   `id` smallint(3) NOT NULL AUTO_INCREMENT,   `name` var
                                      error here ---^
Parse error near line 159: near "AUTO_INCREMENT": syntax error
  TABLE `department`  (   `id` smallint(3) NOT NULL AUTO_INCREMENT,   `name` var
                                      error here ---^
Parse error near line 169: near "AUTO_INCREMENT": syntax error
  ABLE `designation`  (   `id` smallint(3) NOT NULL AUTO_INCREMENT,   `name` var
                                      error here ---^
Parse error near line 180: near "AUTO_INCREMENT": syntax error
  REATE TABLE `document`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `document_
                                      error here ---^
Parse error near line 203: near "AUTO_INCREMENT": syntax error
  LE `document_type`  (   `id` smallint(3) NOT NULL AUTO_INCREMENT,   `name` var
                                      error here ---^
Parse error near line 213: near "AUTO_INCREMENT": syntax error
  EATE TABLE `grade`  (   `id` smallint(3) NOT NULL AUTO_INCREMENT,   `name` var
                                      error here ---^
Parse error near line 223: near "AUTO_INCREMENT": syntax error
  CREATE TABLE `holiday`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `date` dat
                                      error here ---^
Parse error near line 242: near "AUTO_INCREMENT": syntax error
  LE `late_mark_setting`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `from_min`
                                      error here ---^
Parse error near line 259: near "AUTO_INCREMENT": syntax error
  E TABLE `leave_ledger`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `user_id`
                                      error here ---^
Parse error near line 278: near "AUTO_INCREMENT": syntax error
   TABLE `leave_request`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `user_id`
                                      error here ---^
Parse error near line 301: near "'WORKING'": syntax error
  nt(3) NULL DEFAULT NULL,   `attendance_type` enum('WORKING','ABSENT','HOLIDAY'
                                      error here ---^
Parse error near line 316: near "AUTO_INCREMENT": syntax error
   TABLE `leave_setting`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `user_id`
                                      error here ---^
Parse error near line 331: near "AUTO_INCREMENT": syntax error
  TABLE `leave_type`  (   `id` smallint(3) NOT NULL AUTO_INCREMENT,   `name` var
                                      error here ---^
Parse error near line 344: near "AUTO_INCREMENT": syntax error
  E TABLE `location`  (   `id` smallint(3) NOT NULL AUTO_INCREMENT,   `name` var
                                      error here ---^
Parse error near line 362: near "AUTO_INCREMENT": syntax error
  CREATE TABLE `log`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `created_by` i
                                  error here ---^
Parse error near line 378: near "AUTO_INCREMENT": syntax error
  REATE TABLE `menu`  (   `id` smallint(3) NOT NULL AUTO_INCREMENT,   `name` var
                                      error here ---^
Parse error near line 389: near "AUTO_INCREMENT": syntax error
  TE TABLE `message_log`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `user_id`
                                      error here ---^
Parse error near line 410: near "AUTO_INCREMENT": syntax error
  BLE `message_template`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `from_addr
                                      error here ---^
Parse error near line 432: near "AUTO_INCREMENT": syntax error
  E TABLE `notification`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `notificat
                                      error here ---^
Parse error near line 451: near "USING": syntax error
  ULL,   PRIMARY KEY (`notification_id`, `user_id`) USING BTREE ) ENGINE = InnoD
                                      error here ---^
Parse error near line 462: near "AUTO_INCREMENT": syntax error
  reimbursement_request`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `user_id`
                                      error here ---^
Parse error near line 479: near "CHARACTER": syntax error
  REATE TABLE `session_track`  (   `id` varchar(50) CHARACTER SET utf8 COLLATE u
                                      error here ---^
Parse error near line 494: near "AUTO_INCREMENT": syntax error
  EATE TABLE `state`  (   `id` smallint(3) NOT NULL AUTO_INCREMENT,   `name` var
                                      error here ---^
Parse error near line 505: near "AUTO_INCREMENT": syntax error
  ATE TABLE `status`  (   `id` smallint(3) NOT NULL AUTO_INCREMENT,   `name` var
                                      error here ---^
Parse error near line 515: near "AUTO_INCREMENT": syntax error
  E TABLE `sub_menu`  (   `id` smallint(3) NOT NULL AUTO_INCREMENT,   `name` var
                                      error here ---^
Parse error near line 530: near "AUTO_INCREMENT": syntax error
  CREATE TABLE `user`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `name` varcha
                                   error here ---^
Parse error near line 558: near "(": syntax error
  ed_on` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0),   `created_by` int(11)
                                      error here ---^
Parse error near line 572: near "CHARACTER": syntax error
  `user_id` int(11) NOT NULL,   `password` char(42) CHARACTER SET utf8 COLLATE u
                                      error here ---^
Parse error near line 582: near "USING": syntax error
  (11) NULL DEFAULT NULL,   PRIMARY KEY (`user_id`) USING BTREE ) ENGINE = InnoD
                                      error here ---^
Parse error near line 605: near "CHARACTER": syntax error
  int(11) NOT NULL,   `attendance_code` varchar(15) CHARACTER SET utf8 COLLATE u
                                      error here ---^
Parse error near line 660: near "AUTO_INCREMENT": syntax error
   `user_device_mapping`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `created_o
                                      error here ---^
Parse error near line 676: near "USING": syntax error
  L DEFAULT 0.00,   PRIMARY KEY (`user_id`, `date`) USING BTREE ) ENGINE = InnoD
                                      error here ---^
Parse error near line 692: near "CHARACTER": syntax error
   `actual_json` json NULL,   `remark` varchar(100) CHARACTER SET utf8 COLLATE u
                                      error here ---^
Parse error near line 732: near "AUTO_INCREMENT": syntax error
  ABLE `user_status_log`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `createdby
                                      error here ---^
Parse error near line 748: near "AUTO_INCREMENT": syntax error
   TABLE `user_type`  (   `id` smallint(3) NOT NULL AUTO_INCREMENT,   `name` var
                                      error here ---^
Parse error near line 758: near "ALGORITHM": syntax error
  CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `PunchInOutMissingDay`
         ^--- error here
Parse error near line 763: near "FUNCTION": syntax error
  DROP FUNCTION IF EXISTS `camel_case`;
       ^--- error here
Parse error near line 764: near "delimiter": syntax error
  delimiter ;;
  ^--- error here
Parse error near line 765: near "FUNCTION": syntax error
  CREATE FUNCTION `camel_case`(str varchar(128))  RETURNS varchar(128) CHARSET u
         ^--- error here
Parse error near line 769: near "DECLARE": syntax error
  DECLARE sub, proper VARCHAR(128) DEFAULT '';
  ^--- error here
Parse error near line 771: near "if": syntax error
  if length(trim(str)) > 0 then     WHILE pos > 0 DO         set pos = locate('
  ^--- error here
Parse error near line 774: near "if": syntax error
  if pos = 0 then             set sub = lower(trim(substr(trim(str),n)));
  ^--- error here
Parse error near line 776: near "else": syntax error
  else             set sub = lower(trim(substr(trim(str),n,pos-n)));
  ^--- error here
Parse error near line 778: near "if": syntax error
  end if;
      ^--- error here
Parse error near line 780: near "set": syntax error
  set proper = concat_ws(' ', proper, concat(upper(left(sub,1)),substr(sub,2)));
  ^--- error here
Parse error near line 781: near "set": syntax error
  set n = pos + 1;
  ^--- error here
Parse error near line 782: near "WHILE": syntax error
  END WHILE;
      ^--- error here
Parse error near line 783: near "if": syntax error
  end if;
      ^--- error here
Parse error near line 785: near "RETURN": syntax error
  RETURN trim(proper);
  ^--- error here
Runtime error near line 786: cannot commit - no transaction is active
Parse error near line 788: near "delimiter": syntax error
  delimiter ;
  ^--- error here
Parse error near line 793: near "FUNCTION": syntax error
  DROP FUNCTION IF EXISTS `getleavejson`;
       ^--- error here
Parse error near line 794: near "delimiter": syntax error
  delimiter ;;
  ^--- error here
Parse error near line 795: near "FUNCTION": syntax error
  CREATE FUNCTION `getleavejson`(`userid` int,`fromdatearg` date,`todatearg` dat
         ^--- error here
Parse error near line 799: near "into": syntax error
  select CONCAT('[',GROUP_CONCAT(lljson),']') into leavejson from ( select CONCA
                                error here ---^
Parse error near line 803: near "RETURN": syntax error
  RETURN leavejson;
  ^--- error here
Runtime error near line 804: cannot commit - no transaction is active
Parse error near line 806: near "delimiter": syntax error
  delimiter ;
  ^--- error here
Parse error near line 811: near "FUNCTION": syntax error
  DROP FUNCTION IF EXISTS `getpaiddays`;
       ^--- error here
Parse error near line 812: near "delimiter": syntax error
  delimiter ;;
  ^--- error here
Parse error near line 813: near "FUNCTION": syntax error
  CREATE FUNCTION `getpaiddays`(`userid` int,`fromdatearg` date,`todatearg` date
         ^--- error here
Parse error near line 817: near "into": syntax error
  nd a.duration is null then 0.5 else 1.0 end) end) into paiddays from attendanc
                                      error here ---^
Parse error near line 822: near "RETURN": syntax error
  RETURN paiddays;
  ^--- error here
Runtime error near line 823: cannot commit - no transaction is active
Parse error near line 825: near "delimiter": syntax error
  delimiter ;
  ^--- error here
Parse error near line 830: near "FUNCTION": syntax error
  DROP FUNCTION IF EXISTS `haversine`;
       ^--- error here
Parse error near line 831: near "delimiter": syntax error
  delimiter ;;
  ^--- error here
Parse error near line 832: near "FUNCTION": syntax error
  CREATE FUNCTION `haversine`(`lat1` double,`lon1` double,`lat2` double,`lon2` d
         ^--- error here
Runtime error near line 848: cannot commit - no transaction is active
Parse error near line 850: near "delimiter": syntax error
  delimiter ;
  ^--- error here
Parse error near line 852: near "SET": syntax error
  SET FOREIGN_KEY_CHECKS = 1;
  ^--- error here

I am expecting that it should successfully create my table schema and structure based on the one
which I provided

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    import sqlparse

# Read the MySQL SQL script
with open('R1.sql', 'r', encoding='utf-8') as file:
    sql_script = file.read()

# Replace MySQL-specific syntax with SQLite-compatible syntax
sql_script = sql_script.replace(' AUTO_INCREMENT', ' AUTOINCREMENT')
sql_script = sql_script.replace(' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci', '')
sql_script = sql_script.replace(' CHARACTER SET utf8 COLLATE utf8_general_ci', '')
sql_script = sql_script.replace(' SET FOREIGN_KEY_CHECKS = 0;', '')
sql_script = sql_script.replace(' SET FOREIGN_KEY_CHECKS = 1;', '')
sql_script = sql_script.replace(' ENGINE=InnoDB', '')
sql_script = sql_script.replace(' COLLATE utf8mb4_unicode_ci', '')
sql_script = sql_script.replace(' COLLATE utf8_general_ci', '')
sql_script = sql_script.replace(' USING BTREE', '')

# Additional replacements
sql_script = sql_script.replace(' enum(', ' text CHECK(')
sql_script = sql_script.replace('`', '')

# Drop any delimiter lines (used in MySQL for procedures/functions)
sql_script = sqlparse.format(sql_script, strip_comments=True)
sql_script = 'n'.join([line for line in sql_script.split('n') if not line.strip().lower().startswith('delimiter')])

# Save the modified SQL script for SQLite
with open('modified_sql_script.sql', 'w', encoding='utf-8') as file:
    file.write(sql_script)

  2. 0

    Plan A:

    1. Hand create the CREATE statements for the target.
    2. Dump the data as a CSV file (see source documentation for how to do this).
    3. Load the CSV file (see target documentation).

    Plan B:

    See target documentation for a utility that might do the conversion for you. For example, MySQL Workbench is pretty good at converting external data to MySQL.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search