What is the best way to read SQL dataset in to Tensorflow?

Question

What is the best way to read SQL database in to Tensorflow?

Currently, I am using Postgres on server and developed DL algorithm on Tensorflow using Jupyter Lab. How can I import data into Jupyter Lab using tf.data API? I do not want to store the data in the disk and keep running the algorithm when the new data arrives.

It seems like tf.data.experimental.SqlDataset only support for sqlite.

(NOTE: I did not upgrade my Tensorflow, so, I am using tf.contrib.data.SqlDataset() for the minimal working example.)

I migrated the data from PostgreSQL to SQLite3 and using

#Ignore the warnings
import warnings
warnings.filterwarnings("ignore")

import tensorflow as tf
#To start an input pipeline, you must define a source
dataset = tf.contrib.data.SqlDataset("sqlite", "/home/musara1/musara_dev.sqlite3",
                                 "SELECT * FROM basetable LIMIT 10",
                                 (tf.string, tf.int32, tf.int32, tf.int32, tf.int32, tf.int32, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.int32, tf.int32, tf.int32))

iterator = dataset.make_one_shot_iterator()
next_element = iterator.get_next()
# Prints the rows of the result set of the above query.
sess=tf.InteractiveSession()

print(sess.run(next_element))

I can print the next element. However, there are other transformations I need to do on the dataset. such as splitting into training/validation/testing and getting rid of some columns et cetera. However, the output of tf.contrib.data.SqlDataset() is for me <SqlDataset shapes: ((), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), ()), types: (tf.string, tf.int32, tf.int32, tf.int32, tf.int32, tf.int32, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.float64, tf.int32, tf.int32, tf.int32)>

I have 25 columns and tf.contrib.data.SqlDataset() creates 25 different tensorflow.python.framework.ops.Tensor. How can I bring them together? Therefore, I can use tf.data.Dataset.from_tensor_slices()?

Answer 1

You use the methods on SqlDataset to manipulate the data. For example, create a train/test split with:

test_dataset = dataset.take(1000) 
train_dataset = dataset.skip(1000)

I would get rid of unneeded columns in the SELECT statement to reduce the size of the data as early as possible.

Answer 2

That import step doesn't look right. Can you try it like this?

import pypyodbc 
cnxn = pypyodbc.connect("Driver={SQL Server Native Client 11.0};"
                        "Server=ServerName;"
                        "Database=TestDB;"
                        "Trusted_Connection=yes;")

cursor = cnxn.cursor()
cursor.execute('SELECT * FROM Actions')

for row in cursor:
    print('row = %r' % (row,))

Answer 3

I'm late to the party but for anyone reading this in the future: SQLAlchemy is a great way to handle any SQL interfaces. Just create a model class that matches your DB schema, then use the query API to get your data.

If you have very large datasets, you can use use yield_per or slice to easily retrieve it in chunks, as de

Answer 4

here is a link for the driver-installation on possible linux distros. and another link with installation und connection examples. my code looks like as following,

import pandas as pd # data processing
import pyodbc
pyodbc.drivers()

server = 'MY_SERVER' 
database = 'MY_DB' 
username = 'DB_USER' 
password = 'DB_PASS'  
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};'
                  'SERVER='+server+
                  ';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

query = 'SELECT * FROM XXX'
sales = pd.read_sql(query, cnxn)
print(sales.head(26))
print(sales.info())