Tuesday, November 28, 2017

Read from CSV file using Python

Hello,

In this post I am going to explain how to read CSV file using Python. For this I assume that you have python installed in your system. Now we have to install csv package.

sudo pip install csv

This will install csv package for python. Now to read CSV file use, following code.

with open('data.csv', 'rb') as csvfile:
       reader = csv.reader(csvfile, delimiter=',', quotechar='|')
              for row in reader:
                     print row[0]
                     print row[1]
                     print row[2]
                     print row[3]
That's it and now you can have read CSV file.

To run python script. Type command

python myscript.py

And that's it. 

Wednesday, November 8, 2017

Laravel Query Builder GroupBy Syntax error or access violation

Hello,

Recently I forked a Github project and was updating it. I faced strange issue in using GroupBy in query builder. I was trying to query model as follow.

$projects = ProjectReviews::groupBy('project_id')->orderBy('created_at','DESC')->get();

Basically I wanted to get recently reviewed projects and show dates. But in above query I was getting error Syntax error or access violation project_reviews.id isn't in group by.

That was really strange issue as that's the primary key of table and it should not be part of group by. If you run that query directly in PHP MyAdmin it was working fine. So I was not sure about this. Finally after spending couple of hours I was able to find out the problem.

It's because of strict config settings of database. If you look into config/database.php file there is strict config there.

        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => true,
            'engine' => null,
        ],

You have to set to false to make GroupBy working.

Following is mentioned in MySql Documentation for this config.

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE. A value can be invalid for several reasons. For example, it might have the wrong data type for the column, or it might be out of range. A value is missing when a new row to be inserted does not contain a value for a non-NULL column that has no explicit DEFAULT clause in its definition. (For a NULL column, NULL is inserted if the value is missing.) Strict mode also affects DDL statements such as CREATE TABLE.
So basically it secures your database operations. However in Laravel it's not working properly. So I was not sure if it's laravel bug. But I had to disable it make this query working.

Disabling it wouldn't make your web app unsecured if you handle all validations in your controllers and follow best practices like Laravel already does.

However I still recommend to be careful, while using this.