techouse / sqlite3-to-mysql Goto Github PK

View Code? Open in Web Editor NEW

340.0 6.0 48.0 348 KB

Transfer data from SQLite to MySQL

License: MIT License

Python 99.86% Dockerfile 0.14%

mysql tool transfer-data python command-line sqlite

sqlite3-to-mysql's Introduction

SQLite3 to MySQL

A simple Python tool to transfer data from SQLite 3 to MySQL.

How to run

pip install sqlite3-to-mysql
sqlite3mysql --help

Usage

Usage: sqlite3mysql [OPTIONS]

  Transfer SQLite to MySQL using the provided CLI options.

Options:
  -f, --sqlite-file PATH          SQLite3 database file  [required]
  -t, --sqlite-tables TUPLE       Transfer only these specific tables (space
                                  separated table names). Implies --without-
                                  foreign-keys which inhibits the transfer of
                                  foreign keys.
  -X, --without-foreign-keys      Do not transfer foreign keys.
  -W, --ignore-duplicate-keys     Ignore duplicate keys. The default behavior
                                  is to create new ones with a numerical
                                  suffix, e.g. 'exising_key' ->
                                  'existing_key_1'
  -d, --mysql-database TEXT       MySQL database name  [required]
  -u, --mysql-user TEXT           MySQL user  [required]
  -p, --prompt-mysql-password     Prompt for MySQL password
  --mysql-password TEXT           MySQL password
  -h, --mysql-host TEXT           MySQL host. Defaults to localhost.
  -P, --mysql-port INTEGER        MySQL port. Defaults to 3306.
  -S, --skip-ssl                  Disable MySQL connection encryption.
  -i, --mysql-insert-method [UPDATE|IGNORE|DEFAULT]
                                  MySQL insert method. DEFAULT will throw
                                  errors when encountering duplicate records;
                                  UPDATE will update existing rows; IGNORE
                                  will ignore insert errors. Defaults to
                                  IGNORE.
  -E, --mysql-truncate-tables     Truncates existing tables before inserting
                                  data.
  --mysql-integer-type TEXT       MySQL default integer field type. Defaults
                                  to INT(11).
  --mysql-string-type TEXT        MySQL default string field type. Defaults to
                                  VARCHAR(255).
  --mysql-text-type [MEDIUMTEXT|TEXT|TINYTEXT|LONGTEXT]
                                  MySQL default text field type. Defaults to
                                  TEXT.
  --mysql-charset TEXT            MySQL database and table character set
                                  [default: utf8mb4]
  --mysql-collation TEXT          MySQL database and table collation
  -T, --use-fulltext              Use FULLTEXT indexes on TEXT columns. Will
                                  throw an error if your MySQL version does
                                  not support InnoDB FULLTEXT indexes!
  --with-rowid                    Transfer rowid columns.
  -c, --chunk INTEGER             Chunk reading/writing SQL records
  -l, --log-file PATH             Log file
  -q, --quiet                     Quiet. Display only errors.
  --debug                         Debug mode. Will throw exceptions.
  --version                       Show the version and exit.
  --help                          Show this message and exit.

Docker

If you don't want to install the tool on your system, you can use the Docker image instead.

docker run -it \
    --workdir $(pwd) \
    --volume $(pwd):$(pwd) \
    --rm ghcr.io/techouse/sqlite3-to-mysql:latest \
    --sqlite-file baz.db \
    --mysql-user foo \
    --mysql-password bar \
    --mysql-database baz \
    --mysql-host host.docker.internal

This will mount your host current working directory (pwd) inside the Docker container as the current working directory. Any files Docker would write to the current working directory are written to the host directory where you did docker run. Note that you have to also use a special hostname host.docker.internal to access your host machine from inside the Docker container.

Homebrew

If you're on macOS, you can install the tool using Homebrew.

brew tap techouse/sqlite3-to-mysql
brew install sqlite3-to-mysql
sqlite3mysql --help

sqlite3-to-mysql's People

Contributors

Stargazers

Watchers

Forkers

zdk123 minipada walkerwang731 chengluyu firecontroller1847 bamboowind siserin scitao jgodin-c2c liyongxian apogee indigoparadox tslater afansky hisygg mapleknight skrashevich sunyi00 ken6317461024 themacte 59de44955ebd lazykart riyanhax nelli-dev ba7jcm truepillpabloschaffner alexanikanov precept123 hamzabawumia stillywud matteogaito rekliner lad47 jeroen-smit makmoinee david0718 holgerflick 2maz tlack cwbudde ligzy nwespe surapuramakhil larseisenblatt drosanda klausgreulich friendlee-pro

sqlite3-to-mysql's Issues

Auto_increment not working

Auto_increment is not added

Can not correctly convert sqlite's CHAR(2500) type

Describe the bug

(venv) PS C:\Users\Administrator\Desktop\backend> sqlite3mysql -f C:\Users\Administrator\Desktop\backend\gamedata\gamedata.db -d screeps -u Mofeng --mysql-password  foobar -E
2022-08-11 21:20:36 INFO     Truncating table room_info
2022-08-11 21:20:36 INFO     Transferring table room_info
100%|████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 77776/77776 [00:32<00:00, 2368.99it/s] 
2022-08-11 21:21:09 ERROR    MySQL failed creating table room_terrain: 1074 (42000): Column length too big for column 'terrain' (max = 255); use BLOB or TEXT instead 
1074 (42000): Column length too big for column 'terrain' (max = 255); use BLOB or TEXT instead
(venv) PS C:\Users\Administrator\Desktop\backend>

The type of column terrain is 'CHAR(2500)'.

Types in table room_terrain:

cid	name	type
0	id	INTEGER
1	room	TEXT
2	shard	TEXT
3	terrain	CHAR(2500)

I also tried to add options like --mysql-text-type MEDIUMTEXT --mysql-string-type TEXT , which does not help.

System Information

| software               | version                              |
|------------------------|--------------------------------------|
| sqlite3-to-mysql       | 1.4.15                               |
|                        |                                      |
| Operating System       | Windows 10                           |
| Python                 | CPython 3.10.6                       |
| MySQL                  | Server version: 8.0.30 MySQL Community Server |
| SQLite                 | 3.37.2                               |
|                        |                                      |
| click                  | 8.0.4                                |
| mysql-connector-python | 8.0.30                               |
| pytimeparse            | 1.1.8                                |
| simplejson             | 3.17.6                               |
| six                    | 1.16.0                               |
| tabulate               | 0.8.10                               |
| tqdm                   | 4.64.0                               |

Additional context
Add any other context about the problem here.

In case of errors please run the same command with --debug. This option is only available on v1.4.12 or greater.

