<jupyter_start><jupyter_text>I'm connecting to a local sql database and non local sql databases later on<jupyter_code>#connect to one of my SQL databases<jupyter_output><empty_output><jupyter_text>[stackoverflow](https://stackoverflow.com/questions/50973191/connect-to-mysql-db-from-jupyter-notebook)<jupyter_code>#pip install PyMySQL<jupyter_output><empty_output><jupyter_text>The steps to make the 'acme' database base below comes from [here](https://www.youtube.com/watch?v=9ylj9NR0Lcg). I am accessing this local database as an example to work with SQL inside Jupyter Notebook.<jupyter_code>#establishing a connection with the local database
#keep in mind there are many other ways to establish the connection
import pymysql
import pandas as pd
conn=pymysql.connect(host='localhost',port=int(3306),user='deco',passwd='<PASSWORD>',db='acme')
df=pd.read_sql_query("SELECT * FROM users ",conn)
print(df)
df
#a nice dataframe which anyone can do anything using all commands available in pandas to make graphs, data viz, etc
conn.close() #this command is to close the connection 
conn.close() #run again to see if it did close the connection, and it did 
#but what if I want to open a connection with the data and use only sql commands inside jupyter the whole time?
%load_ext sql
%sql sqlite:///acme.db
%%sql 

SELECT * FROM users
#nice, now we can use sql commands the whole time if we want to<jupyter_output><empty_output><jupyter_text>some of sql steps comes from [here](https://www.youtube.com/watch?v=9ylj9NR0Lcg)<jupyter_code>%%sql

SELECT COUNT(id) FROM users;
%%sql

SELECT first_name, last_name FROM users;
%%sql

SELECT * FROM users WHERE location='Massachusetts';
%%sql

SELECT * FROM users WHERE location='Massachusetts' AND dept='sales';
%%sql

SELECT * FROM users WHERE is_admin>0;
%%sql 

SELECT * FROM users;
%%sql

SELECT * FROM users ORDER BY last_name ASC; 
%%sql

SELECT * FROM users ORDER BY last_name DESC;
'''
This is mysql command below, then the code cell below the line below is the equivalent in SQLite

SELECT CONCAT(first_name, '  ',last_name) AS 'Name', dept FROM users;
'''<jupyter_output><empty_output><jupyter_text>In fact here are the [differences](https://stackoverflow.com/questions/3568779/string-concatenation-does-not-work-in-sqlite)SQLite                      ||  Oracle                      CONCAT(string1, string2) or ||MySQL                       CONCAT(string1, string2, string3...) or || if PIPES_AS_CONCAT enabledPostgres                    CONCAT(string1, string2, string3...) or ||Microsoft SQL Server 2012+  CONCAT(string1, string2, string3...) or + Microsoft Access<jupyter_code>%%sql

SELECT
    first_name || ' ' || last_name AS 'Name'
FROM
    users
ORDER BY
    dept;
#the connection made is using sqlite syntax, if someone wants to create all commands with mysql syntax and not sqlite syntax,
#there are some options, but not as nice visually as the one above, at least not that I know of. Keep in mind there are 
#many other commands that could be presented, even graphs, but I wanted to keep these code together showing ways to connect 
#to an existing database and creating a database inside jupyter notebook
'''
import mysql.connector as mdb
con = mdb.connect(user='deco', password ='<PASSWORD>', database='acme')                      


this was not working right so I used the option below 
'''
#pip install pymysql
import pymysql as MySQLdb
con = MySQLdb.connect(user='deco', password ='<PASSWORD>', database='acme')
cursor = con.cursor()
cursor
cursor.execute('SELECT * FROM users;') #there are 5 rows in the table<jupyter_output><empty_output><jupyter_text>There are other options such as [SQLALchemy](https://www.sqlalchemy.org/), and others. It's also nice to work inside [MySQL Workbench](https://www.mysql.com/products/workbench/).But if someone wants to work with sql inside jupyter notebook, I'd stick with the option using %%sql, it makes me feel like I am inside MySQL Workbench seeing the tables, although from time to time, the syntax might be a bit different between sqlite and mysql such as || for CONCAT,but most of the time the syntax is the same.<jupyter_code>#What if I want to create some data from scratch and use SQL commands inside jupyter notebook without an existing database<jupyter_output><empty_output><jupyter_text>This [helped](https://www.youtube.com/watch?v=sDY_fKe_JVw)<jupyter_code>#pip install ipython-sql
import pandas as pd
import sqlite3
df= pd.DataFrame({'name': ['Juan', 'Victoria', 'Mary'], 
                  'age': [23,34,43], 
                  'city': ['Miami', 'Buenos Aires','Santiago']})

df
#connect the sqlite3 library creates a connection with the database
cnn = sqlite3.connect('exampledatabase.db')
df.to_sql('newtablecreated1',cnn) 
%load_ext sql
%reload_ext sql
%sql sqlite:///exampledatabase.db
%%sql 

SELECT * FROM newtablecreated1
#again, now we can use sql commands the whole time if we want to 
#all sql commands can be applied here again if you want
#if you wish, keep in mind that the 'COMMIT;' command will save changes made and permanently record these changes in the database
#Below are examples from two medium articles, there are some errors in some cells below, I kept it here for future use<jupyter_output><empty_output><jupyter_text>Medium [article](https://towardsdatascience.com/how-to-run-sql-queries-from-a-jupyter-notebook-aaa18e59e7bc)<jupyter_code>%load_ext sql
%sql sqlite://
print('connecting with engine ' + str(engine))
%%sql
CREATE TABLE sales
(
    key       varchar(6),
    ts        timestamp,
    product   integer,
    completed boolean,
    price     float
);
INSERT INTO sales
VALUES ('sale_1', '2019-11-08 00:00', 0, TRUE, 1.1),
       ('sale_2', '2019-11-08 01:00', 0, FALSE, 1.2),
       ('sale_3', '2019-11-08 01:00', 0, TRUE, 1.3),
       ('sale_4', '2019-11-08 01:00', 1, FALSE, 1.4),
       ('sale_5', '2019-11-08 02:00', 1, TRUE, 1.5),
       ('sale_6', '2019-11-08 02:00', 1, TRUE, 1.5);
%%sql
select * from sales;
result = _
type(result)
# sql.run.ResultSet
df = result.DataFrame()
df
df.price.hist();<jupyter_output><empty_output><jupyter_text>Medium [article](https://towardsdatascience.com/heres-how-to-run-sql-in-jupyter-notebooks-f26eb90f3259)<jupyter_code>import sqlalchemy
sqlalchemy.create_engine('oracle://hr:hr@localhost:1521/orcl')
%load_ext sql
%reload_ext sql
%sql oracle://hr:hr@localhost:1521/orcl
%%sql 

SELECT * FROM employees 
WHERE ROWNUM <= 5<jupyter_output>* sqlite:///acme.db
   sqlite:///exampledatabase.db
(sqlite3.OperationalError) no such table: employees
[SQL: SELECT * FROM employees 
WHERE ROWNUM <= 5]
(Background on this error at: http://sqlalche.me/e/14/e3q8)
{'fraction_non_alphanumeric': 0.1088230873290245, 'fraction_numerical': 0.026754847437246355, 'mean_word_length': 4.57286432160804, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 40, 'https://': 9, 'lorem ipsum': 0, 'www.': 5, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '456523', 'license': 'MIT', 'n_tokens_mistral': 2276, 'n_tokens_neox': 2096, 'n_words': 730, 'path': 'mysql_jupyternotebook.ipynb', 'repo_name': 'andreluizcoelho/usefulcodes', 'size': '6641'}