6 ms·

Show HN: Mycli – A CLI for MySQL with auto-completion and syntax highlighting

106 points by amjith 11y ago

amjith 11y ago
I'm the author of this project. I'll be happy to answer any questions. Brief Intro: This is a command line tool that is intended as a replacement for MySQL client. It works with MySQL, MariaDB and Percona. It is written in Python and it works in Python 2.6 to Python 3.4. This is a counter part to http://pgcli.com http://pgcli.com. MyCli was funded through Kickstarter (https://www.kickstarter.com/projects/1839007321/mysql-cli https://www.kickstarter.com/projects/1839007321/mysql-cli)
- trashcan 11y ago
  I tried installing it via pip on CentOS 6.6, and I get: Traceback (most recent call last): File "/usr/bin/mycli", line 5, in <module> from pkg_resources import load_entry_point File "/usr/lib/python2.6/site-packages/pkg_resources.py", line 2655, in <module> working_set.require(__requires__) File "/usr/lib/python2.6/site-packages/pkg_resources.py", line 648, in require needed = self.resolve(parse_requirements(requirements)) File "/usr/lib/python2.6/site-packages/pkg_resources.py", line 546, in resolve raise DistributionNotFound(req) pkg_resources.DistributionNotFound: six>=1.8 Am I missing something obvious?
  - amjith 11y ago
    I've heard it from someone else as well. I couldn't reproduce it myself. The suggested solution was to uninstall mycli and reinstall directly from github. pip uninstall mycli six; pip install https://github.com/dbcli/mycli/archive/master.zip https://github.com/dbcli/mycli/archive/master.zip
    - autotune 11y ago
      Yep, getting the same issue: [foo@chef-solo ~]$ mycli Traceback (most recent call last): File "/usr/bin/mycli", line 5, in <module> from pkg_resources import load_entry_point File "/usr/lib/python2.6/site-packages/pkg_resources.py", line 2655, in <module> working_set.require(__requires__) File "/usr/lib/python2.6/site-packages/pkg_resources.py", line 648, in require needed = self.resolve(parse_requirements(requirements)) File "/usr/lib/python2.6/site-packages/pkg_resources.py", line 546, in resolve raise DistributionNotFound(req) pkg_resources.DistributionNotFound: six>=1.8 Running sudo python install did the trick. I'm not sure I can really recommending anyone check this out without a working install from a package manager though.
- ecesena 11y ago
  Did you try it with memsql? Just curious... I don't see any reason why it shouldn't work.
  - amjith 11y ago
    I don't fully understand your question. If you're asking whether this client will work with memsql, then the answer is No. Unless, memsql uses mysql protocol to talk to clients. Mycli uses MySQL adapter called PyMySQL to connect to MySQL instances. I don't think it'll work with memsql. If you're talking about writing a similar client for memsql, then yes it is possible to do that as long as there is a Python adapter for it.
    - sanxiyn 11y ago
      MemSQL does use MySQL protocol to talk to clients.
      - amjith 11y ago
        You're right. I didn't know that. http://docs.memsql.com/latest/faq/#is-memsql-a-storage-engine-for-mysql http://docs.memsql.com/latest/faq/#is-memsql-a-storage-engin... I haven't personally tried it with memsql.
- cauterize 11y ago
  Is there a way to have it bundled into a single file which I can scp to remote systems and need not worry about dependencies?
  - simeonf 11y ago
    You could use pex which bundles up python requirements into an executable zip. I ran $ virtualenv PEX (PEX) $ pip install pex (PEX) $ pex mycli -e mycli.main:cli -o mycli.pex (PEX) $ deactivate $ chmod +x mycli.pex $ ./mycli.pex Version: 1.0.1 Chat: https://gitter.im/dbcli/mycli Mail: https://groups.google.com/forum/#!forum/mycli-users Home: http://mycli.net Thanks to the contributor - jweiland.net mysql user@localhost:(none)> You could scp the .pex to another host that also has python installed and it should run. You can also unzip it to inspect its contents...
    - cauterize 11y ago
      Very cool! Thanks!
talleyrand 11y ago
Looks great, thank you!
jafingi 11y ago
Looks really great! I think you should highlight better that it support fuzzy search, as I had to dig deep into Docs->Auto completion to find it (was about to suggest it to you). Good luck with it!
leni536 11y ago
This is really cool. I wanted to write an interactive input module for sqlite and I really struggled with GNU readline. It looks like I should try it again using python-prompt-toolkit. My use case would be something different than writing SQL statements, my module would let you create interactive, autocompleting input forms. I usually struggle inputting student exam scores into large spreadsheets and I want to replace that with sqlite. And thanks for this one: https://github.com/jonathanslenders/python-prompt-toolkit/tree/master/examples/tutorial https://github.com/jonathanslenders/python-prompt-toolkit/tr...
- amjith 11y ago
  Yes! Python-prompt-toolkit has made it tremendously easy to write programs like mycli and pgcli. The author has done an excellent job of keeping the APIs simple and intuitive. It also helps that he's a good OSS maintainer (responsive and courteous). :)
  - machinshin_ 11y ago
    humble and modest too ;)
    - tcfunk 11y ago
      I think they were talking about the author of Python-prompt-toolkit, though I read it that way too at first.
