SQLConn์ ๋ค์ํ SQL ๋ฐ์ดํฐ๋ฒ ์ด์ค ๊ด๋ฆฌ ์์คํ (DBMS)์ ์ฐ๊ฒฐํ์ฌ ๋ฐ์ดํฐ๋ฅผ ์ฝ๊ฒ ์กฐ์ํ๊ณ ๊ด๋ฆฌํ ์ ์๋ Python ํจํค์ง์ ๋๋ค. ์ด ํจํค์ง๋ MySQL, PostgreSQL, Microsoft SQL Server ๋ฐ SQLite ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ๋ํ ์ง์์ ํฌํจํฉ๋๋ค.
- ์ฌ๋ฌ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ๋ํ ํตํฉ๋ ์ธํฐํ์ด์ค ์ ๊ณต
- ๋ฐ์ดํฐ ์กฐํ ๋ฐ ์กฐ์์ ์ํ ๊ฐํธํ ํจ์ ์ ๊ณต
- ๋ฐ์ดํฐ๋ฒ ์ด์ค ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ๋ฅผ DataFrame์ผ๋ก ๋ณํ
- CSV, Excel, TSV ํ์ผ๋ก์ ๋ฐ์ดํฐ ์ถ๋ ฅ ์ง์
- ๋ค๋ฅธ ๋ฐ์ดํฐ๋ฒ ์ด์ค๋ก์ ๋ฐ์ดํฐ ์ด๋ ์ง์
- MySQL
- PostgreSQL
- Microsoft SQL Server
- SQLite
ํ์ด์ฌ๊ณผ pip๊ฐ ์ค์น๋ ํ๊ฒฝ์์ ๋ค์ ๋ช
๋ น์ด๋ฅผ ํตํด SQLConn
ํจํค์ง๋ฅผ ์ค์นํ ์ ์์ต๋๋ค:
pip install SQLConn
ํด๋์ค๋ช | ์๊ฐ | ํน์ด ์ฌํญ |
---|---|---|
SQLConn |
๋ค์ํ SQL ๋ฐ์ดํฐ๋ฒ ์ด์ค ๊ด๋ฆฌ ์์คํ (DBMS)์ ์ฐ๊ฒฐํ๊ธฐ ์ํ ํด๋์ค์ ๋๋ค. | ์ถ์ ํด๋์ค์ ๋๋ค. |
MYSQLConn |
MySQL ๋ฐ์ดํฐ๋ฒ ์ด์ค์์ ์ฐ๊ฒฐ์ ๊ด๋ฆฌํฉ๋๋ค. | SQLConn์๊ฒ ์์ ๋ฐ์์ต๋๋ค |
MSSQLConn |
Microsoft SQL Server ๋ฐ์ดํฐ๋ฒ ์ด์ค์์ ์ฐ๊ฒฐ์ ๊ด๋ฆฌํฉ๋๋ค. | SQLConn์๊ฒ ์์ ๋ฐ์์ต๋๋ค |
PostgreSQLConn |
PostgreSQL ๋ฐ์ดํฐ๋ฒ ์ด์ค์์ ์ฐ๊ฒฐ์ ๊ด๋ฆฌํฉ๋๋ค. | SQLConn์๊ฒ ์์ ๋ฐ์์ต๋๋ค |
SQLiteConn |
SQLite ํ์ผ ๊ธฐ๋ฐ ๋ฐ์ดํฐ๋ฒ ์ด์ค์์ ์ฐ๊ฒฐ์ ๊ด๋ฆฌํฉ๋๋ค. | SQLConn์๊ฒ ์์ ๋ฐ์์ต๋๋ค |
๋ฉ์๋๋ช | ์๊ฐ | ํน์ด์ฌํญ |
---|---|---|
to_DataFrame |
SQL ์ฟผ๋ฆฌ๋ฅผ ์คํํ๊ณ ๊ฒฐ๊ณผ๋ฅผ pandas DataFrame์ผ๋ก ๋ฐํํฉ๋๋ค. | Show, Select ์ปค๋งจ๋๋ง ๊ฐ๋ฅ |
execute |
๋ฐ์ดํฐ๋ฒ ์ด์ค์์ SQL ์ฟผ๋ฆฌ๋ฅผ ์คํํ๋ ๊ฒฐ๊ณผ๋ฅผ ๋ฐํํ์ง ์์ต๋๋ค. | Show, Select ์ปค๋งจ๋ ์ฌ์ฉ ๋ถ๊ฐ๋ฅ |
to_csv |
SQL ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ๋ฅผ CSV ํ์ผ๋ก ์ ์ฅํฉ๋๋ค. | Show, Select ์ปค๋งจ๋๋ง ๊ฐ๋ฅ |
to_excel |
SQL ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ๋ฅผ Excel ํ์ผ๋ก ์ ์ฅํฉ๋๋ค. | Show, Select ์ปค๋งจ๋๋ง ๊ฐ๋ฅ |
to_tsv |
SQL ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ๋ฅผ TSV ํ์ผ๋ก ์ ์ฅํฉ๋๋ค. | Show, Select ์ปค๋งจ๋๋ง ๊ฐ๋ฅ |
to_sql |
๋ค๋ฅธ ๋ฐ์ดํฐ๋ฒ ์ด์ค์ SQL ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ๋ฅผ ์ ์ฅํฉ๋๋ค. | Show, Select ์ปค๋งจ๋๋ง ๊ฐ๋ฅ |
to_HTML |
ํด๋น ์ฟผ๋ฆฌ๋ฅผ HTML์ฝ๋๋ก ๋ณํํฉ๋๋ค | Show, Select ์ปค๋งจ๋๋ง ๊ฐ๋ฅ |
URL |
๋ฐ์ดํฐ๋ฒ ์ด์ค ์ฐ๊ฒฐ URL์ ์ ๊ณตํฉ๋๋ค. | ์ฝ๊ธฐ์ฉ ํ๋กํผํฐ |
engine |
๋ฐ์ดํฐ๋ฒ ์ด์ค ์ฐ๊ฒฐ์ ์ํ SQLAlchemy ์์ง์ ์ ๊ณตํฉ๋๋ค. | ์ฝ๊ธฐ์ฉ ํ๋กํผํฐ |
conn |
๋ฐ์ดํฐ๋ฒ ์ด์ค ์ฐ๊ฒฐ ๊ฐ์ฒด๋ฅผ ์ ๊ณตํฉ๋๋ค | ์ฝ๊ธฐ์ฉ ํ๋กํผํฐ |
๊ฐ ๋ฐ์ดํฐ๋ฒ ์ด์ค ์ฐ๊ฒฐ ๊ฐ์ฒด๋ฅผ ์์ฑํ๊ณ ์ฌ์ฉํ๋ ๊ธฐ๋ณธ์ ์ธ ๋ฐฉ๋ฒ์ ๋ค์๊ณผ ๊ฐ์ต๋๋ค:
from SQLConn import MYSQLConn, PostgreSQLConn, MSSQLConn, SQLiteConn
# MySQL ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ฐ๊ฒฐ
mysql_conn = MYSQLConn(host='your-host', user='your-user', password='your-password', database='your-database',port='your-port')
# MsSQL ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ฐ๊ฒฐ
mssql_conn = MSSQLConn(host='your-host', user='your-user', password='your-password', database='your-database',port='your-port')
# PostgreSQL ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ฐ๊ฒฐ
postgresql_conn = PostgreSQLConn(host='your-host', user='your-user', password='your-password', database='your-database',port='your-port')
# SQLite ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ฐ๊ฒฐ
mssql_conn = SQLiteConn('your-host')
# ๋ฐ์ดํฐ ์กฐํ ์์
df = mysql_conn.to_DataFrame("SELECT * FROM your_table")
print(df)
๋ก์ปฌ ํธ์คํธ ๋ฐ์ดํฐ๋ฒ ์ด์ค ์ฐ๊ฒฐ ๊ฐ์ฒด๋ฅผ ์์ฑํ๊ณ ์ฌ์ฉํ๋ ๊ธฐ๋ณธ์ ์ธ ๋ฐฉ๋ฒ์ ๋ค์๊ณผ ๊ฐ์ต๋๋ค:
from SQLConn import MYSQLConn, PostgreSQLConn, MSSQLConn
# MySQL ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ฐ๊ฒฐ
mysql_conn = MYSQLConn('your-password')
# MsSQL ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ฐ๊ฒฐ
mssql_conn = MSSQLConn('your-password')
# PostgreSQL ๋ฐ์ดํฐ๋ฒ ์ด์ค์ ์ฐ๊ฒฐ
postgresql_conn = PostgreSQLConn('your-password')
# ๋ฐ์ดํฐ ์กฐํ ์์
df = mysql_conn.to_DataFrame("SELECT * FROM your_table")
print(df)
SQLConn is a Python package that connects to various SQL database management systems (DBMS) to easily manipulate and manage data. This package includes support for MySQL, PostgreSQL, Microsoft SQL Server and SQLite databases.
- Provides a unified interface to multiple databases
- Provides simple functions for data inquiry and manipulation
- Convert database query results to DataFrame
- Supports data output to CSV, Excel, TSV files
- Support for data movement to other databases
- MySQL
- PostgreSQL
- Microsoft SQL Server
- SQLite
In an environment where Python and pip are installed, you can install the SQLConn
package using the following command:
pip install SQLConn
class name | info | significant |
---|---|---|
SQLConn |
Class for connecting to various SQL database management systems (DBMS). | It is an abstract class. |
MYSQLConn |
Manages MySQL database relationships. | protected SQLConn |
MSSQLConn |
Manages connections to Microsoft SQL Server databases. | protected SQLConn |
PostgreSQLConn |
Manages connections to PostgreSQL databases. | protected SQLConn |
SQLiteConn |
Manages SQLite file-based database relationships. | protected SQLConn |
method name | info | significant |
---|---|---|
to_DataFrame |
Executes a SQL query and returns the results as a pandas DataFrame. | Show, Select commands only |
execute |
Executes a SQL query on a database but returns no results. | Show, Select command not available |
to_csv |
Save the SQL query results as a CSV file. | Show, Select commands only |
to_excel |
Save the SQL query results as an Excel file. | Show, Select commands only |
to_tsv |
Save the SQL query results as a TSV file. | Show, Select commands only |
to_sql |
Store SQL query results in another database. | Show, Select commands only |
to_HTML |
Converts the SQL query results to HTML code. | Show, Select ์ปค๋งจ๋๋ง ๊ฐ๋ฅ |
URL |
Provide the database connection URL. | get property |
engine |
Provides SQLAlchemy engine for database connection. | get property |
conn |
Provides the database connection object. | get property |
The basic way to create and use each database connection object is as follows:
from SQLConn import MYSQLConn, PostgreSQLConn, MSSQLConn, SQLiteConn
# Connect to MySQL database
mysql_conn = MYSQLConn(host='your-host', user='your-user', password='your-password', database='your-database',port='your-port')
# Connect to MsSQL database
mssql_conn = MSSQLConn(host='your-host', user='your-user', password='your-password', database='your-database',port='your-port')
# Connect to PostgreSQL database
postgresql_conn = PostgreSQLConn(host='your-host', user='your-user', password='your-password', database='your-database',port='your-port')
# Connect to SQLite database
mssql_conn = SQLiteConn('your-host')
# Data query example
df = mysql_conn.to_DataFrame("SELECT * FROM your_table")
print(df)
The basic way to create and use a localhost database connection object is as follows:
from SQLConn import MYSQLConn, PostgreSQLConn, MSSQLConn
# Connect to MySQL database
mysql_conn = MYSQLConn('your-password')
# Connect to MsSQL database
mssql_conn = MSSQLConn('your-password')
# Connect to PostgreSQL database
postgresql_conn = PostgreSQLConn('your-password')
# Data query example
df = mysql_conn.to_DataFrame("SELECT * FROM your_table")
print(df)
This document was converted from the Korean version to English through a translator, so the meaning may be strange.