Read Data from SQLite Where Column Name Contains Spaces

Read data from SQLite where column name contains spaces

For quoting identifiers like table/column names, SQLite supports back ticks for compatibility with MySQL, and square brackets for compatibility with SQL Server, but the portable way is to use double quotes:

SELECT "condition name" FROM library

(Single quotes would be used for string values.)

SQLite column with space in name

You are modifying the String value column to delimit the column name

column = "`" + column + "`"; //handle space in column names
String sql = "SELECT DISTINCT "+column+", COUNT(*) ...

but when you go to retrieve the value from the ResultSet you are using the delimited column name

rs.getString(column)

which is the equivalent of

rs.getString("`class of worker`")

and the ResultSet contains no column of that name: the name is just class of worker. In other words, the SQL command needs the delimiters, but the ResultSet#getString method does not.

So instead of modifying the column variable you should just put the backticks (or double quotes, or square brackets; SQLite supports all of them) in the sql string

// column = "`" + column + "`"; // do not handle space in column names here, do it below
String sql = "SELECT DISTINCT `" + column + "`, COUNT(*) ...

and then your rs.getString(column) should work fine.

How to select a column name with a space in MySQL

Generally the first step is to not do that in the first place, but if this is already done, then you need to resort to properly quoting your column names:

SELECT `Business Name` FROM annoying_table

Usually these sorts of things are created by people who have used something like Microsoft Access and always use a GUI to do their thing.

Insert in column names with blank space in sqlite3

Your original code is likely producing this

INSERT or IGNORE INTO preodds ("id","handicap","goal line","corner line")
 VALUES (:id,:handicap,:goal line,:corner line)

Notice the spaces in the parameters names... the names starting with colon (:). That's the problem.

Your attempted solution is just submitting string literals as values, so that is exactly what is inserted.

INSERT or IGNORE INTO preodds ("id","handicap","goal line","corner line")
 VALUES (":id",":handicap",":goal line",":corner line")

Quotes can be used to delimit object names, but in other contexts quotes are interpreted as literal string value delimiters. Precise rules for determining how it interprets quoted values are found here.

As far as I can tell, parameters cannot be escaped and so cannot contain spaces or other special characters, at least nothing that is documented. See sqlite docs.

If you are going to build the parameter list dynamically, you should strip out all spaces from the parameter names. Or alternatively, you could just use unnamed parameters using the ? character. Either way the parameters are assigned values in the order they appear, so there would be no difference.

Something like:

INSERT or IGNORE INTO preodds ("id","handicap","goal line","corner line")
 VALUES (:id,:handicap,:goalline,:cornerline)

or

INSERT or IGNORE INTO preodds ("id","handicap","goal line","corner line")
 VALUES (?, ?, ?, ?)

SQLite selecting two columns (with spaces) as one

Try this:

 SELECT ("column 1" || ' ' || "column 2") AS expr1 FROM your_table;

OR this

 SELECT ([column 1] || ' ' || [column 2]) AS expr1 FROM your_table;

OR this

 SELECT (`column 1` || ' ' || `column 2`) AS expr1 FROM your_table;

Per the SQLIte documentation, you use single quote for strings, and double for identifiers, but you have the other options for compatibility

• 'keyword' A keyword in single quotes is a string literal.
• "keyword" A keyword in double-quotes is an identifier.
• [keyword] A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.
• keyword` A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility.

Creating table with column name (having spaces in between)

You need to add [] brackets to column name.

CREATE TABLE IDE_Dump
(
   Name VARCHAR(255),
   [Head Name] VARCHAR(255),
   [Parent Account] VARCHAR(255) 
);

Or you can use double quotes "" as jarlh commented:

CREATE TABLE IDE_Dump
(
   Name VARCHAR(255),
   "Head Name" VARCHAR(255),
   "Parent Account" VARCHAR(255) 
);

SQLAlchemy column name with space

There are two ways to resolve this.

1. When defining the table you would need to specify an alias with the key parameter

t_table_name = Table(
    'tablename',
    metadata,
    Column('SQL Column', Integer, key='sql_column')
)

2. Define the ORM class as

class Employee(Base):
    emp_name = Column("employee name", String)

---

Related Topics