JBPressac
commented
4 years ago Hello, I have got the same error trying to convert a table from a MySQL database. The table has the following schema:
CREATE TABLE IF NOT EXISTS ville (
id int(11) NOT NULL AUTO_INCREMENT,
nom varchar(100) NOT NULL,
wikidata varchar(20) NOT NULL,
idref varchar(10) NOT NULL,
pays_id int(11),
latitude decimal(13,10),
longitude decimal(13,10),
PRIMARY KEY (id),
KEY ville_10741763 (pays_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=720 ;
However, after running:
pipenv run db-to-sqlite "mysql://user@localhost/database" database.db --table=ville -p
The following error is displayed:
qlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.
and database.db contains (among other things, but without any data): CREATE TABLE [ville] ( [id] INTEGER PRIMARY KEY, [nom] TEXT, [wikidata] TEXT, [idref] TEXT, [pays_id] TEXT, [latitude] TEXT, [longitude] TEXT )
I am using Python 3.8.0 and the followings libraries:
aiofiles 0.4.0
Click 7.0
click-default-group 1.2.2
csvs-to-sqlite 1.0
datasette 0.33
datasette-cluster-map 0.8
datasette-vega 0.6.2
dateparser 0.7.2
db-to-sqlite 1.0.2
h11 0.9.0
httptools 0.0.13
hupper 1.9.1
Jinja2 2.10.3
MarkupSafe 1.1.1
mysqlclient 1.4.6
numpy 1.18.1
pandas 0.25.3
Pint 0.10.1
pip 19.3.1
pluggy 0.13.1
py-lru-cache 0.1.4
pylzma 0.5.0
python-dateutil 2.8.1
pytz 2019.3
regex 2020.1.8
setuptools 42.0.1
six 1.14.0
SQLAlchemy 1.3.12
sqlite-utils 2.0.1
tabulate 0.8.6
tzlocal 2.0.0
uvicorn 0.11.2
uvloop 0.14.0
websockets 8.1
wheel 0.33.6
Thanks,
simonw
Hey !
First of all, thanks for this script, it saved to me a lot of time ! 👍
I got a problem when I try to use it with a MySQL database that I need to export every week. So I am trying to automatise this.
PS C:\Users\user\Desktop\db-to-sqlite-master\db_to_sqlite> db-to-sqlite "mysql://root:@localhost:3306/gtrx" gtrx.db --output=query_results --sql="select * from animal_2 where IdANIMAL='US000135746776'" --pk=id Traceback (most recent call last): File "c:\users\user\appdata\local\programs\python\python37\lib\runpy.py", line 193, in _run_module_as_main "__main__", mod_spec) File "c:\users\user\appdata\local\programs\python\python37\lib\runpy.py", line 85, in _run_code exec(code, run_globals) File "C:\Users\user\AppData\Local\Programs\Python\Python37\Scripts\db-to-sqlite.exe\__main__.py", line 9, in <module> File "c:\users\user\appdata\local\programs\python\python37\lib\site-packages\click\core.py", line 764, in __call__ return self.main(*args, **kwargs) File "c:\users\user\appdata\local\programs\python\python37\lib\site-packages\click\core.py", line 717, in main rv = self.invoke(ctx) File "c:\users\user\appdata\local\programs\python\python37\lib\site-packages\click\core.py", line 956, in invoke return ctx.invoke(self.callback, **ctx.params) File "c:\users\user\appdata\local\programs\python\python37\lib\site-packages\click\core.py", line 555, in invoke return callback(*args, **kwargs) File "c:\users\user\appdata\local\programs\python\python37\lib\site-packages\db_to_sqlite\cli.py", line 148, in cli db[output].insert_all(rows, pk=pk) File "c:\users\user\appdata\local\programs\python\python37\lib\site-packages\sqlite_utils\db.py", line 1024, in insert_all result = self.db.conn.execute(sql, values) sqlite3.InterfaceError: Error binding parameter 18 - probably unsupported type.It's working with some lines before this message because they got "NULL" data. But once I reach the line with ID 'US000135746776' this error is throw.
This is the table structure :
CREATE TABLE IF NOT EXISTS `animal` ( `IdANIMAL` varchar(16) NOT NULL, `LAST_DATE` text, `IdCHEPTEL` int(11) DEFAULT NULL, `NOBOVIC` text, `NOBOVIL` text, `NOBOET` text, `M` text, `AGMMP` text, `AGMPP` text, `AGMPM` text, `AGMMM` text, `AGPMP` text, `AGPPP` text, `AGPPM` text, `AGPMM` text, `GPP` text, `GMP` text, `GMM` text, `GPM` text, `P` text, `INDEX_VVEL_ORI` char(1) DEFAULT NULL, `INDEX_EC_ORI` char(1) DEFAULT NULL, `INDEX_LT_ORI` char(1) DEFAULT NULL, `INDEX_FERG_CD` tinyint(2) unsigned DEFAULT NULL, `INDEX_IA_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_REPRO_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_INEL_CD` tinyint(2) unsigned DEFAULT NULL, `INDEX_PC_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_FERV_ORI` char(1) DEFAULT NULL, `INDEX_MACL_CD` tinyint(2) unsigned DEFAULT NULL, `INDEX_LP_ORI` char(1) DEFAULT NULL, `INDEX_ME_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_PI_ORI` char(1) DEFAULT NULL, `INDEX_FNAI_ORI` char(1) DEFAULT NULL, `INDEX_TB_ORI` char(1) DEFAULT NULL, `INDEX_MU_VAL3` smallint(6) DEFAULT NULL, `INDEX_FVEL_CD` tinyint(2) unsigned DEFAULT NULL, `INDEX_LAIT_ORI` char(1) DEFAULT NULL, `INDEX_MA_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_VNAI_CD` tinyint(2) unsigned DEFAULT NULL, `INDEX_CC_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_FVEL_ORI` char(1) DEFAULT NULL, `INDEX_INEL_VAL3` smallint(6) DEFAULT NULL, `INDEX_LAIT_VAL4` smallint(6) DEFAULT NULL, `INDEX_TP_ORI` char(1) DEFAULT NULL, `INDEX_MO_CD` tinyint(2) unsigned DEFAULT NULL, `INDEX_MO_REF` varchar(12) DEFAULT NULL, `INDEX_AS_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_TE_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_MACL_ORI` char(1) DEFAULT NULL, `INDEX_HS_ORI` char(1) DEFAULT NULL, `INDEX_VVEL_VALSM` smallint(6) DEFAULT NULL, `INDEX_TB_VAL31` decimal(3,1) DEFAULT NULL, `INDEX_FERV_CD` tinyint(2) unsigned DEFAULT NULL, `INDEX_INEL_ORI` char(1) DEFAULT NULL, `INDEX_IVIA1_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_IVIA1_CD` tinyint(2) unsigned DEFAULT NULL, `INDEX_STMA_ORI` char(1) DEFAULT NULL, `INDEX_HS_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_LGF_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_TE_ORI` char(1) DEFAULT NULL, `INDEX_MO_NF` int(11) DEFAULT NULL, `INDEX_IB_ORI` char(1) DEFAULT NULL, `INDEX_PS_ORI` char(1) DEFAULT NULL, `INDEX_FERG_ORI` char(1) DEFAULT NULL, `INDEX_LGF_CD` tinyint(2) unsigned DEFAULT NULL, `INDEX_LAIT_NF` mediumint(9) DEFAULT NULL, `INDEX_AJ_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_ISU_REF` varchar(12) DEFAULT NULL, `INDEX_MP_ORI` char(1) DEFAULT NULL, `INDEX_EQ_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_AH_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_LO_ORI` char(1) DEFAULT NULL, `INDEX_CELL_ORI` char(1) DEFAULT NULL, `INDEX_AS_ORI` char(1) DEFAULT NULL, `INDEX_LT_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_CC_ORI` char(1) DEFAULT NULL, `INDEX_REPRO_ORI` char(1) DEFAULT NULL, `INDEX_ME_ORI` char(1) DEFAULT NULL, `INDEX_IS_ORI` char(1) DEFAULT NULL, `INDEX_FERV_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_AA_ORI` char(1) DEFAULT NULL, `INDEX_VNAI_VALSM` smallint(6) DEFAULT NULL, `INDEX_MG_VAL3` smallint(6) DEFAULT NULL, `INDEX_AH_ORI` char(1) DEFAULT NULL, `INDEX_LO_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_EA_ORI` char(1) DEFAULT NULL, `INDEX_LP_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_FNAI_CD` tinyint(2) unsigned DEFAULT NULL, `INDEX_MACL_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_IS_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_ISU_VAL3` smallint(3) unsigned DEFAULT NULL, `INDEX_AA_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_IA_ORI` char(1) DEFAULT NULL, `INDEX_STMA_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_MA_ORI` char(1) DEFAULT NULL, `INDEX_TR_ORI` char(1) DEFAULT NULL, `INDEX_PS_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_CELL_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_MO_ORI` char(1) DEFAULT NULL, `INDEX_LAIT_REF` varchar(12) DEFAULT NULL, `INDEX_FVEL_VALSM` smallint(6) DEFAULT NULL, `INDEX_EC_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_VVEL_CD` tinyint(2) unsigned DEFAULT NULL, `INDEX_MR_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_EA_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_MR_ORI` char(1) DEFAULT NULL, `INDEX_PC_ORI` char(1) DEFAULT NULL, `INDEX_CELL_CD` tinyint(2) unsigned DEFAULT NULL, `INDEX_FERG_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_PJ_ORI` char(1) DEFAULT NULL, `INDEX_ISU_ORI` char(1) DEFAULT NULL, `INDEX_MP_VAL3` smallint(6) DEFAULT NULL, `INDEX_PJ_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_MO_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_TP_VAL31` decimal(3,1) DEFAULT NULL, `INDEX_IVIA1_ORI` char(1) DEFAULT NULL, `INDEX_MG_ORI` char(1) DEFAULT NULL, `INDEX_LGF_ORI` char(1) DEFAULT NULL, `INDEX_TR_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_PI_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_VNAI_ORI` char(1) DEFAULT NULL, `INDEX_IB_VAL31` decimal(2,1) DEFAULT NULL, `INDEX_AJ_ORI` char(1) DEFAULT NULL, `INDEX_MU_ORI` char(1) DEFAULT NULL, `INDEX_FNAI_VALSM` smallint(6) DEFAULT NULL, `ORIGINE` varchar(2) DEFAULT NULL, `REF` varchar(50) DEFAULT NULL, `EDITION` text, `AANAIS` varchar(10) DEFAULT NULL, `COPAMA` decimal(2,1) DEFAULT NULL, `COPAFN` decimal(2,1) DEFAULT NULL, `NBGECO` decimal(3,1) DEFAULT NULL, `IdPAYS` int(11) DEFAULT NULL, `COPAIP` text, `NUNATI` text, PRIMARY KEY (`IdANIMAL`), KEY `IdPAYS` (`IdPAYS`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;And this is the line where the script is blocking
INSERT INTO `animal_2` (`IdANIMAL`, `LAST_DATE`, `IdCHEPTEL`, `NOBOVIC`, `NOBOVIL`, `NOBOET`, `M`, `AGMMP`, `AGMPP`, `AGMPM`, `AGMMM`, `AGPMP`, `AGPPP`, `AGPPM`, `AGPMM`, `GPP`, `GMP`, `GMM`, `GPM`, `NOM_GPM`, `P`, `NOM_P`, `INDEX_VVEL_ORI`, `INDEX_EC_ORI`, `INDEX_LT_ORI`, `INDEX_FERG_CD`, `INDEX_IA_VAL31`, `INDEX_REPRO_VAL31`, `INDEX_INEL_CD`, `INDEX_PC_VAL31`, `INDEX_FERV_ORI`, `INDEX_MACL_CD`, `INDEX_LP_ORI`, `INDEX_ME_VAL31`, `INDEX_PI_ORI`, `INDEX_FNAI_ORI`, `INDEX_TB_ORI`, `INDEX_MU_VAL3`, `INDEX_FVEL_CD`, `INDEX_LAIT_ORI`, `INDEX_MA_VAL31`, `INDEX_VNAI_CD`, `INDEX_CC_VAL31`, `INDEX_FVEL_ORI`, `INDEX_INEL_VAL3`, `INDEX_LAIT_VAL4`, `INDEX_TP_ORI`, `INDEX_MO_CD`, `INDEX_MO_REF`, `INDEX_AS_VAL31`, `INDEX_TE_VAL31`, `INDEX_MACL_ORI`, `INDEX_HS_ORI`, `INDEX_VVEL_VALSM`, `INDEX_TB_VAL31`, `INDEX_FERV_CD`, `INDEX_INEL_ORI`, `INDEX_IVIA1_VAL31`, `INDEX_IVIA1_CD`, `INDEX_STMA_ORI`, `INDEX_HS_VAL31`, `INDEX_LGF_VAL31`, `INDEX_TE_ORI`, `INDEX_MO_NF`, `INDEX_IB_ORI`, `INDEX_PS_ORI`, `INDEX_FERG_ORI`, `INDEX_LGF_CD`, `INDEX_LAIT_NF`, `INDEX_AJ_VAL31`, `INDEX_ISU_REF`, `INDEX_MP_ORI`, `INDEX_EQ_VAL31`, `INDEX_AH_VAL31`, `INDEX_LO_ORI`, `INDEX_CELL_ORI`, `INDEX_AS_ORI`, `INDEX_LT_VAL31`, `INDEX_CC_ORI`, `INDEX_REPRO_ORI`, `INDEX_ME_ORI`, `INDEX_IS_ORI`, `INDEX_FERV_VAL31`, `INDEX_AA_ORI`, `INDEX_VNAI_VALSM`, `INDEX_MG_VAL3`, `INDEX_AH_ORI`, `INDEX_LO_VAL31`, `INDEX_EA_ORI`, `INDEX_LP_VAL31`, `INDEX_FNAI_CD`, `INDEX_MACL_VAL31`, `INDEX_IS_VAL31`, `INDEX_ISU_VAL3`, `INDEX_AA_VAL31`, `INDEX_IA_ORI`, `INDEX_STMA_VAL31`, `INDEX_MA_ORI`, `INDEX_TR_ORI`, `INDEX_PS_VAL31`, `INDEX_CELL_VAL31`, `INDEX_MO_ORI`, `INDEX_LAIT_REF`, `INDEX_FVEL_VALSM`, `INDEX_EC_VAL31`, `INDEX_VVEL_CD`, `INDEX_MR_VAL31`, `INDEX_EA_VAL31`, `INDEX_MR_ORI`, `INDEX_PC_ORI`, `INDEX_CELL_CD`, `INDEX_FERG_VAL31`, `INDEX_PJ_ORI`, `INDEX_ISU_ORI`, `INDEX_MP_VAL3`, `INDEX_PJ_VAL31`, `INDEX_MO_VAL31`, `INDEX_TP_VAL31`, `INDEX_IVIA1_ORI`, `INDEX_MG_ORI`, `INDEX_LGF_ORI`, `INDEX_TR_VAL31`, `INDEX_PI_VAL31`, `INDEX_VNAI_ORI`, `INDEX_IB_VAL31`, `INDEX_AJ_ORI`, `INDEX_MU_ORI`, `INDEX_FNAI_VALSM`, `ORIGINE`, `REF`, `EDITION`, `NUIETA`, `AANAIS`, `COPAMA`, `COPAFN`, `NBGECO`, `IdPAYS`, `COPAIP`, `NUNATI`, `POINTS_FORTS`, `POINTS_FAIBLES`) VALUES ('US000135746776', NULL, NULL, 'MAN-O-MAN', 'LONG-LANGS OMAN OMAN-ET', NULL, 'FRU130677626', 'FRUS14889171', 'FRUS13066986', 'FRUS13751565', 'FRUS14962609', 'FRUS02071864', 'FRUS01986164', 'FRUS01912270', 'FRUS02103297', 'FRUS02183007', 'FRUS15459080', 'FRUS15930909', 'FRUS02265005', 'AARON', 'FRU122358313', 'O-MAN JUST', NULL, NULL, NULL, 95, 1.2, 1.2, 95, 0.2, NULL, 95, NULL, -0.4, NULL, NULL, NULL, 42, 95, NULL, 0.1, 95, -0.1, NULL, 27, 175, NULL, 95, NULL, 0.5, -0.1, NULL, NULL, 93, 1.8, 95, NULL, 0.9, 95, NULL, 0.8, -0.8, NULL, 1482, NULL, NULL, NULL, 95, 1798, -0.2, NULL, NULL, -0.1, 0.9, NULL, NULL, NULL, 0.0, NULL, NULL, NULL, NULL, 1.1, NULL, 93, 21, NULL, -0.2, NULL, -0.1, 95, -0.8, -0.4, 121, -1.5, NULL, -0.8, NULL, NULL, 1.8, -0.7, NULL, NULL, 93, -0.5, 99, -0.8, 0.0, NULL, NULL, 95, 0.2, NULL, NULL, 21, 0.2, -0.1, 1.8, NULL, NULL, NULL, 0.4, -0.2, NULL, -0.9, NULL, NULL, 92, 'EF', NULL, 'a:31:{i:0;s:11:"FRA 08/2019";i:1;s:11:"FRA 04/2019";i:2;s:11:"FRA 12/2018";i:3;s:11:"FRA 12/2018";i:4;s:11:"FRA 08/2018";i:5;s:11:"FRA 04/2018";i:6;s:11:"FRA 12/2017";i:7;s:11:"FRA 08/2017";i:8;s:11:"FRA 04/2017";i:9;s:11:"FRA 12/2016";i:10;s:11:"FRA 08/2016";i:11;s:11:"FRA 04/2016";i:12;s:11:"FRA 12/2015";i:13;s:11:"FRA 08/2015";i:14;s:11:"FRA 04/2015";i:15;s:11:"FRA 12/2014";i:16;s:11:"FRA 08/2014";i:17;s:11:"FRA 04/2014";i:18;s:11:"FRA 10/2013";i:19;s:11:"FRA 06/2013";i:20;s:11:"INT 04/2013";i:21;s:11:"INT 12/2012";i:22;s:11:"INT 08/2012";i:23;s:11:"INT 04/2012";i:24;s:11:"INT 12/2011";i:25;s:11:"INT 08/2011";i:26;s:11:"INT 04/2011";i:27;s:11:"INT 12/2010";i:28;s:11:"INT 08/2010";i:29;s:11:"INT 04/2010";i:30;s:11:"INT 01/2010";}', 75694, '2004-04-30', NULL, NULL, NULL, NULL, 'US', '000135746776', 'AJ,HS,TP', 'AA,IA,IS,LP,MO,MR,PI,STMA');Thanks for your help ! :)