(venv) PS C:\Users\Administrator\Desktop\backend> sqlite3mysql -f C:\Users\Administrator\Desktop\backend\gamedata\gamedata.db -d screeps -u Mofeng --mysql-password  foobar -E --debug
2022-08-11 21:37:50 INFO     Truncating table room_info 
2022-08-11 21:37:50 INFO     Transferring table room_info 
100%|████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 77776/77776 [00:29<00:00, 2612.82it/s] 
2022-08-11 21:38:20 ERROR    MySQL failed creating table room_terrain: 1074 (42000): Column length too big for column 'terrain' (max = 255); use BLOB or TEXT instead 
Traceback (most recent call last): 
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\runpy.py", line 196, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "C:\Users\Administrator\AppData\Local\Programs\Python\Python310\lib\runpy.py", line 86, in _run_code
    exec(code, run_globals)
  File "C:\Users\Administrator\Desktop\backend\venv\Scripts\sqlite3mysql.exe\__main__.py", line 7, in <module>
  File "c:\users\administrator\desktop\backend\venv\lib\site-packages\click\core.py", line 1128, in __call__
    return self.main(*args, **kwargs)
  File "c:\users\administrator\desktop\backend\venv\lib\site-packages\click\core.py", line 1053, in main
    rv = self.invoke(ctx)
  File "c:\users\administrator\desktop\backend\venv\lib\site-packages\click\core.py", line 1395, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "c:\users\administrator\desktop\backend\venv\lib\site-packages\click\core.py", line 754, in invoke
    return __callback(*args, **kwargs)
  File "c:\users\administrator\desktop\backend\venv\lib\site-packages\sqlite3_to_mysql\cli.py", line 204, in cli
    converter.transfer()
  File "c:\users\administrator\desktop\backend\venv\lib\site-packages\sqlite3_to_mysql\transporter.py", line 700, in transfer
    self._create_table(table["name"], transfer_rowid=transfer_rowid)
  File "c:\users\administrator\desktop\backend\venv\lib\site-packages\sqlite3_to_mysql\transporter.py", line 380, in _create_table
    self._mysql_cur.execute(sql)
  File "c:\users\administrator\desktop\backend\venv\lib\site-packages\mysql\connector\cursor.py", line 1242, in execute
    self._prepared = self._connection.cmd_stmt_prepare(operation)
  File "c:\users\administrator\desktop\backend\venv\lib\site-packages\mysql\connector\connection.py", line 1484, in cmd_stmt_prepare
    result = self._handle_binary_ok(packet)
  File "c:\users\administrator\desktop\backend\venv\lib\site-packages\mysql\connector\connection.py", line 1424, in _handle_binary_ok
    raise get_exception(packet)
mysql.connector.errors.ProgrammingError: 1074 (42000): Column length too big for column 'terrain' (max = 255); use BLOB or TEXT instead
(venv) PS C:\Users\Administrator\Desktop\backend>

PRAGMAS automatic_index seems to be ignored

Describe the bug
I try to transform sqlite database generate by the headless cms strapi.io to a mysql database.

But no mysql id field has auto_increment.

The field in sqllite does have a PRAGMA Automatic_index

Expected behaviour
sqlite3-to-mysql do not add the AUTO_INCREMENT option.

ALTER TABLE `contents`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `contents_slug_unique` (`slug`);

ALTER TABLE `contents`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
COMMIT;

Actual result

ALTER TABLE `contents`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `contents_slug_unique` (`slug`);

System Information

$ sqlite3mysql --version

sqlite3mysql --version
| software               | version                                                                     |
|------------------------|-----------------------------------------------------------------------------|
| sqlite3-to-mysql       | 1.4.1                                                                       |
|                        |                                                                             |
| Operating System       | Linux 5.4.0-74-generic                                                      |
| Python                 | CPython 3.8.5                                                               |
| MySQL                  | mysql  Ver 14.14 Distrib 5.7.34, for Linux (x86_64) using  EditLine wrapper |
| SQLite                 | 3.31.1                                                                      |
|                        |                                                                             |
| click                  | 7.0                                                                         |
| mysql-connector-python | 8.0.25                                                                      |
| pytimeparse            | 1.1.8                                                                       |
| simplejson             | 3.16.0                                                                      |
| six                    | 1.14.0                                                                      |
| tabulate               | 0.8.9                                                                       |
| tqdm                   | 4.61.1                                                                      |

Support converting (or at least ignoring) generated fields

Describe the bug

MySQL transfer failed inserting data into table tablename: 1054 (42S22): Unknown column 'name' in 'field list'

Expected behaviour

Accept (or ignore) generated fields.

Actual result

See above

System Information

| software               | version                              |
|------------------------|--------------------------------------|
| sqlite3-to-mysql       | 1.4.16                               |
|                        |                                      |
| Operating System       | Windows 10                           |
| Python                 | CPython 3.10.9                       |
| MySQL                  | MySQL client not found on the system |
| SQLite                 | 3.39.4                               |
|                        |                                      |
| click                  | 8.1.3                                |
| mysql-connector-python | 8.0.29                               |
| pytimeparse            | 1.1.8                                |
| simplejson             | 3.18.4                               |
| six                    | 1.16.0                               |
| tabulate               | 0.9.0                                |
| tqdm                   | 4.64.1                               |

Additional context

I'm comfortable with Python, so if you could give me some pointers on how to implement this fix (enhancement?), I'd be happy to try to cobble together a PR.

Conversion failure due to collation and unique indexes

Describe the bug
If an sqlite file with (default) BINARY collation has a column with a unique index and two values that differ only in case, the conversion will fail, because sqlite3mysql creates databases and tables with utf8mb4_general_ci case insensitive collation, and under that collation the values are not unique.

Expected behaviour
Conversion to work

Actual result
MySQL failed adding indices to table mytable: 1062 (23000): Duplicate entry '' for key 'mykey'

System Information

$ sqlite3mysql --version

| software               | version                                                                        |
|------------------------|--------------------------------------------------------------------------------|
| sqlite3-to-mysql       | 1.4.5                                                                          |
|                        |                                                                                |
| Operating System       | Linux 5.14.2                                                                   |
| Python                 | CPython 3.9.6                                                                  |
| MySQL                  | mysql  Ver 15.1 Distrib 10.5.10-MariaDB, for Linux (x86_64) using readline 8.1 |
| SQLite                 | 3.35.5                                                                         |
|                        |                                                                                |
| click                  | 8.0.1                                                                          |
| mysql-connector-python | 8.0.26                                                                         |
| pytimeparse            | 1.1.8                                                                          |
| simplejson             | 3.17.3                                                                         |
| six                    | 1.16.0                                                                         |
| tabulate               | 0.8.9                                                                          |
| tqdm                   | 4.62.0                                                                         |