Grue3 11y ago
I gave it a spin. Seems pretty useful. Is there a way to view the last result again without re-running the query? Also the keybindings are missing from the docs. For example on http://mycli.net/multi-line http://mycli.net/multi-line it says "You can enable multi-line mode by pressing key" but doesn't say which key to press. And on http://mycli.net/history http://mycli.net/history it says "So press and then start typing your search term to see the queries", again doesn't say what to press.
- amjith 11y ago
  Thank you for reporting the missing keys. I had them as <C-r> and <F3> but it was misinterpreted as html tags. It is fixed now. I'm not sure what you mean by view the results without re-running the query. Does scrolling not work? or do you mean if you launch a new session and want to see the results? If you want to keep the results you can use audit logging feature (http://mycli.net/logging http://mycli.net/logging) which records every query and it's output into a file.
  - Grue3 11y ago
    If the resulting table is large and opens up a less-like mode where I can scroll and pan around, but if I quit this mode, the terminal is cleared and there seems to be no way to take a second look at the data.
    - amjith 11y ago
      Can you tell me which OS you're using? In Linux and OS X the less command should keep the output in the screen after you press 'q'.
      - chx 11y ago
        I am on Linux too and it doesn't. Not just this tool: in general.
        amjith 11y ago
jgalt212 11y ago
Does is protect against auto-completing dangerous commands like 'DROP TABLE;'?
- amjith 11y ago
  It still does auto-complete the DROP TABLE and DROP DATABASE, but it will ask for confirmation before running it.
jupiter909 11y ago
Looks nice. Will give it a proper test drive soon. Am currently using: http://ewaters.github.io/altsql-shell/ http://ewaters.github.io/altsql-shell/ Software that few people know about.Worth others trying out too. The data world really does need these more sane solutions.
tcfunk 11y ago
Can I use this for mysql over ssh?
- rajamohata 11y ago
  Any update on this?
PauloManrique 11y ago
Works perfect on Windows 10. Great tool!

106 points by amjith 11y ago

I'm the author of this project. I'll be happy to answer any questions. Brief Intro: This is a command line tool that is intended as a replacement for MySQL client. It works with MySQL, MariaDB and Percona. It is written in Python and it works in Python 2.6 to Python 3.4. This is a counter part to http://pgcli.com http://pgcli.com. MyCli was funded through Kickstarter (https://www.kickstarter.com/projects/1839007321/mysql-cli https://www.kickstarter.com/projects/1839007321/mysql-cli)

trashcan 11y ago
I tried installing it via pip on CentOS 6.6, and I get: Traceback (most recent call last): File "/usr/bin/mycli", line 5, in <module> from pkg_resources import load_entry_point File "/usr/lib/python2.6/site-packages/pkg_resources.py", line 2655, in <module> working_set.require(__requires__) File "/usr/lib/python2.6/site-packages/pkg_resources.py", line 648, in require needed = self.resolve(parse_requirements(requirements)) File "/usr/lib/python2.6/site-packages/pkg_resources.py", line 546, in resolve raise DistributionNotFound(req) pkg_resources.DistributionNotFound: six>=1.8 Am I missing something obvious?
- amjith 11y ago
  I've heard it from someone else as well. I couldn't reproduce it myself. The suggested solution was to uninstall mycli and reinstall directly from github. pip uninstall mycli six; pip install https://github.com/dbcli/mycli/archive/master.zip https://github.com/dbcli/mycli/archive/master.zip
  - autotune 11y ago
    Yep, getting the same issue: [foo@chef-solo ~]$ mycli Traceback (most recent call last): File "/usr/bin/mycli", line 5, in <module> from pkg_resources import load_entry_point File "/usr/lib/python2.6/site-packages/pkg_resources.py", line 2655, in <module> working_set.require(__requires__) File "/usr/lib/python2.6/site-packages/pkg_resources.py", line 648, in require needed = self.resolve(parse_requirements(requirements)) File "/usr/lib/python2.6/site-packages/pkg_resources.py", line 546, in resolve raise DistributionNotFound(req) pkg_resources.DistributionNotFound: six>=1.8 Running sudo python install did the trick. I'm not sure I can really recommending anyone check this out without a working install from a package manager though.
