'Store SQL query results returned from the Server

I am using Sequel pro to connect to a remote server on which the mysql database is running. I am using Mac and the server has Linux installed. I want to store that result (i.e. the table that you can see in the picture) as a text file on my Mac. How can I do that? Attached is the screen shot of my query in sequel pro.

Screeon shot of the sequel pro

mysqlsqlmacossequelpropythonreplaceword


Solution 1:[1]

There is an export menu at the bottom of query results:

Export query from Sequel Pro

Solution 2:[2]

Based on MySQL Documentation there two ways for this

SELECT ... INTO OUTFILE

writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format.

SELECT ... INTO DUMPFILE

writes a single row to a file without any formatting.

Example Query

SELECT * INTO OUTFILE '/some_path/results.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM `test_table`;

Solution 3:[3]

Workaround to export your custom result set as SQL:

I came here wondering how to export the custom results as SQL because the Export Results menu only provides the option for CSV or XML. The export as SQL option is only for an entire table or columns within a table. So what I ended up doing as a workaround was first exporting as CSV, then re-importing that CSV data into Sequel Pro into a separate table (or an empty table that already exists), and then finally exporting the table (now populated with my custom results set) as a SQL export.

Solution 4:[4]

If you want the query results saved as a ready to import SQL statement you can:

  1. select/highlight the rows you want from the search results window
  2. right/context click the selection
  3. select the "Copy as SQL INSERT" option
  4. paste the contents of your system clipboard into a text file
  5. save the new file with the .sql extension

Credit - Greg Davidson https://www.endpointdev.com/blog/2014/01/copy-data-between-mysql-databases-with/

Solution 5:[5]

In my case:

  1. Duplicate Table
  2. Run SQL
INSERT INTO {{table_duplicated}} (column1, column2, ...) FROM {{table}} where {{WHERE CONDITIONS...}}}
  1. Export the duplicated table

Solution 6:[6]

Paragraphs in a docx file are made of text runs. MS Word will break up text runs arbitrarily, often in the middle of a word.

<w:r>
    <w:t>work to im</w:t>
</w:r>
<w:r>
    <w:t>prove docx2python</w:t>
</w:r>

These breaks are due to style differences, version differences, spell-check state, etc. This makes things like algorithmic search-and-replace problematic. I often use docx templates with placeholders (e.g., #CATEGORY_NAME#) then replace those placeholders with data. This won't work if your placeholders are broken up (e.g, #CAT, E, GORY_NAME#).

Docx2python v2 merges such runs in the XML as a pre-processing step. Specifically, Docx2Python merges runs with identical formatting AS DOCX2PYTHON SEES FORMATTING, that is, Docx2Python will ignore version data, spell-check state, etc. but respect supported formatting elements like bold, italics, font-size, etc.

With argument html=False, Docx2Python will merge nearly all runs (some like links are kept separate intentionally) to make most paragraphs one run.

These examples should make everything clear. Check out replace_docx_text and other functions in the Docx2Python utilities.py module.

from docx2python.main import docx2python
from docx2python.utilities import get_links, replace_docx_text, get_headings


class TestSearchReplace:
    def test_search_and_replace(self) -> None:
        """Apples -> Pears, Pears -> Apples

        Ignore html differences when html is False"""
        html = False
        input_filename = "apples_and_pears.docx"
        output_filename = "pears_and_apples.docx"
        assert docx2python(input_filename, html=html).text == (
            "Apples and Pears\n\nPears and Apples\n\n"
            "Apples and Pears\n\nPears and Apples"
        )
        replace_docx_text(
            input_filename,
            output_filename,
            ("Apples", "Bananas"),
            ("Pears", "Apples"),
            ("Bananas", "Pears"),
            html=html,
        )
        assert docx2python(output_filename, html=html).text == (
            "Pears and Apples\n\nApples and Pears\n\n"
            "Pears and Apples\n\nApples and Pears"
        )

    def test_search_and_replace_html(self) -> None:
        """Apples -> Pears, Pears -> Apples

        Exchange strings when formatting is consistent across the string. Leave
        alone otherwise.
        """
        html = True
        input_filename = "apples_and_pears.docx"
        output_filename = "pears_and_apples.docx"
        assert docx2python(input_filename, html=html).text == (
            "Apples and Pears\n\n"
            "Pears and Apples\n\n"
            'Apples and <span style="background-color:green">Pears</span>\n\n'
            "Pe<b>a</b>rs and Apples"
        )
        replace_docx_text(
            input_filename,
            output_filename,
            ("Apples", "Bananas"),
            ("Pears", "Apples"),
            ("Bananas", "Pears"),
            html=html,
        )
        assert docx2python(output_filename, html=html).text == (
            "Pears and Apples\n\n"
            "Apples and Pears\n\n"
            'Pears and <span style="background-color:green">Apples</span>\n\n'
            "Pe<b>a</b>rs and Pears"
        )

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 hoju
Solution 2
Solution 3
Solution 4
Solution 5 Codus
Solution 6 Shay

Related Questions