Additional context
Modifying sqlite3mysql to use utf8mb4_bin collation worked fine.

Documentation links:
https://www.sqlite.org/datatype3.html#collation
https://mariadb.com/kb/en/supported-character-sets-and-collations/

Syntax error

Describe the bug
I'm trying to convert an sqlite file to mysql / MariaDB but I get the following error:
2022-08-09 12:23:45 ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-assistant_v2' at line 1

Expected behaviour
Conversion to occur without error

Actual result
Got error as above
Traceback when running with --debug flag:

Traceback (most recent call last):
File "/home/gp/.local/bin/sqlite3mysql", line 8, in
sys.exit(cli())
File "/usr/lib/python3/dist-packages/click/core.py", line 1128, in call
return self.main(*args, **kwargs)
File "/usr/lib/python3/dist-packages/click/core.py", line 1053, in main
rv = self.invoke(ctx)
File "/usr/lib/python3/dist-packages/click/core.py", line 1395, in invoke
return ctx.invoke(self.callback, **ctx.params)
File "/usr/lib/python3/dist-packages/click/core.py", line 754, in invoke
return __callback(*args, **kwargs)
File "/home/gp/.local/lib/python3.10/site-packages/sqlite3_to_mysql/cli.py", line 179, in cli
converter = SQLite3toMySQL(
File "/home/gp/.local/lib/python3.10/site-packages/sqlite3_to_mysql/transporter.py", line 169, in init
self._mysql.database = self._mysql_database
File "/home/gp/.local/lib/python3.10/site-packages/mysql/connector/connection.py", line 1136, in database
self.cmd_query("USE %s" % value)
File "/home/gp/.local/lib/python3.10/site-packages/mysql/connector/connection.py", line 922, in cmd_query
result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
File "/home/gp/.local/lib/python3.10/site-packages/mysql/connector/connection.py", line 732, in _handle_result
raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-assistant_v2' at line 1

I'm guessing it has something to do with the source file but it is quite large and not sure what the error is trying to say. Any ideas? Thanks!

progress bar

Is your feature request related to a problem? Please describe.
when going over a large database a progress indicator can be very useful i.e. that everything is performing well, how much is done, how much is left etc...

Describe the solution you'd like
a progress bar, either records done, left, megabytes written ... whatever

Describe alternatives you've considered
du the database directory repeatedly, i.e. to see space usage

Slow while transfering large tables

Describe the bug
For tables with like more than 10,000 rows. Why it takes so kuch time.
command i ran:
sqlite3mysql -f student_club.sqlite -d student_club -u .. -h ..-p -E -c 1000

Expected behaviour
While using tools like dbeaver its just matter of few seconds to transfer 10,000 rows.

Actual result
For transfering 1000 rows it is taking 500 seconds

System Information

sqlite3mysql --version

software	version
sqlite3-to-mysql	2.0.2

Operating System	Darwin 21.3.0
Python	CPython 3.9.16
MySQL	mysql Ver 8.0.28 for macos11 on x86_64 (MySQL Community Server - GPL)
SQLite	3.42.0

click	8.1.3
mysql-connector-python	8.0.33
pytimeparse2	1.7.1
simplejson	3.19.1
tabulate	0.9.0
tqdm	4.65.0


**Additional context**
Add any other context about the problem here.

Columns having default values not recognized by mariadb

Describe the bug
I have a sqlite file with this table:

CREATE TABLE IF NOT EXISTS "credentials_entity" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar(128) NOT NULL, "data" text NOT NULL, "type" varchar(32) NOT NULL, "nodesAccess" text NOT NULL, "createdAt" datetime(3) NOT NULL DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')), "updatedAt" datetime(3) NOT NULL DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')));

When using sqlite3-to-mysql, I get an error

Expected behaviour
I don't expect error. However, I don't have a clear idea how to fix it properly.

Actual result
I get the following error:

2022-03-29 15:41:12 ERROR    MySQL failed creating table credentials_entity: 1901 (HY000): Function or expression '`STRFTIME`()' cannot be used in the DEFAULT clause of `createdAt`
1901 (HY000): Function or expression '`STRFTIME`()' cannot be used in the DEFAULT clause of `createdAt

System Information

$ sqlite3mysql --version

| software               | version                                                                                   |
|------------------------|-------------------------------------------------------------------------------------------|
| sqlite3-to-mysql       | 1.4.14                                                                                    |
|                        |                                                                                           |
| Operating System       | Linux 4.19.0-18-amd64                                                                     |
| Python                 | CPython 3.7.3                                                                             |
| MySQL                  | mysql  Ver 15.1 Distrib 10.3.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 |
| SQLite                 | 3.27.2                                                                                    |
|                        |                                                                                           |
| click                  | 8.1.0                                                                                     |
| mysql-connector-python | 8.0.28                                                                                    |
| pytimeparse            | 1.1.8                                                                                     |
| simplejson             | 3.17.6                                                                                    |
| six                    | 1.12.0                                                                                    |
| tabulate               | 0.8.9                                                                                     |
| tqdm                   | 4.63.1                                                                                    |

Additional context
Add any other context about the problem here.

In case of errors please run the same command with --debug. This option is only available on v1.4.12 or greater.

text field with a default value report error while convert

definition of source table in sqlite is like this：
create table data_source (
...
uid text default 0
)
the key is the type of uid field is text,and it has a default value.
when convert to mysql,it report a error message:
MySQL failed creating table data_source: 1101 (42000): BLOB,TEXT,GEOMETRY or JSON column 'uid' can't have a default value

affect version: 1.4.9
but version 1.4.8 hasn't this issue

I have a look at the source code（create statement concat logic in file transporter.py）,then I see the difference：
version 1.4.8（line 304）：sql += " {name} {type} {notnull} {auto_increment}, ".format(
version 1.4.9（line 304）：sql += " {name} {type} {notnull} {dflt} {auto_increment}, ".format(

I can clearly see version 1.4.9 add the default clause,but mysql don't support BLOB,TEXT,GEOMETRY or JSON column have a default value

Transfer composite primary keys

The tool currently lacks the ability to transfer composite primary keys.

utf8 support

Is your feature request related to a problem? Please describe.
Hi, I'm trying to convert my sqlite 3 to mysql (using a cron so i can use it for some analytics) however i get the error:

2022-08-11 17:26:59 ERROR Character set 'utf8' unsupported
Character set 'utf8' unsupported

Describe the solution you'd like
is it difficult to add support for this, and i'm happy to donate.

Convertion hang on 'no such collation sequence: unicase' leading some tables crunched

Describe the bug
Hello techouse, today I wanted to convert fresh and clean anki database .anki2 (sqlite3 based) into mysql to retrieve the schema on phpmysql and work on it.

I succeed to retrieve the schema (aka ER Diagram) from the fresh sqlite3 database with anki created with:

anki -l en -b ankidir/

using after DBeaver-ce behind on collection.anki2 .
As you can see collection.anki2, a sqlite3 database contains 12 tables:

So what I did is to convert to mysql is

first create a mysql anki database for the user anki with phpMyAdmin
launch the conversion

sqlite3mysql -f ankidir/User\ 1/collection.anki2 -d anki -u anki -p

Expected behaviour
All table conversion Completed

Actual result

sqlite3mysql -f ankidir/User\ 1/collection.anki2 -d anki -u anki -p 
MySQL password: 
2021-09-14 20:36:30 INFO     Transferring table col
100%|█████████████████████████████████████████████| 1/1 [00:00<00:00, 22.26it/s]
2021-09-14 20:36:31 INFO     Adding index to column "mid" in table notes
2021-09-14 20:36:31 INFO     Adding index to column "csum" in table notes
2021-09-14 20:36:31 INFO     Adding index to column "usn" in table notes
2021-09-14 20:36:32 INFO     Adding index to column "odid" in table cards
2021-09-14 20:36:32 INFO     Adding index to column "did, queue, due" in table cards
2021-09-14 20:36:32 INFO     Adding index to column "nid" in table cards
2021-09-14 20:36:33 INFO     Adding index to column "usn" in table cards
2021-09-14 20:36:34 INFO     Adding index to column "cid" in table revlog
2021-09-14 20:36:34 INFO     Adding index to column "usn" in table revlog
2021-09-14 20:36:34 INFO     Transferring table deck_config
100%|█████████████████████████████████████████████| 1/1 [00:00<00:00, 21.88it/s]
2021-09-14 20:36:35 INFO     Transferring table config
100%|██████████████████████████████████████████| 15/15 [00:00<00:00, 341.32it/s]
no such collation sequence: unicase

System Information

$ sqlite3mysql --version

| software               | version                                                                                   |
|------------------------|-------------------------------------------------------------------------------------------|
| sqlite3-to-mysql       | 1.4.5                                                                                     |
|                        |                                                                                           |
| Operating System       | Linux 4.19.0-17-amd64                                                                     |
| Python                 | CPython 3.9.5                                                                             |
| MySQL                  | mysql  Ver 15.1 Distrib 10.3.29-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 |
| SQLite                 | 3.27.2                                                                                    |
|                        |                                                                                           |
| click                  | 8.0.1                                                                                     |
| mysql-connector-python | 8.0.26                                                                                    |
| pytimeparse            | 1.1.8                                                                                     |
| simplejson             | 3.17.3                                                                                    |
| six                    | 1.16.0                                                                                    |
| tabulate               | 0.8.9                                                                                     |
| tqdm                   | 4.62.1                                                                                    |

Additional context
If you take a look on phpMyAdmin designer.. you see whose tables has been crunched (5 disappeared) or even renamed in uppercase like fields.

To help you, here is a zip of ankidir folder

Option to skip duplicate index creation if key name already exists

Is your feature request related to a problem? Please describe.

We use this tool as part of a workflow that exports a database from MySQL -> Sqlite and then later re-imports that database back from Sqlite -> the same MySQL database. When it does so, it detects that all of the indices it is trying to create already exist, so it creates duplicate indices with a "_1" suffix using the duplicate key name functionality on lines 512-520 of transporter.py. We intend to iterate through this workflow many times, so this problem will compound itself further over time.

Describe the solution you'd like

An option to skip index creation if an index of the same name already exists would be one possible solution. There may be better approaches I haven't considered.

Float-Double option ?

While converting from the homeassistant.hsd directly to MariaDB some people have problems with wrong FLOAT.
I don't have this issue but some people do have that,

So a guy stated this and made a work around but its very very long:

"Biggest issue with the method of using sqlite3mysql is that it ends up with a DB where many values are of the wrong type (FLOAT), and when you convert them to the right one (DOUBLE) you lose data"

and some stated this:
"Sqlite3mysql will mess up your db because all of the columns that should be DOUBLE are set to FLOAT. This will mess up data on import and it will affect your future data too."

Link here

Is there a way to implement an option for preventing this?

Regards

Accept `INTEGER PRIMARY KEY DEFAULT 0` construct

Describe the bug

Converting

id INTEGER PRIMARY KEY DEFAULT 1

fails with

MySQL failed creating table const: 1067 (42000): Invalid default value for 'id'

Expected behaviour

To succeed.

Actual result

See above.

System Information

| software               | version                              |
|------------------------|--------------------------------------|
| sqlite3-to-mysql       | 1.4.16                               |
|                        |                                      |
| Operating System       | Windows 10                           |
| Python                 | CPython 3.10.9                       |
| MySQL                  | MySQL client not found on the system |
| SQLite                 | 3.39.4                               |
|                        |                                      |
| click                  | 8.1.3                                |
| mysql-connector-python | 8.0.29                               |
| pytimeparse            | 1.1.8                                |
| simplejson             | 3.18.4                               |
| six                    | 1.16.0                               |
| tabulate               | 0.9.0                                |
| tqdm                   | 4.64.1                               |

Additional context

2023-03-27 21:55:58 ERROR    MySQL failed creating table const: 1067 (42000): Invalid default value for 'id'
Traceback (most recent call last):
  File "C:\Python310\lib\runpy.py", line 196, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "C:\Python310\lib\runpy.py", line 86, in _run_code
    exec(code, run_globals)
  File "C:\Python310\Scripts\sqlite3mysql.exe\__main__.py", line 7, in <module>
  File "C:\Python310\lib\site-packages\click\core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "C:\Python310\lib\site-packages\click\core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "C:\Python310\lib\site-packages\click\core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "C:\Python310\lib\site-packages\click\core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "C:\Python310\lib\site-packages\sqlite3_to_mysql\cli.py", line 204, in cli
    converter.transfer()
  File "C:\Python310\lib\site-packages\sqlite3_to_mysql\transporter.py", line 706, in transfer
    self._create_table(table["name"], transfer_rowid=transfer_rowid)
  File "C:\Python310\lib\site-packages\sqlite3_to_mysql\transporter.py", line 386, in _create_table
    self._mysql_cur.execute(sql)
  File "C:\Python310\lib\site-packages\mysql\connector\cursor.py", line 1209, in execute
    self._prepared = self._connection.cmd_stmt_prepare(operation)
  File "C:\Python310\lib\site-packages\mysql\connector\connection.py", line 1423, in cmd_stmt_prepare
    result = self._handle_binary_ok(packet)
  File "C:\Python310\lib\site-packages\mysql\connector\connection.py", line 1363, in _handle_binary_ok
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1067 (42000): Invalid default value for 'id'

Sqlite to MySQL keeping datetime

Is your feature request related to a problem? Please describe.
When it parses datetime in sqlite, it gives null entries in mysql db

Describe the solution you'd like
No idea

Describe alternatives you've considered
not considered

Additional context

Primary Key is not auto increment

Describe the bug
When i converted the database according to the document,everything seem well. but when i insert a record django exception was showing that id doesn't have a default value.

$ sqlite3mysql --version
| software               | version                                                              |
|------------------------|----------------------------------------------------------------------|
| sqlite3-to-mysql       | 1.3.10                                                               |
|                        |                                                                      |
| Operating System       | Windows 10                                                           |
| Python                 | CPython 3.7.3                                                        |
| MySQL                  | mysql  Ver 8.0.16 for Win64 on x86_64 (MySQL Community Server - GPL) |
| SQLite                 | 3.21.0                                                               |
|                        |                                                                      |
| click                  | 7.1.2                                                                |
| mysql-connector-python | 8.0.21                                                               |
| pytimeparse            | 1.1.8                                                                |
| simplejson             | 3.17.2                                                               |
| six                    | 1.15.0                                                               |
| tabulate               | 0.8.7                                                                |
| tqdm                   | 4.50.0                                                               |

| software               | version                                                              |
|------------------------|----------------------------------------------------------------------|
| sqlite3-to-mysql       | 1.3.10                                                               |
|                        |                                                                      |
| Operating System       | Windows 10                                                           |
| Python                 | CPython 3.7.3                                                        |
| MySQL                  | mysql  Ver 8.0.16 for Win64 on x86_64 (MySQL Community Server - GPL) |
| SQLite                 | 3.21.0                                                               |
|                        |                                                                      |
| click                  | 7.1.2                                                                |
| mysql-connector-python | 8.0.21                                                               |
| pytimeparse            | 1.1.8                                                                |
| simplejson             | 3.17.2                                                               |
| six                    | 1.15.0                                                               |
| tabulate               | 0.8.7                                                                |
| tqdm                   | 4.50.0                                                               |

sqlite to MS SQL

Hi,

Is there any way the script can be adapted for pushing the data into MS SQL, instead of mysql ?

Thanks in advance !

It seems to ignore decimal places

Processing this SQL:

CREATE TABLE IF NOT EXISTS "planidiet_app_valornormalperfil" (
"id" integer NOT NULL,
"quantitat" decimal(9, 5) NOT NULL,
"calories" decimal(9, 5) NOT NULL,
"element_id" integer NOT NULL,
"perfil_id" integer NOT NULL,
FOREIGN KEY("element_id") REFERENCES "planidiet_app_element"("id") DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY("perfil_id") REFERENCES "planidiet_app_perfil"("id") DEFERRABLE INITIALLY DEFERRED,
PRIMARY KEY("id" AUTOINCREMENT)
);

It creates that table in MySQL:

Seems to ignore decimal digits!!

Access Denied for user

Describe the bug
Can't connect to remote mysql host, access denied for user even though I can connect using the mysql CLI tool

System Information

software	version
sqlite3-to-mysql	1.3.12

Operating System	Linux 4.19.128-microsoft-standard
Python	CPython 3.8.5
MySQL	mysql Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))
SQLite	3.31.1

click	7.0
mysql-connector-python	8.0.23
pytimeparse	1.1.8
simplejson	3.16.0
six	1.14.0
tabulate	0.8.9
tqdm	4.60.0

Additional context
I am pretty sure this is due to the script using TLS whilst my host does not use it, and there being no option to turn it off

Problem with datetime

Hey there, thanks for this awesome project, it's the only one I could find that really works well.
Unfortunately I'm experiencing problems with my database. My sqlite values of type datetime have all been converted to 0000-00-00 00:00 (also type datetime in MySQL)

Also autoincrement flag has not been transferred to MySQL (which is easy to fix for my table though)

Can somebody support please? Do you need more information?

Cheers
Hofaa

Support for "string"

Please add support for "string" column type.

I have this on sqlite

CREATE TABLE "posts" (
	"postid"	INTEGER,
	"authorname"	STRING,
);

and it didnt work till i added the "STRING" on the code

        if data_type in {"TEXT", "CLOB","STRING"}:
            return "TEXT"

Sqlite3 has the "string" as a valid column type as you can see here
https://github.com/WinBuilds/sqlite3/blob/1ef6f39d2238c8d75bb1a555a98e583340a33195/test/types.test#L275

Thank you

Using int() method to a string.

100%|████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 30/30 [00:00<00:00, 72.55it/s]
2021-08-01 02:17:29 INFO     Transferring table accounts
invalid literal for int() with base 10: b'1 19:27:7'

This is a text value in SQLite database actually, it seems like to int() a string.

invalid literal

Describe the bug
Duplicated of #18 .

2022-03-12 17:23:10 INFO     Transferring table Goods
invalid literal for int() with base 10: b'01 00:00:00'

Expected behaviour
Well, converted table.

Actual result
Not converted table. :(

System Information

$ sqlite3mysql --version

[root@maplecraft html]# sqlite3mysql --version
| software               | version                                                     |
|------------------------|-------------------------------------------------------------|
| sqlite3-to-mysql       | 1.4.11                                                      |
|                        |                                                             |
| Operating System       | Linux 4.18.0-305.25.1.el8_4.x86_64                          |
| Python                 | CPython 3.6.8                                               |
| MySQL                  | mysql  Ver 8.0.26 for Linux on x86_64 (Source distribution) |
| SQLite                 | 3.26.0                                                      |
|                        |                                                             |
| click                  | 8.0.4                                                       |
| mysql-connector-python | 8.0.28                                                      |
| pytimeparse            | 1.1.8                                                       |
| simplejson             | 3.17.6                                                      |
| six                    | 1.16.0                                                      |
| tabulate               | 0.8.9                                                       |
| tqdm                   | 4.63.0                                                      |

Additional context
The link on SQL Dump: link
It's not the full dump actually, but I think it is the place of the problem.

Insert values only

Is your feature request related to a problem? Please describe.
See #39

Describe the solution you'd like
The user may create the table by its own to the targetted mysql database and run the sqlite3mysql command only to migrate rows. Therefore the column datatypes can be finely tuned.

Describe alternatives you've considered

Instead, we could introduce an option to only dump the sqlite db and let the user transform the result.
Or one could use the project as a python dependency and override SQLite3toMySQL for specific needs.

Additional context

Optional Transfer of implicit column rowid

Hello, it's me again. Sorry to be bothering you so much >.<

Now I'm no expert in python so I probably won't be able to make a PR for this, but in sqlite there appears to be an implicit column called "rowid". I was wondering if adding this as an optional row to transfer would be something you'd be willing to do?

Thanks again for this software this has saved my butt countless times, especially in my transfers as I migrate some data to MySQL :)

Install sqlite3 to mysql in CentOS 7.6.1810x86_64 (Py3.7.9)

I tried to install it in server CentOS 7.6.1810x86_64 (Py3.7.9), but it seems not to be able to install, and I am disconnected from the server

Error determining table encoding

Describe the bug
Hello. When converting the database, an error occurred with the support of national languages (in my case, Russian). Instead of text, I got "??????????"

Expected behaviour
Normal text)

Actual result
I got "??????????"

System Information
Debian 11

$ sqlite3mysql --version

| software               | version                                                                                        |
|------------------------|------------------------------------------------------------------------------------------------|
| sqlite3-to-mysql       | 1.4.8                                                                                          |
|                        |                                                                                                |
| Operating System       | Linux 5.10.0-9-amd64                                                                           |
| Python                 | CPython 3.9.2                                                                                  |
| MySQL                  | mysql  Ver 15.1 Distrib 10.5.12-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper |
| SQLite                 | 3.34.1                                                                                         |
|                        |                                                                                                |
| click                  | 7.1.2                                                                                          |
| mysql-connector-python | 8.0.27                                                                                         |
| pytimeparse            | 1.1.8                                                                                          |
| simplejson             | 3.17.6                                                                                         |
| six                    | 1.16.0                                                                                         |
| tabulate               | 0.8.9                                                                                          |
| tqdm                   | 4.62.3                                                                                         |

This command is only available on v1.3.6 and greater. Otherwise, please provide some basic information about your system (Python version, operating system, etc.).

Additional context
Screenshot in studio )

.

how to ignore index

sqlite3 index field is text ,convert to mysql get an error : MySQL failed adding index to column "sorting" in table ay_content: 1089 (HY000): Incorrect prefix key; the used key part isn't a string, the used length is longer than the key part, or the storage engine doesn't support unique prefix keys

Out Of Memory killer triggered

Describe the bug
Out Of Memory killer triggered when I am trying to transfer a 1.59GB .sqlite file
screenshot of VNC viewer:

Expected behaviour
It works

Actual result
screenshot of VNC viewer:

> ./.local/bin/sqlite3mysql -f ./ledger.sqlite -d ledger -u * -h *.*.*.* --mysql-password ****************
2022-03-22 20:42:10 INFO     Transferring table players
100%|███████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 67/67 [00:00<00:00, 74.73it/s]
2022-03-22 20:42:11 INFO     Adding unique index to column "player_id" in table players
2022-03-22 20:42:11 INFO     Adding unique index to column "player_id" in table players
2022-03-22 20:42:11 INFO     Transferring table ActionIdentifiers
100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 6/6 [00:00<00:00, 47.51it/s]
2022-03-22 20:42:11 INFO     Adding unique index to column "action_identifier" in table ActionIdentifiers
2022-03-22 20:42:11 INFO     Adding unique index to column "action_identifier" in table ActionIdentifiers
2022-03-22 20:42:11 INFO     Transferring table worlds
100%|█████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 3/3 [00:00<00:00, 33.94it/s]
2022-03-22 20:42:11 INFO     Adding unique index to column "identifier" in table worlds
2022-03-22 20:42:11 INFO     Adding unique index to column "identifier" in table worlds
2022-03-22 20:42:12 INFO     Transferring table ObjectIdentifiers
100%|███████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 1314/1314 [00:16<00:00, 80.31it/s]
2022-03-22 20:42:28 INFO     Adding unique index to column "identifier" in table ObjectIdentifiers
2022-03-22 20:42:28 INFO     Adding unique index to column "identifier" in table ObjectIdentifiers
2022-03-22 20:42:28 INFO     Transferring table sources
100%|███████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████████| 45/45 [00:00<00:00, 73.59it/s]
2022-03-22 20:42:29 INFO     Adding unique index to column "name" in table sources
2022-03-22 20:42:29 INFO     Adding unique index to column "name" in table sources
2022-03-22 20:42:30 INFO     Transferring table actions
 19%|████████████████████████▍                                                                                                     | 483474/2494012 [1:41:22<7:33:52, 73.83it/s]
[1]    1017098 killed     ./.local/bin/sqlite3mysql -f ./ledger.sqlite -d ledger -u * -h

System Information
debian 11 which has 8 GB of memory

$ sqlite3mysql --version

| software               | version                                                     |
|------------------------|-------------------------------------------------------------|
| sqlite3-to-mysql       | 1.4.12                                                      |
|                        |                                                             |
| Operating System       | Linux 5.10.0-13-amd64                                       |
| Python                 | CPython 3.9.2                                               |
| MySQL                  | mysql  Ver 8.0.26 for Linux on x86_64 (Source distribution) |
| SQLite                 | 3.34.1                                                      |
|                        |                                                             |
| click                  | 8.0.4                                                       |
| mysql-connector-python | 8.0.28                                                      |
| pytimeparse            | 1.1.8                                                       |
| simplejson             | 3.17.6                                                      |
| six                    | 1.16.0                                                      |
| tabulate               | 0.8.9                                                       |
| tqdm                   | 4.63.0                                                      |

Transfer composite indices

The tool currently lacks the ability to transfer composite indices.

Correct Transfer of Nullable Primary Keys

Hello! I am absolutely loving this package and thank you for the effort and work you've put into it :)

I noticed while trying to transfer one of my databases, that SQLite allows for null values in the primary key column, whereas MySQL doesn't. What I've noticed is that when I go to transfer an SQLite3 database to MySQL using this, it will crash when making the table because of this.

I just wanted to bring this to your attention, in hopes that you'd be willing to fix it :)

OverflowError: Python int too large to convert to C long

Hello,

I am geting this error OverflowError: Python int too large to convert to C long when moving data to mysql. Is there anything I can do?

mysql-string-type argument has no effect

I want to set mysql-string-type argument to MEDIUMTEXT,but has no effect,string values from sqlite always convert to TEXT type

version 1.4.8，1.4.9 both have this issue

just convert to a MySQL-compatible dump to stdout

Is your feature request related to a problem? Please describe.
Contrary to SO scripts, this script tries to do the import. But in some case it's preferible to just get the MySQL-compliant output because MySQL is down / on another machine, ... or additional post-processing is needed, ...

Describe the solution you'd like
Don't make MySQL credentials/connection mandatory and output the convert dump to stdout.

Error convertling sqlite db with custom column type name

Describe the bug
I have a db I'm trying to convert that uses "META" as the column type.

Expected behaviour
Script to complete successfully, assuming string type for the meta column

Actual result
Script exited with error:

2021-04-13 12:51:53 ERROR    MySQL failed creating table ilis: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'META NULL, PRIMARY KEY (`rowid`), CONSTRAINT `ilis_rowid` UNIQUE (`rowid`) ) ENG' at line 1

System Information

$ sqlite3mysql --version

| software               | version                                             |
|------------------------|-----------------------------------------------------|
| sqlite3-to-mysql       | 1.3.12                                              |
|                        |                                                     |
| Operating System       | Darwin 20.3.0                                       |
| Python                 | CPython 3.9.2                                       |
| MySQL                  | mysql  Ver 8.0.23 for osx10.16 on x86_64 (Homebrew) |
| SQLite                 | 3.34.1                                              |
|                        |                                                     |
| click                  | 7.1.2                                               |
| mysql-connector-python | 8.0.23                                              |
| pytimeparse            | 1.1.8                                               |
| simplejson             | 3.17.2                                              |
| six                    | 1.15.0                                              |
| tabulate               | 0.8.9                                               |
| tqdm                   | 4.59.0                                              |

how can i solve it

2019-09-14 10:57:22 ERROR _create_table failed creating table asins: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT64 NOT NULL , nation CHAR(5) NOT NULL , temprank INT(11) NULL , sourc' at line 1 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT64 NOT NULL , nationCHAR(5) NOT NULL , temprankINT(11) NULL , sourc' at line 1

Character set 'utf8' unsupported

Running this under brew on an Intel Mac (OS 12.5.1). Tried with python 3.10 and python 3.7.8 with pyenv. This is driving me crazy.
The database (MariaDB mariadbd (server 10.9.2-MariaDB-1:10.9.2+maria~ubu2204).

Db creation using:
mysql -e 'CREATE SCHEMA IF NOT EXISTS homeassistant DEFAULT CHARACTER SET utf8mb4'

This is absolutely driving me crazy.

sqlite3mysql -f home-assistant_v2.db  -u hassio -d homeassistant -h unraid -E --mysql-password xxx  --debug
2022-08-27 17:10:26 ERROR    Character set 'utf8' unsupported
Traceback (most recent call last):
  File "/usr/local/bin/sqlite3mysql", line 8, in <module>
    sys.exit(cli())
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.9/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/sqlite3_to_mysql/cli.py", line 179, in cli
    converter = SQLite3toMySQL(
  File "/usr/local/lib/python3.9/site-packages/sqlite3_to_mysql/transporter.py", line 155, in __init__
    self._mysql = mysql.connector.connect(
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/pooling.py", line 287, in connect
    return MySQLConnection(*args, **kwargs)
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/connection.py", line 137, in __init__
    self.connect(**kwargs)
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/abstracts.py", line 1095, in connect
    self._open_connection()
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/connection.py", line 544, in _open_connection
    self.set_converter_class(self._converter_class)
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/abstracts.py", line 1271, in set_converter_class
    self.converter = convclass(charset_name, self._use_unicode)
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/conversion.py", line 136, in __init__
    MySQLConverterBase.__init__(self, charset, use_unicode, str_fallback)
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/conversion.py", line 59, in __init__
    self.set_charset(charset)
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/conversion.py", line 73, in set_charset
    self.charset_id = CharacterSet.get_charset_info(self.charset)[0]
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/constants.py", line 775, in get_charset_info
    info = cls.get_default_collation(charset)
  File "/usr/local/lib/python3.9/site-packages/mysql/connector/constants.py", line 746, in get_default_collation
    raise ProgrammingError(f"Character set '{charset}' unsupported")
mysql.connector.errors.ProgrammingError: Character set 'utf8' unsupported

invalid literal for int() with base 10: b'23 2

Describe the bug
SQlite field is DATETIME. Using the script I get error:
invalid literal for int() with base 10: b'23 22:17:07'

In sqlite database the field value is 2021-03-23 22:17:07

Expected behaviour
To import to mysql

Actual result
error

System Information
WSL1 ubuntu

$ sqlite3mysql --version

| software               | version                                                                     |
|------------------------|-----------------------------------------------------------------------------|
| sqlite3-to-mysql       | 1.3.12                                                                      |
|                        |                                                                             |
| Operating System       | Linux 4.4.0-19041-Microsoft                                                 |
| Python                 | CPython 3.8.5                                                               |
| MySQL                  | mysql  Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using  EditLine wrapper |
| SQLite                 | 3.33.0                                                                      |
|                        |                                                                             |
| click                  | 7.1.2                                                                       |
| mysql-connector-python | 8.0.23                                                                      |
| pytimeparse            | 1.1.8                                                                       |
| simplejson             | 3.17.2                                                                      |
| six                    | 1.15.0                                                                      |
| tabulate               | 0.8.9                                                                       |
| tqdm                   | 4.51.0                                                                      |

This command is only available on v1.3.6 and greater. Otherwise, please provide some basic information about your system (Python version, operating system, etc.).

Additional context
Nothing to add

Transfer composite foreign keys

The tool currently lacks the ability to transfer composite foreign keys.

password prompt

Is your feature request related to a problem? Please describe.
passing a plaintext password over command line is insecure

Describe the solution you'd like
password prompt with hidden/asterisks input

Describe alternatives you've considered
keychain

not enough values to unpack

I get an error called not enough values to unpack (expected 2, got 1) While trying to convert,
I can not provide any additional info because thats all it shows,

2021-08-11 10:58:57 INFO     Transferring table global_stats
not enough values to unpack (expected 2, got 1)

migrating a Home Assistant DB to mariadb, 7.000.000 records to transfer...

Hello,

i found this tool on github. i want to migrate home assistant data to mariadb.
i tried with rpi but crashing after a while. I'm now trying with wsl under win10 because it's a I7 cpu, it is running for the moment but ETA is 14hours...
do you think i will go until the end ?

thanks for your help

Large scale data migration

I have a very large sqlite db (150,000 records). I need to migrate to MySQL. When I run the script with chunk size of 100, it migrates only one table and abruptly stops printing the message 'origin'.
Could you please help ?

The Boolean type is not converted to int, but to varchar

Describe the bug
A clear and concise description of what the bug is.

Expected behaviour
What you expected.

Actual result
What happened instead.

System Information

$ sqlite3mysql --version

<paste here>

This command is only available on v1.3.6 and greater. Otherwise, please provide some basic information about your system (Python version, operating system, etc.).

Additional context
Add any other context about the problem here.

sqlite3mysql

sqlite3mysql converts sqlite3 tables to mysql tables by giving "sqlite3mysql -f e:/mydata.db -d mydata -uroot -p". It converts few tables and stopes with an error "Invalid column_type!". But it does not show which table has the problem.

Unknown column 'created' in 'field list'

Describe the bug
I installed mariaDB into HASS OS as an addon in my VM. I changed my database in home assistant to mariadb and rebooted HASS OS. So it is now filling mariadb.
I moved the sqlite database to my main PC. Downloaded your script, and used the following command:
sqlite3mysql -f home-assistant_v2.db -d homeassistant -u homeassistant -p -h 192.168.2.3 -S --debug

Expected behaviour
Import the database into the current database and fill in the missing gaps.

Actual result
Program crashed with the following error:

2022-05-02 10:39:32 ERROR    MySQL transfer failed inserting data into table events: 1054 (42S22): Unknown column 'created' in 'field list'
Traceback (most recent call last):
  File "/home/niels/.local/bin/sqlite3mysql", line 8, in <module>
    sys.exit(cli())
  File "/usr/lib/python3/dist-packages/click/core.py", line 764, in __call__
    return self.main(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/click/core.py", line 717, in main
    rv = self.invoke(ctx)
  File "/usr/lib/python3/dist-packages/click/core.py", line 956, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/lib/python3/dist-packages/click/core.py", line 555, in invoke
    return callback(*args, **kwargs)
  File "/home/niels/.local/lib/python3.8/site-packages/sqlite3_to_mysql/cli.py", line 204, in cli
    converter.transfer()
  File "/home/niels/.local/lib/python3.8/site-packages/sqlite3_to_mysql/transporter.py", line 766, in transfer
    self._transfer_table_data(sql=sql, total_records=total_records)
  File "/home/niels/.local/lib/python3.8/site-packages/sqlite3_to_mysql/transporter.py", line 651, in _transfer_table_data
    self._mysql_cur.executemany(
  File "/home/niels/.local/lib/python3.8/site-packages/mysql/connector/cursor.py", line 1253, in executemany
    self.execute(operation, params)
  File "/home/niels/.local/lib/python3.8/site-packages/mysql/connector/cursor.py", line 1209, in execute
    self._prepared = self._connection.cmd_stmt_prepare(operation)
  File "/home/niels/.local/lib/python3.8/site-packages/mysql/connector/connection.py", line 1420, in cmd_stmt_prepare
    result = self._handle_binary_ok(packet)
  File "/home/niels/.local/lib/python3.8/site-packages/mysql/connector/connection.py", line 1360, in _handle_binary_ok
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'created' in 'field list'

System Information

| software               | version                                                                                   |
|------------------------|-------------------------------------------------------------------------------------------|
| sqlite3-to-mysql       | 1.4.15                                                                                    |
|                        |                                                                                           |
| Operating System       | Linux 5.10.102.1-microsoft-standard-WSL2                                                  |
| Python                 | CPython 3.8.10                                                                            |
| MySQL                  | mysql  Ver 15.1 Distrib 10.3.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 |
| SQLite                 | 3.31.1                                                                                    |
|                        |                                                                                           |
| click                  | 7.0                                                                                       |
| mysql-connector-python | 8.0.28                                                                                    |
| pytimeparse            | 1.1.8                                                                                     |
| simplejson             | 3.16.0                                                                                    |
| six                    | 1.14.0                                                                                    |
| tabulate               | 0.8.9                                                                                     |
| tqdm                   | 4.64.0                                                                                    |

Conversion failure with table name over 64 characters

Describe the bug
MariaDB/MySQL have a table name length limit of 64 characters. Sqlite doesn't. When importing an sqlite database with table names longer than 64 characters, there is an error and the conversion fails.

Expected behaviour
Conversion works

Actual result
MySQL failed creating table myverylongtablename012345678901234567890123456789012345678901234567890: 1103 (42000): Incorrect table name 'myverylongtablename012345678901234567890123456789012345678901234567890'

System Information

$ sqlite3mysql --version

| software               | version                                                                        |
|------------------------|--------------------------------------------------------------------------------|
| sqlite3-to-mysql       | 1.4.5                                                                          |
|                        |                                                                                |
| Operating System       | Linux 5.14.2                                                                   |
| Python                 | CPython 3.9.6                                                                  |
| MySQL                  | mysql  Ver 15.1 Distrib 10.5.10-MariaDB, for Linux (x86_64) using readline 8.1 |
| SQLite                 | 3.35.5                                                                         |
|                        |                                                                                |
| click                  | 8.0.1                                                                          |
| mysql-connector-python | 8.0.26                                                                         |
| pytimeparse            | 1.1.8                                                                          |
| simplejson             | 3.17.3                                                                         |
| six                    | 1.16.0                                                                         |
| tabulate               | 0.8.9                                                                          |
| tqdm                   | 4.62.0                                                                         |

Additional context
Documentation links:
https://mariadb.com/kb/en/identifier-names/
https://dev.mysql.com/doc/refman/8.0/en/identifier-length.html
(I couldn't find any documentation verifying limits (or that there are none) for sqlite, but I have existing files with table names up to 70 characters.)

Don't fail on empty column "type"

Describe the bug
In SQLite, the column "affinity" can be omitted. Eg (here the size)

sqlite> pragma table_info(x);
[...]
23|foo|INT|0||0
24|size||0||0
25|bar|TEXT|0||0
[...]

or
CREATE TABLE x (foo int, size, bar text

Expected behaviour
Either guess the data-type based on the following insert, either assumed INT, either make it configurable.
In any case, the output should be more explicit, like

Unspecified column_type for table "x". Guessed: "INT"
Unspecified column_type for table "x". Using user-choice: "VARCHAR"

Actual result
Invalid column_type

System Information

$ sqlite3mysql --version

1.4.16

techouse / sqlite3-to-mysql Goto Github PK

sqlite3-to-mysql's Introduction

SQLite3 to MySQL

A simple Python tool to transfer data from SQLite 3 to MySQL.

How to run

Usage

Docker

Homebrew

sqlite3-to-mysql's People

Contributors

Stargazers

Watchers

Forkers

sqlite3-to-mysql's Issues

Recommend Projects

Recommend Topics

Recommend Org