ecesena 11y ago
Did you try it with memsql? Just curious... I don't see any reason why it shouldn't work.
- amjith 11y ago
  I don't fully understand your question. If you're asking whether this client will work with memsql, then the answer is No. Unless, memsql uses mysql protocol to talk to clients. Mycli uses MySQL adapter called PyMySQL to connect to MySQL instances. I don't think it'll work with memsql. If you're talking about writing a similar client for memsql, then yes it is possible to do that as long as there is a Python adapter for it.
  - sanxiyn 11y ago
    MemSQL does use MySQL protocol to talk to clients.
    - amjith 11y ago
      You're right. I didn't know that. http://docs.memsql.com/latest/faq/#is-memsql-a-storage-engine-for-mysql http://docs.memsql.com/latest/faq/#is-memsql-a-storage-engin... I haven't personally tried it with memsql.
cauterize 11y ago
Is there a way to have it bundled into a single file which I can scp to remote systems and need not worry about dependencies?
- simeonf 11y ago
  You could use pex which bundles up python requirements into an executable zip. I ran $ virtualenv PEX (PEX) $ pip install pex (PEX) $ pex mycli -e mycli.main:cli -o mycli.pex (PEX) $ deactivate $ chmod +x mycli.pex $ ./mycli.pex Version: 1.0.1 Chat: https://gitter.im/dbcli/mycli Mail: https://groups.google.com/forum/#!forum/mycli-users Home: http://mycli.net Thanks to the contributor - jweiland.net mysql user@localhost:(none)> You could scp the .pex to another host that also has python installed and it should run. You can also unzip it to inspect its contents...
  - cauterize 11y ago
    Very cool! Thanks!

talleyrand 11y ago

Looks great, thank you!

jafingi 11y ago

Looks really great! I think you should highlight better that it support fuzzy search, as I had to dig deep into Docs->Auto completion to find it (was about to suggest it to you). Good luck with it!

leni536 11y ago

This is really cool. I wanted to write an interactive input module for sqlite and I really struggled with GNU readline. It looks like I should try it again using python-prompt-toolkit. My use case would be something different than writing SQL statements, my module would let you create interactive, autocompleting input forms. I usually struggle inputting student exam scores into large spreadsheets and I want to replace that with sqlite. And thanks for this one: https://github.com/jonathanslenders/python-prompt-toolkit/tree/master/examples/tutorial https://github.com/jonathanslenders/python-prompt-toolkit/tr...

amjith 11y ago
Yes! Python-prompt-toolkit has made it tremendously easy to write programs like mycli and pgcli. The author has done an excellent job of keeping the APIs simple and intuitive. It also helps that he's a good OSS maintainer (responsive and courteous). :)
- machinshin_ 11y ago
  humble and modest too ;)
  - tcfunk 11y ago
    I think they were talking about the author of Python-prompt-toolkit, though I read it that way too at first.

Grue3 11y ago

I gave it a spin. Seems pretty useful. Is there a way to view the last result again without re-running the query? Also the keybindings are missing from the docs. For example on http://mycli.net/multi-line http://mycli.net/multi-line it says "You can enable multi-line mode by pressing key" but doesn't say which key to press. And on http://mycli.net/history http://mycli.net/history it says "So press and then start typing your search term to see the queries", again doesn't say what to press.

amjith 11y ago
Thank you for reporting the missing keys. I had them as <C-r> and <F3> but it was misinterpreted as html tags. It is fixed now. I'm not sure what you mean by view the results without re-running the query. Does scrolling not work? or do you mean if you launch a new session and want to see the results? If you want to keep the results you can use audit logging feature (http://mycli.net/logging http://mycli.net/logging) which records every query and it's output into a file.
- Grue3 11y ago
  If the resulting table is large and opens up a less-like mode where I can scroll and pan around, but if I quit this mode, the terminal is cleared and there seems to be no way to take a second look at the data.
  - amjith 11y ago
    Can you tell me which OS you're using? In Linux and OS X the less command should keep the output in the screen after you press 'q'.
    - chx 11y ago
      I am on Linux too and it doesn't. Not just this tool: in general.
      - amjith 11y ago

jgalt212 11y ago

Does is protect against auto-completing dangerous commands like 'DROP TABLE;'?

amjith 11y ago
It still does auto-complete the DROP TABLE and DROP DATABASE, but it will ask for confirmation before running it.

jupiter909 11y ago

Looks nice. Will give it a proper test drive soon. Am currently using: http://ewaters.github.io/altsql-shell/ http://ewaters.github.io/altsql-shell/ Software that few people know about.Worth others trying out too. The data world really does need these more sane solutions.

tcfunk 11y ago

Can I use this for mysql over ssh?

rajamohata 11y ago
Any update on this?

PauloManrique 11y ago

Works perfect on Windows 10. Great tool!