-1

I'm trying to get column names from a specific table.

EDIT: I forgot to mention it initially, but the python script i.e. gets the table names without any issues (different query), but the one selecting column names runs into an error.

When I write this query (see below) inside of DBeaver's SQL editor I get the expected result. But when I run the query inside of my python script, I get an error that COLUMN_NAME doesn't exist.

SELECT column_name FROM information_schema.columns WHERE table_schema=<database-name> AND table_name=<table-name>;

(db name and table name are correctly written in the real query)

I also need to mention, that when I previously used MySQL Workbench, this query written in python has worked as expected. It was when I switched to DBeaver for database management that the issue arose.

Are there possibly some further steps I need to do inside of DBeaver in order to get the information_schema to work as expected?

mysql --version >> mysql  Ver 15.1 Distrib 10.4.13-MariaDB, for Linux (x86_64) using  EditLine wrapper
sushiPlague
  • 23
  • 1
  • 6
  • You are contradicting yourself (first you wrote the query worked in dbeaver, then you wrote it did not). – Shadow Jul 06 '20 at 18:11
  • @Shadow I've reformulated the question. Thanks for pointing it out. – sushiPlague Jul 06 '20 at 21:44
  • Changing the client you use to manage a mysql instance cannot have any effect on a program that connects to the same mysql server. You must have changed something in the configuration or access controls to cause the error in the mysql statement. – Shadow Jul 06 '20 at 21:48
  • @Shadow Is it possible that PyMySql cannot work out all of the queries with MariaDB? The program was initially tested with MySQL, not MariaDB, but the syntax for the queries I used should be the same. Edit: Because I find it confusing, that all other queries work, except the one for the column names. – sushiPlague Jul 06 '20 at 21:55
  • This is a very common mistake: mariadb is **not** mysql! Mariadb is **fork** of mysql with increasingly different functionality, thus syntax! You should match your dev environment with your live environment down to the sub-version level! I think O. Jones may be onto something with the case sensitivity of table / column names. What is your exact select statement (pls replicate it exactly in your question)? What is the lower_case_table_names setting in your dev and prodcution evironments? What is the exact version of your production environment? – Shadow Jul 06 '20 at 22:03
  • Btw, case sensitivity also applies to information_schema table and column names as well! – Shadow Jul 06 '20 at 22:09
  • @Shadow There shouldn't be any difference in the query I posted in the question as I'm aware. Also, PyMySQL has support for MariaDB >= 5.5, therefore the MariaDB queries should work... – sushiPlague Jul 06 '20 at 22:10
  • `Also, PyMySQL has support for MariaDB >= 5.5, therefore the MariaDB queries should work` - again, this means the pymysql driver is compatible with MariaDB. It does not mean you cannot create a query with invalid syntax. See the following SO question and particularly Bill Karwin's accepted answer for an interesting insight into information_schema cases: https://stackoverflow.com/questions/54538448/what-are-the-changes-in-mysql-8-result-rowset-case – Shadow Jul 06 '20 at 22:15
  • The exact select statement you've asked for in the upper comment is the same as the question one, except in the placeholder for database name is "higher-education-institution", and for the table name the user selects a table from a list of tables for a certain db. Those table names are queried with "SHOW TABLES" statement, and later on parsed out to get the according table names. Therefore, the table names should be the correct ones, directly from the db. – sushiPlague Jul 06 '20 at 22:25
  • Again, the problem is not with your table or database names. Those are only data in your current query. The error is with the COLUMN_NAME identifier. Case sensitivity settings should apply to the information_schema objects as well. – Shadow Jul 06 '20 at 22:32
  • Or not. Identifier case sensitivity settings do not apply to column names. OK. In this case, check if you have some invisible (non-printing) characters in your python code that would change the query. – Shadow Jul 06 '20 at 22:38
  • There are no invisible chars. I've also changed the column_name -> COLUMN_NAME, but the issue persists. Do I possibly need to configure permissions for the information_schema (I'm currently testing as the root user)? – sushiPlague Jul 07 '20 at 07:14
  • You would get a different error message, not a column does not exist. Sorry, but cannot reproduce the bug you are facing. – Shadow Jul 07 '20 at 07:56
  • Let's see the error message. Are there any punctuation marks in the table names? – Rick James Jul 08 '20 at 21:39

2 Answers2

0

Say this to your server

SHOW VARIABLES LIKE 'lower_case_table_names';

If you get the value 0 your table names are case sensitive. (1 means case-insensitive). See this for a bit more explanation.

If your names are case-sensitive you may need to try a query with this casing.

SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=<database-name> AND TABLE_NAME=<table-name>;

Be sure to give <database-name> and <table-name> in the case you used to create them.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • I got the value 0 after running that query. But the issue is that the table name and database name are correct, but the query returns an error saying COLUMN_NAME doesn't exist, while it certainly does. – sushiPlague Jul 06 '20 at 21:36
0
WHERE table_schema=<database-name> AND table_name=<table-name>;

If there are no quotes around those two names, and if there is punctuation or spacing in the names, any of several error can occur.

Don't simply stick the names in; quote them and escape certain characters. Or use some "bind" technique. Show us the actual Python code that builds the string and the built SQL string.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Sorry for the late reply. There were no spacing in the names, nor was there a character that needed escaping, nonetheless, just inserting the value without quotation marks was causing the issue. When I added them, it worked again. I'm just curious how it worked with MySQL but not with MariaDB (inserting the value without quotation marks). Thanks! – sushiPlague Jul 13 '20 at 14:01